Leading zeros and CSV files

  • Thread starter Thread starter Dave Williams
  • Start date Start date
D

Dave Williams

We use CSV files to take data from source systems and import them into a
data warehouse. Some of the product codings have changed in certain
countries such that they are numeric (not previously the case) and some have
leading zeros. They need to be opened in Excel to check and perhaps make
some amendments. However the leading zeros disappear giving us wrong product
codings.

Does anyone know of a way to 'update' the csv file so that leading zeros
remain.

Thanks for your help - Dave
 
Dave,

Instead of just opening the csv file try this:

In a new workbook/worksheet
Data -> Get External Data -> Import Text File...
Change the Files of Type to "All Files (*.*)"
Locate your CSV file, select it and hit Import
The text import wizard should appear...
Choose Delimited and hit next
Choose the Delimiter to be Comma and hit next
Change the Format of your data (all columns containing leading zeros)
to Text and hit Finish.
This will open your CSV file and retain leading zeros, Save As a CSV
to save it again.

Dna E
 
Changing the CSV file will not help. The leading 0's are stripped when Excel opens the file and
interprets it. You can try changing the file extension to .TXT (text). Then you can designate
the column as Text, and Excel will leave the values alone. You may want to develop a macro to
open the file and interpret the columns correctly. Turn on the macro recorder and do it
manually, then modify the code to make it more general (i.e. perhaps ask the user for the file
name, etc).
 
Back
Top