Re: Leading 0's in a CSV file

[Douglas Handy <dhandy@xxxxxxxxx>]

Mike,

> I have created a CSV file and this particular record looks
> like"0006075", but when it is opened(through EXCEL)  it shows as 6075.

This is a "feature" of Excel's handling of CSV files.  No matter
whether you use quotes or not, if the contents looks like a number,
then Excel treats it as a number by default.  The same thing happens
with zip codes, for example.

One could argue that Excel shouldn't do that when a data element is
quoted, but there is no real industry standard when it comes to CSV
formats so it is hard to claim they don't follow the standard.  One
could just as easily claim IBM doesn't correctly handle CSV files with
embedded double quote characters, such as a description containing 3"
widget or a height of 5' 2" or whatever (which BTW, Excel can handle
just fine).

The nice thing about standards like CSV, is there are so many to choose from...

Quite simply, you can't coerce Excel to automagically open a CSV file
and treat that data as text.  However, you can do it using Excel's
text import wizard.  On my machine, that is under menu Data -> Import
External Data -> Import Data ... then selecting a delimited import and
following the wizard prompts.  On step 3 of 3 you can identify what
data format to use for each column (independently), and making the
appropriate columns "Text" will preserve the leading zeros.  The
default is "General", which is described as "converts numeric values
to numbers, date values to dates, and all remaining values to text".

I don't know of a way to change the default from General to Text.  I
think to get the data to import correctly automagically, you'd need to
use a different format than CSV.  AFAIK

Doug