Microsoft Access 2003 Import Issue- randomly cutting out first character

[Bertimismaximus]

Whenever I try to import a file in Microsoft Access 2003 (txt, csv, excel) it'll randomly cut off the first character of some of the columns. For example, I have a file with 300,000 rows and states are listed. About 200 of the states are imported as "labama" or "lorida." It seems like this error is completely random. I just patched Microsoft Office and it's still doing it. Anybody know what to do?

[June7]

What is the method of import - wizard, VBA? The error does not repeat on same records in second attempt?

[Bertimismaximus]

I go to "New" then "Import Table" for the import. The error repeats the same records in the 2nd attempt.

I went back to the original file, however, and I can't find any visible difference that would set these error records apart from the others. The data is all standardized, but the errors appear scattered throughout the file (some states don't have errors, others have 8, some have 5 etc...)

[June7]

If it ALWAYS happens with the same records then I conclude there is something wrong with the records, even if not apparent. How is this source data created? I have never heard of such an issue.

Can you attach one of the problem files for analysis, at least enough that includes problem records? Let's see if we can replicate.

[Bertimismaximus]

I exported the data to Excel 2010- then edited it. After editing it I saved as CSV. The problem records in the CSV file look the same to me as all the others.

What's the best way to attach the CSV file for you to check out?

Thanks so much for your help by the way.

[boblarson]

I am thinking that the number of records is potentially the problem. It may be that it will work if you split the text file into smaller text files. What happens if you cut it down to only about 1/4 of the records that you have (less than 65,535)?

[June7]

You export to Excel2010 from where? And then saved as CSV and then import to Access. Why?

Attach files by clicking Go Advanced from the Quick Reply editor. Below the Advanced editor window is the Attachment Manager.

[Bertimismaximus]

Access 2003 doesn't recognize Excel 2010... so files over 60,000 rows I need to save as txt.

Just tried to import a smaller sample, of 40,000 rows rather than 300,000. The same type of error occurred except this time with different rows. This is so crazy...

[June7]

You have Excel 2010 but not Access 2010? Can you save the file in Excel 2003 format? I no longer have Access 2003 available. (2007 at work, 2010 at home). Bummer, Access2007 isn't importing the CSV at all. I saved the csv as an xlsx and that imported. No sign of problem with 51217 records.

[boblarson]

I was able to import all of the records into Access 2003 with no discernable missing characters. It may be that Office 2010 and Access 2003 are fighting each other. It is a known issue that older versions of Office programs don't do so well when installed alongside Office 2010.