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

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.)

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 columns 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\"" ;