Importing data with leading zeros

[buckychudd]

Hi,



We recently upgraded from Access 2003 to 2010. I have a table that I overwrite each month with new data from a tab delimited text file. There is a field in the table that I have formatted the data type as text. In the previous version of Access I could import the data and it would keep any leading zeros during the import. Now it is dropping the leading zeros when I import the data even though I have specifically formatted the data as text.

Does anyone know how I can import my data and keep the leading zeros?

Thanks!

[June7]

What method are you using for the import? The wizard or VBA? Show code. You might have to run an UPDATE query to fix the data.

[buckychudd]

What method are you using for the import? The wizard or VBA? Show code. You might have to run an UPDATE query to fix the data.

I'm using the wizard.

[June7]

So if the wizard won't import the leading zeros even if data is going to a text field and this is specified in the setup, then only suggestion I have is to run an UPDATE query on the field. Use Format function.

[buckychudd]

So if the wizard won't import the leading zeros even if data is going to a text field and this is specified in the setup, then only suggestion I have is to run an UPDATE query on the field. Use Format function.

That kind of sucks, it worked correctly in 2003.

[June7]

I tested import of CSV and TXT files with 2007 and 2010. Both brought the data in with the leading zeros intact. Sorry, cannot replicate your issue. Do you want to provide the file and I will test it as well.

[buckychudd]

The weird thing is if I import to a new table it works fine. If I do an append import into my existing file it doesn't work. I can import into a new table then append that table into my existing file, but it worked much better before as a one step process where now it is a two step process.

[June7]

I tried the APPEND, still can replicate your issue.