Leading Zeros in CSV

[Krupa.r]

Hi There,

 

I am writing to a CSV file. One of the column is alphanumeric but some of the values in it are just numbers starting with zeros eg., 000128xxxx. When I write to the file, i want to retain these leading zeros. I have tried double quotes, single quote, Lpad, prepending =" ", adding to_char while retrieving data, prepending blank space etc. I am unable to achieve desired output. This output csv file will manually be compared/pivoted with another excel where this number is stored as text.  So, I have to retain the zeros and possibly store it as text without any additional characters being displayed. How can this be handled?

 

Thanks !

[Eugene Greiff]

Place an apostrophe character before the leading zero.  Values with an apostrophe or single quote mark (') must be contained in double quotes.

 

V/r,

Gene

[Krupa.r]

Thanks Gene. I already tried apostrophe character before the leading zero. The output file displays the apostrophe character before the data.

The apostrophe trick only works when entering data directly into Excel. When writing it in a CSV file, the apostrophe appears in the field.

[Eugene Greiff]

Yep sure does.

 

Ok,

 

Here is my test file:

'

 

And the results from opening the csv in Excel.

 

 

 

So what worked is ="0005"

 

V/r,

Gene

[Krupa.r]

Yes, ="0005" worked for me too. But the problem with that is, the value gets reformatted (zeros trimmed) if you make any change to the file and save it once.

[Eugene Greiff]

If you do a save as and select the  Excel Workbook, it will maintain the format.

 

Saving it back as a CSV rewrites the file and Excel strips the special ="00005" as a feature not compatible with CSV.

 

V/r,

Gene

[Krupa.r]

Thank you Gene. Saving the file as xls after opening as it is in csv is a good workaround. Adds additional work for users though.

 

Appreciate your help ! Thanks very much !

[mike2.2]

MS Excel like to strip leading zero's from numbers. it just how it is. That said, once your file has been saved, you can import the data into MS Excel and during the importation set the field to a text field and the zero's will remain.  I had this problem as well.

 

From the Data Tab select "From Text"

 

Select  your data file (csv file)

Press the "Import" button

From the Text Import Wizard (Step 1 of 3), seelct "Delimited"

Press the "Next" button

Select  "Comma"

Press the "Next" button

On the Text Import Wizard - Step 3 of 3 screen, highlight the field with leading zeros and set it as a "Text" field, then press the "Finish" button

 

 

Then press the "OK" button

Regards,

Mike