CSV for Excel, Including Both Leading Zeros and Commas

Question

I want to generate a CSV file for user to use Excel to open it.

If I want to escape the comma in values, I can write it as "640,480".

If I want to keep the leading zeros, I can use ="001234".

But if I want to keep both comma and leading zeros in the value, writing as ="001,002" will be splitted as two columns. It seems no solution to express the correct data.

Is there any way to express 001, 002 in CSV for Excel?

Answer 1

Do

"""001,002"""

I found this out by typing "001,002" and then doing save-as CSV in Excel. If this isn't exactly what you want (you don't want quotes), this might be a good way for you to find what you want.

Another option might be use tab-delimited text, if this is an option for you.

Answer 2

Put a prefix String on your data:

 "N001,002","N002,003"

( As long as that prefix is not an E )

That notation ( In OpenOffice at least) above parses as a total of 2 collums with the N001,002 bytes correctly stored.

CSV Specification says that , is permitted inside quote strings.

Also, A warning from experience: make sure you do this with phone numbers too. Excel will otherwise interpret phone numbers as a floating point number and save them in scientific notation :/ , and 1.800E10 is not a really good phone number.

In OpenOffice, this RawCSV chunk also decodes as expected:

  "=""001,002""","=""002,004"""

ie:

   $rawdata = '001,002'; 
   $equation = "=\"$rawdata\"";
   $escaped = str_replace('"','""',$equation); 
   $csv_chunk = "\"$escaped\"" ;

Answer 3

Looking more at the Excel spreadsheet it looks what you want can't be done using CSV.

This site http://office.microsoft.com/en-us/excel/HP052002731033.aspx says "If cells display formulas instead of formula values, the formulas are converted as text. All formatting, graphics, objects, and other worksheet contents are lost. The euro symbol will be converted to a question mark."

However, you can change how you load it to get the result you want. See this web page: Microsoft import a text file.

The key thing is to choose Import External Data-Import Data-Text Files, go Next, Next, and then tick "Text" under column data format. This will prevent it being interpreted as a number, and losing formatting.

Answer 4

A reader of my blog found a solution, ="001" & CHAR(44) & "002", it seems workable on my machine!

Answer 5

Kent Fredric's answer contains the solution:

  "=""001,002"""

(I'm bothering to post this as a separate answer because it's not clear from Kent's answer that it is a valid Excel solution.)