DoCmd.TransferSpreadSheet drops a record on import

D

DanL

I am trying to automate the import of a file that changes each day in the
number of records. Intermittently, it seems, the import function will drop a
record (the first record). Here is the command I am using:

DoCmd.TransferSpreadsheet acImport, , "ImportData", fs, True

fs is set to the path and filename of the excel file to be imported. Some
days it works fine, othrer days it drops one row. In every case, I want to
import the entire spreadsheet using the first row as the header. Before I
import, I always delete the table "ImportData" so there is no conflict with
previous imports.

Does anyone have any ideas why this is happening?

Thanks,
 
J

Jerry Whittle

Do you see a table named something like "ImportData_ImportErrors"? It often
tells what offending data is messing up the row in question.

Also instead of dropping the _ImportErrors table, I suggest just deleting
all the records instead. Dropping then recreating tables can cause bloating
problems.
 
D

DanL

I am not getting an import error table. It names the columns appropriately,
but doesn't bring in the first row of data. If there is only one row, then
the table is empty.

I will try reworking the code to delete the rows.

Thanks for the suggestions.
 
D

Dale Fye

Personally, I wish Microsoft would allow us to define ImportSpecifications
for Excel files, like they do for csv, text, ... Unfortunately, they have
decided that they can figiure out what our Excel data is supposed to look
like better than we can.

Have you tried linking to the spreadsheet rather than importing? This will
prevent bloating of your database, and the need to compact occassionally.

I've found that if the import wizard decides that a field is of one
datatype, and the actual data in that row for one of the records doesn't
match the datatype, that Access will drop rows, but as Jerry mentioned, this
usually results in an ???_ImportErrors table. This is another reason for
creating a table, defining its structure to match what the import datashould
be (make sure to allow Null values if you have some fields that contain
Nulls), and then import the data into that table, rather than creating the
table during the import.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
D

DanL

Thank you all for your responses. Although you have been very helpful, the
problem persists.

I am at the mercy of the file I am downloading (from a large retailer) and
am restrained to accepting the excel file as it comes in. I have found the
problem by looking at the raw data in notepad and comparing it to a csv
conversion file (the csv file loads fine every time).

Intermittantly in the download, the crlf after the header row is missing.
When the excel file is saved as a csv, the crlf is inserted. When the crlf is
missing, the first row of data (after the header) is loaded as a
continuation of the header row and is omitted from the import.

I am currently working on a way to detect the missing crlf and solve this
problem before importing. Any suggestions would be appreciated.

Of course, I could convert the downloadd excel file to csv before importing
each day (...but that's no fun).
 
Top