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).
--
DanL
"Dale Fye" wrote:
> 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" wrote:
>
> > 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.
> > --
> > DanL
> >
> >
> > "Jerry Whittle" wrote:
> >
> > > 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.
> > > --
> > > Jerry Whittle, Microsoft Access MVP
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > >
> > > "DanL" wrote:
> > >
> > > > 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,
> > > > --
> > > > DanL
|
Similar Threads
