Solved How to copy data from excel to access query. (1 Viewer)

Local time
Today, 12:46
Joined
Aug 19, 2021
Messages
130
Hi, I am trying to copy attendance from excel to my Microsoft access query.
There are 3 columns that I want to copy
1) Attendance Status: this is a lookup datatype including Present, Absent.
2) Time_In: this is a date/time datatype with a general format.
3) Time_Out: this is a date/time datatype with a general format.

When I am trying to copy the data an error is appearing. Please check the screenshot below.

DATA IN MICROSOFT EXCEL
1642168243871.png


ERROR WHEN I AM TRYING TO COPY IN MY QUERY
1642168284131.png

Please guide me it takes much time when I copy it one by one.

Thank you in Advance.
 

GPGeorge

Grover Park George
Local time
Today, 00:46
Joined
Nov 25, 2004
Messages
678
First, I wholeheartedly agree that trying to copy/paste partial spreadsheet data into an Access query is high-risk, low-reward. Even if you try to paste into a table, not a query, it's iffy.

You can easily link to a spreadsheet using the built-in "External Data" feature of Access.

1642169762296.png
 
Local time
Today, 12:46
Joined
Aug 19, 2021
Messages
130
First, I wholeheartedly agree that trying to copy/paste partial spreadsheet data into an Access query is high-risk, low-reward. Even if you try to paste into a table, not a query, it's iffy.

You can easily link to a spreadsheet using the built-in "External Data" feature of Access.

View attachment 97549
Thank you, GP for your response.
Actually, in my query, there are more hidden fields also calculated fields.
This query is based on the date parameter.
Will it work in this case?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:46
Joined
Feb 19, 2002
Messages
36,352
Linking to the spreadsheet and joining it to the query will retrieve the data as George said but it will make the query not updateable.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:46
Joined
Sep 21, 2011
Messages
10,561
Linking to the spreadsheet and joining it to the query will retrieve the data as George said but it will make the query not updateable.
I was thinking of an append query using the linked data as source?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:46
Joined
Feb 19, 2002
Messages
36,352
That's fine except we don't know what the poster was trying to do with the data.
 

GPGeorge

Grover Park George
Local time
Today, 00:46
Joined
Nov 25, 2004
Messages
678
That's fine except we don't know what the poster was trying to do with the data.
In the past, when faced with the task of incorporating external data, I preferred to use a Staging table in Access with all text fields. The process would be:
  • Link to the external data
  • Run a delete query to remove existing data from the Staging table
  • Run an append query to append the new data from the linked table into the Staging table
  • Do whatever needs to be done to the data to prepare it for insertion to the appropriate production table(s)
  • Append that prepared data to the production table(s).
Granted that works best when the process is repeated, e.g. a daily import of external data. In that scenario, a lot of it can be automated in VBA.
 
Local time
Today, 12:46
Joined
Aug 19, 2021
Messages
130
My issue is solved.
I first copied the data from Excel to Notepad. And then Re-copy it from Notepad and paste it into my your query.
Thank you very much all of you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:46
Joined
Feb 19, 2002
Messages
36,352
copy/paste is not how we work once we are using Access. Queries don't store data. Tables store data. Using the suggestion to link to the spreadsheet and run an append query to import the data gives you the most control and allows you to even correct certain kinds of bad dat in the process.
 

Users who are viewing this thread

Top Bottom