Import An Excel Spreadsheet With VBA In Access

[Leo_Polla_Psemata]

Hi there
With docmd.transferspreedsheet, we can import a spreadsheet in our table. So far so good.

Could we import a spreadsheet on a child table that requires generation of key that is linked to the PR.key of mother table ?

So far, i use the primitive method, i display the excel that i want to import, I copy all cells and paste in the subform which is tailored
to the exact same format as excel lay out. There are few problems that i solve on the spot, for example, some columns that contain numbers
and text, must sit in a field that is text, however the text doesn't paste (if the first excel cell is number).

 

[Uncle Gizmo]

however the text doesn't paste (if the first excel cell is number).

I had this problem a few years back. The solution, I seem to recall was to add a new top row to the spreadsheet, with the correct type in it. If you want to copy text but the first entry is a number, then precede it with a row with text in that row instead of numbers. Once you've imported it then delete this "formatting row"...

 

[Pat Hartman]

If the spreadsheet has both parent and child data in the same row, you can do this with two queries. The first to insert the parent rows, the second to insert the child rows. Create a totals query to summarize the parent data and insert it. In the child insert, join to the parent table on whatever the unique fields are to obtain the FK for the insert of the child data.

If the parent data is inconsistent, the process will be much more complex because you have to find the uniqueness of the parent data to tie the two sets together

 

[CJ_London]

or just use a query on the excel file - the basic select query would be

SELECT XL.*
FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\folderpath\filename.xlsx'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes]) AS XL;

which once you have working you can go to design view and change it to an append query

Because excel data is untyped so you can have a mix of numbers/dates/text etc in the same column, I tend to set HDR=no which brings through the header row as the first record - and consequently all fields are treated as text and columns are named F1, F2 etc

In your append query you can use the cDate/cInt/clng etc to set it to the correct datatype - and have a criteria to ignore the first record (typically something like

WHERE F1<>"whatever is the name of the first column"

You can also specify a range for example instead of

[sheet1$]

you have

[sheet1$A:E]- will return the first 5 columns

[sheet1$A:E6] - will return the first 6 rows of the first 5 columns

[sheet1$A3:E6]- will return the 3rd to 6th rows of the first 5 columns

[sheet1$C:E]- will return the 3rd and 4th and 5th columns

 

[Pat Hartman]

So, CJ, are you saying that you can append data to two tables from the one query?

 

[CJ_London]

No - you have two queries - append the parent data then the child (or children) data

My query suggestion gets around the problem of having a temporary or linked table with the potential for data mismatches and FE bloat due to data inconsistencies.

One of the apps I have for a client imports close on a million rows each month from excel. I use this technique for sense checking the data and providing a routine which basically applies filters to each column based on a meta table which identifies values which are wrong datatype, out of scope etc and reports back to user for them to fix before being able to import (a client requirement - it could instead have just imported compliant rows for the user to resolve and import at a later time). Once compliant, being a million rows that particular routine then imports to a table in a temporary database and applies indexes so next steps are efficient. But for others it just continues with the import based on the query.

The next steps do a number of things - perhaps a new category has been added (assuming the meta data sees this in scope) or a new customer/supplier - these would appended first, followed by (in the case of invoices), invoice header, new products, invoice lines - etc. So could be 4 or 5 queries to insert new or updated data to different table. Think my record was 14 queries from an output from SFDC.