Importing numbers with leading zeros into Excel 1

[mmsbasd]

I have a fixed text file with 11 digit numbers that begin with zeros. Here's an example:

DMH000000012910000000129200000001293
DMH000000012940000000129500000001296
DMH000000012970000000129800000001299

I've created a import specification file but Excel drops the leading zeros, even though the specification lists 11 digits. The only way I was able to get it to work was to import it as TEXT. My problem is that I need them as numbers and, since these Excel files will be updated and linked to Access databases, I don't relish the thought of converting the format of the column back and forth from TEXT to NUMBER all the time.

Is there a way to configure the import specification to bring in the numbers as NUMBERS, WITH the leading zeros? If so, how? Thanks.

 

[Hasit]

I am not aware of a way to achieve what you want. However, you could consider automating the conversion from text to numbers by using a macro.

 

[mmsbasd]

In this case, my problem is that I need to work with the numbers, as numbers, in Access and yet be able to export back to a fixed text file later, with the leading zeros. If all of the actual numbers were the same length, I could have created a dummy zero field and stick it into the export file. Unfortunately, the real data has differing lengths.

Most likely, I'll need to create a macro that imports them as text and them copies them to another table, changing these NEW fields as numbers. I can then work with the numbers THERE and still maintain the original format within the first table. Not elegant but a solution. Thanks again.

 

[xlbo]

Just to back up Hasit - there is no way to have leading zeros as numbers in excel. There is a function that may be useful to you tho
=REPT("0",11-LEN(A1))&A1

where your "Number" (ie without leading zeros) is in A1
This will be text but you will also have your imported numbers to work with
HTH
Geoff

 

[PeterMoran]

Hi mmsbasd,

After you have imported your text you can format the numbers so that they show leading zeros - up to 11. Select the area to format once you have loaded the data, and then select Format, Cells, Number, Custom, and you should see some wierd formats. Select a similar one say "#,##0" and then modify it to "00000000000" on the Type line and then select OK. There are further considerations if you can have negatives, but I suspect this is not the case here.

You should now have what you requested.

I don't know how to set up a template with this formatting included so that you could load the data into the template. May be someone else can assist in that area.


Good Luck!

 

[xlbo]

Nice one Peter - completely forgot about that - and it stays as a number - have a star
Don't think you can set it up as import but if you run
sub formatleadingzeros()
lRow = range("A65536&quot.end(xlup).row
range("A2:A" & lRow).numberformat = "00000000000"
End sub
it will convert your numbers to have leading zeros
NB - change A2 to A1 if you have no header row
Rgds
Geoff

 

[mmsbasd]

I already tried configuring the FORMAT setting to '00000000000' and it works as long as you keep the data in Access. The moment I export the data as fixed text, the resulting file has the leading zeros removed. Now, if the export filter gave you the ability to add leading zeros to any number field, like it does for the date (which I use), I would be done.

Unfortunately, I have to keep the whole process in Access, so I can't look at options like exporting to an interim format that would support the leading zeros.

Thanks.

 

[xlbo]

Actually, the code I posted was a conversion to leading zeros format, as suggested by Peter, in excel - It won't happen as part of the import spec but if you are coding the export then adding the lines I suggested AFTER the import has been done, will convert your numbers to have leading zeros. If you then save the excel file as .txt, leading zeros are kept
HTH
Geoff

 

[mmsbasd]

I wound up importing the information as TEXT and then making an update query to copy the information to other fields, which were formatted as NUMBER. This gave me both types to work with, as needed. Not the most eloquent solution but it did the trick. Thanks to all for the suggestions. Michael
BASD IS/IT Dept