unparsable record

[Claire]

I am trying to import a comma deliminated file from an outside database into
access. If I first open it with excel, and then import that into access, it
works with limited errors- some zip codes do not go through and such, which
is no problem. However, if I try to import the csv file directly into
access, I get ~15 unparsable record errors from the table of ~8,500 records.
Is there a reason that excel would be able to read the csv file, but access
isn't? When I looked at the access table, the records with the erors
imported the first ~10 fields, but left the rest blank.

Thanks for any suggestions as to what is going on,
Claire

 

[Clifford Bass]

Hi Claire,

That is kind of hard to answer without actually seeing the data that
fails. Possibly related to strings that contain embedded commas and/or
quotes? If that does not help, post a sample with any sensitive information
converted to dummy data.

Clifford Bass

 

[Larry Daugherty]

My guess would be that the CSV file contains some
non-printable/control characters. Excel filters them out, Access
doesn't.

Depending on the level of automation you desire you can set a
reference to Excel in your application and when importing from that
source, call a function of your creation that opens the target CSV
file in Excel and saves it as either the same filename, a newly names
CSV or even as an XLS file. Then import the newly named file into
your application. That should leave you with the errors you don't
seem to mind. File housekeeping after the fact is up to you

HTH