DoCmd.TransferSpreadSheet drops a record on import

  • Thread starter Thread starter DanL
  • Start date Start date
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,
 
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.
 
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.
 
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.
 
DanL said:
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?
Click to expand...

When importing an Excel worksheet the first row is very influential in
determining the data type for the column.

I sometimes use Office Automation to insert a dummy row at the top of
the Excel worksheet with appropriately formatted data in each column to
help "prime" the import pump.

After import I remove my "dummy" row from the table holding the imported
data. Just one idea....

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' Microsoft MVP 2007, 2008
'--------------------------
 
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).
 
Back
Top