Problem:
When you open CSV files in Microsoft, they do not retain the leading zeros from DataExport/400.
Solution:
This is a limitation in Microsoft Excel. Excel opens CSV files automatically and, if a field contains all numbers, imports that field as a number. Because leading zeroes are unnecessary for a true number, Excel strips them off.
You can work around this problem in several ways:
- Change the CSV file to a TXT file:
- Replace the CSV extension with TXT.
- In Excel, go to File > Open.
- Change File of Type to All.
- Go through the three-step wizard and, in step three, select the column of data and import it as a text field.
Excel no longer strips the leading zeroes.
- If the fields with leading zeroes all have the same number of characters:
- Select all the cells with leading zeroes.
- Go to Format > Cells > Custom.
- In the format box, type the number format for the field, using zeroes.
For example: If the fields should all have three characters, including leading zeroes, type 000 as the format.
- If the fields with leading zeroes all have the same number of characters:
- In a new column, duplicate the field and format it with the correct formatting.
For example: If the field is A1 and fields should all have three characters, type =TEXT(A1,"000") in the new field.
- Copy the new field.
- Choose Paste Special... > Values to paste the new value over the old field.
- Delete the new field.
Last Modified On:
No, open a new Support Case