Why are leading zeros dropped on a import to Access 2007 from a .csv file?

[jenniferhelen]

I am working in Access 2007 and trying to import a .csv file. One of the fields in the .csv file, called Locations, contains numbers with leading zeros and also the last record contains letters. I am importing into a predefined table and the field Locations is set up with Text formatting. When I import my file, the leading zeros are lost and the last record that contains letters is not loaded. The error I receive is “Type Conversion”.
I would greatly appreciate any help, I am lost and don’t know what to try next.
Thanks in advance.
Jennifer

[Mariostg]

You might want to double check the field type of the database. It really looks like the problematic field is of numeric type. That woud be why the leading zeros are dropped and the the text record is rejected because it tries to place text in a numeric field.

[MMcCarthy]

Also I assume you are linking the csv table to your database. Check what datatypes access is imposing on the csv linked table.

One thing you could try is to change the .csv extension to .txt

It might help

[jenniferhelen]

Hello MMcCarthy,
It is definately a text field format (see attached file, field Loc) that is why I find this so frustrating. I have read on a few other posts that Access looks at the first few records in a load to determine the field type and this seems to be exactly what Access is doing here. The solution written on the other post was to predefine a table with the formats I desire. I have the predefined table and still it is not working. I will try changing the load file to a .txt extension. I am manually loading the file. Once I get this to work I will automate it but I am still in the testing phase. Any other suggestions? I really appreciate it.
Jennifer

[MMcCarthy]

If you are doing a manual import click on advanced and expand the field list to show data type. You should be able to change the data type there.

[jenniferhelen]

The advanced button only gives two columns, field name and skip. If I choose to import to a new table then the advanced button will show other options such as field type. Am I missing something? Is there a way to have the field type appear when importing to an existing table? I can then save my options as a specification for future uploads.

I did change the load file from .csv to .txt and amazingly it worked! I am very grateful to you for this recommendation. Just out of curiosity any idea why the .txt file works? I thought .csv and .txt were basically the same?
Thanks for your help.
Jennifer

[MMcCarthy]

.csv files are managed by Excel and excel rules get imposed which doesn't seem to happen with .txt files. The wonders of Microsoft :)

Regarding the issue of the Advanced Button. The other fields are actually there. If you double click in the join between field name and skip they should expand. If I remember correctly it will expand by one field for each double click.