leading zeroes in CSV files

[JohnH]

I have an application that creates CSV files. Some of the fields in
this file have leading zeroes. When I opened the file in XL the
leading zeroes were deleted. I thought that I could avoid having the
leading zeroes deleted by placing an apostrophe in front of the fields
that contain the leading zeroes, but when I did that the zeroes
remained and so did the apostrophe. When I edit the cell (F2-enter)
the apostrophe disappears, but I don't want to ask the XL users to do
that. Does anyone know of a better way? Thanks.

 

[Dan E]

John,

To get the file into excel go:
Data -> Get External Data -> Text Files
In the file open dialog change the file type to All Files (*.*) and find
your text file. Click Import. This will open the Text import wizard.

Step 1 -> Choose Delimeted -> hit next
Step 2 -> Choose comma as your delimiter -> hit next
Step 3 -> Set the format of your columns with leading zeros to
text. -> hit finish

Your text file should now be imported with the leading zeros
preserved.

Dan E

 

[Dave Peterson]

If you rename your file to .txt, then when you open it (file|open), you'll see
the text to columns wizard and you can specify each field the way you want.

(Text for this one.)

 

[JohnH]

Thanks for your help. The problem is that my application posts the csv
file to a web server and ideally the users can click on the csv file
and have it open in excel. I tried preceeding the first zero with a
space but excel still treated the field as a number and stripped off
the leading zeroes. I may have to use the apostrophe approach
(preceeding the field with an apostrophe). It isn't pretty but it
works and the user doesn't have to do anything. Thanks again for your
suggestion.

 

[Dave Peterson]

Maybe you could have your .csv file create the field as a formula:

="0001234",="000234"

The values will be brought in as formulas and will evaluate to text--but they'll
look pretty for you.