By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

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

P: 15
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
Sep 27 '10 #1
Share this Question
Share on Google+
6 Replies


100+
P: 332
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.
Sep 27 '10 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Sep 27 '10 #3

P: 15
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
Attached Files
File Type: zip Doc1.zip (145.7 KB, 111 views)
Sep 27 '10 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Sep 27 '10 #5

P: 15
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
Sep 28 '10 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
.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.
Sep 28 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.