Access is not importing leading zeros!!!!!! (1 Viewer)

[gblack]

Ok this one is driving me crazy! I have had this issue before and it seemed to aleyas be just a matter of setting the correct data type (i.e., text) vs. trying to import the value as a number...

I am downloading data from an application. If I try to DL the data as an excel spreadsheet or csv for mat, the zeros get chopped off. So my only option is txt. When I DL the data in tab delimited .txt format, the download works.

However, when I push it into Access... Access drops the leading zeros, even if I manually set the datatype for the field as text.

How can I get Access to import the data without trying to think for me and assuming this value doesn't need leading zeros?!?! Is it possible I have some setting checked that is doing that for me and I don't know about it? I am currently at a new job and they have some backwards equipment here.

BTW: I am simplifying this example just to make it easier to digest... I actually have several fields and several imports/DLs where this is the case and some other currency fields I need to set by hand as well...

 

[John Big Booty]

Access will not hold leading zeros in a numeric field you will need to play around with the formatting of the field. This relies on the length of your number being consistent.

If for example the majority of your values are four digits long and you want those that are not to have leading zeros use "0000" as your format. This will cause any value with less than four digits to be padded with leading zeros.

 

[plog]

I've imported various versions of Excel files into various versions of Access and one thing I've found that helps when importing data is to put a dummy row of data at the top of the Excel file. If I want the column to be imported as text the dummy data i put in is "ZZZZ". This makes Access want to import the field as text and makes finding that dummy record easy when I need to delete it after the imports done.

Now, the big question--does this data really need to be text? You can always control the output and insert leading 0's with the Format() function.

 

[Alansidman]

Another alternative is to bring them in as text with no leading zeros. In a query concatenate in a new field a series of zeros, perhaps 8 of them to the number you imported. Then in another new field, perform a right function for the length of the field you want to have. It will have the leading zeros in place. You can then run an update query to change your table. A bit convoluted but it can be done and very quickly.

Alan

 

[Galaxiom]

Another alternative is to bring them in as text with no leading zeros. In a query concatenate in a new field a series of zeros, perhaps 8 of them to the number you imported.....

Why would you go to that complexity instead of using the Format function?

 

[Alansidman]

Galaxiom--excellent point. First thought as an alternative. Didn't think about Format. Thanks. Some days the brain works slower than other days.

 

[Galaxiom]

I know can sometimes I go off on a tangent if there is a potential solution that involves a technique I had been using a lot and I completely miss the obvious.

It is important to try to approach every new problem with a clean slate but it isn't always easy to avoid the "scratches".

 

[kevt1981]

Just registered to reply to this thread as I've encountered a similar situation in Access 2010 (I've also tried Changing Text Format and that didn't help) but I've now found a solution, so would like to share it with everyone.

The problem as importing text file to Access table using Import Text Wizard wizard and the leading zero gets dropped.

The way I got around it was in Import Text Wizard, go to Advanced and tick "Leading Zeroes in Dates" (that's right, doesn't really make sense) :banghead:.