Best method for importing

tmyers

Well-known member
Local time
Today, 01:50
Joined
Sep 8, 2020
Messages
1,091
When dealing with imported excel worksheets, what would be considered the best method? So far the majority of what I have done is just the transferspreadsheet method, but for some reason or another lately it has resulted in the imported data getting all kinds of jumbled up. I have one module that does it via looping and adding records via DAO (provide by ArnelGP initially).

Are there arguments for one versus the other or is another method just better?
 
There is no general rule because there is no such thing as a perfectly generic problem. I look at it as "tools in the toolkit" and use the right tool for each job. So sometimes the TransferSpreadsheet method is perfectly adequate and the simplest to use. Least amount of work, least amount of data manipulation to get things into a usable table.

But as you noted, there are times when that doesn't quite work as planned. That is when you need to consider a DAO import via a recordset and an Excel Applications Object. There are also cases where you could just map the spreadsheet as a read-only external table and do a simple query to an internal table in your database. And if it happens that you have a .CSV worksheet, there is also the idea of reading the sheet via an OPEN filename FOR INPUT AS FILE #n after which you would use INPUT LINE and some type of text parsing. You have all sorts of options. The trick is to know when to use each particular option.

You have asked a perfectly valid question, but there is no "one size fits all" answer.
 
I usually use TransferSpreadsheet to link to the spreadsheet. Then use an append query with validation where possible to append to the destination table.
 
When dealing with imported excel worksheets, what would be considered the best method? So far the majority of what I have done is just the transferspreadsheet method, but for some reason or another lately it has resulted in the imported data getting all kinds of jumbled up. I have one module that does it via looping and adding records via DAO (provide by ArnelGP initially).

Are there arguments for one versus the other or is another method just better?
The problem with looking for "the best X" is that the best X in every class is a unicorn. I.e. it doesn't exist.

Pick the method that provides consistent, efficient results with the minimum amount of effort. Reevaluate that method regularly and be ready to switch when something more consistent, or more efficient or requiring less effort comes along.
 
another way is to use a linked query - something like

Code:
INSERT INTO tblDestination ( field1, field2, field3 )
SELECT field1, field2, field3
FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\pathtofile\XLfile.XLSX'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])  AS XL;
 

Users who are viewing this thread

Back
Top Bottom