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:
  1. Change the CSV file to a TXT file:
    1. Replace the CSV extension with TXT.
    2. In Excel, go to File > Open.
    3. Change File of Type to All.
    4. 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.

  2. If the fields with leading zeroes all have the same number of characters:
    1. Select all the cells with leading zeroes.
    2. Go to Format > Cells > Custom.
    3. 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.

  3. If the fields with leading zeroes all have the same number of characters:
    1. 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.

    2. Copy the new field.
    3. Choose Paste Special... > Values to paste the new value over the old field.
    4. Delete the new field.

Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: December 10, 2016