CSV files - keeting leading zeros
Posted by Bob Ziegler on January 17, 2002 11:10 AM
I have a ten digit number that needs to start with 2 zeros. I formatted the column as text. Whether I do this in excel or in the csv file, the format of the cells changes from text to general when I save and reopen the file, dropping the 2 leading zeros.
Posted by Mark W. on January 17, 2002 11:14 AM
Consider using .txt extension instead of .csv [nt]
Posted by Bob on January 17, 2002 11:23 AM
Re: Consider using .txt extension instead of .csv [nt]
I am trying to import the csv file into an ERP system that requires a csv file format.
Posted by Jack in UK on January 17, 2002 11:29 AM
Then darn Zero's
Hi--
Correct me if im wrong you open a CVS to data which reports ten characters ie 0012345678 Right?
OK you open and get 12345678 Right?
So you need some majic to format or jig excel to 00 start and them 8 charcters. If so i can do this with a cool formula i wrote, ill look to see if here, you need to add it in VBA - can you do this?
Then add a row and use my formual and that will give a 0012345678 you wish.... BUT
Ecxel will left hook the data not right but will add and will vlookup ok, you can right aling thou:
Does this help?
RDGS
Jack in the UK
Posted by Mark W. on January 17, 2002 2:30 PM
Let me elaborate...
You can continue to save the file as .csv for your
ERP system, but when you want to open the file (or
if you prefer, a copy of the file) in Excel first
change its extension to .txt and then open the file
using Excel's File | Open... menu command. This
approach will invoke the Text to Columns... wizard
and allow you to apply a Text format to those
columns containing numeric values with leading zeros.
This is the only way to apply Text formatting to
a clean worksheet that will then be populated with
the contents of a text file.
