When using DataExport to create a CSV file, leading zeros from a character field get truncated

From Support

If you have a question or seek clarification, please call Technical Support.

Problem:

CSV files do not correctly retain leading zeros when opened in MS Excel. This is a MS Excel limitation with CSV files.

Solution:

Microsoft Excel opens .csv files automatically and if it sees a field containing all numbers, it imports that field as a number and because a true number has no need for leading zeroes, strips them off.

You can attack this problem several ways.

1) Remove the .csv extension from the file, or replace it with .txt, then via File>Open and changing the 'file of type' in the dialog to 'All', you should invoke a three step wizard, in which step three will allow you to select the column of data and import it as a text field. This stops Excel from stripping leading zeroes.

2) Import as normal and highlight the data and providing the field you require all have the same number of characters, in you example three. Go to Format>Cells>Custom and in the box type 000 (3 zeroes).

3) Similarly to above, chose a new column and assuming your original data is in A1, enter in the new column =TEXT(A1,"000"), this will convert the number to text and format it similarly to '2'. You should then Copy>Paste Special...Values, your new data over your old and delete the new column.