Solved How to copy data from excel to access query.

[Khurramshaikh47]

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

ERROR WHEN I AM TRYING TO COPY IN MY QUERY

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

Thank you in Advance.

 

[Gasman]

Link to the spreadsheet instead.

 

[Khurramshaikh47]

Link to the spreadsheet instead.

Can you please explain how to link to the spreadsheet?

 

[GPGeorge]

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.

 

[Khurramshaikh47]

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]

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]

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]

That's fine except we don't know what the poster was trying to do with the data.

 

[GPGeorge]

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.

 

[Khurramshaikh47]

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]

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.