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?

Balcer

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

Win

One side-note: this seems to work for anything EXCEPT values that are over 253 or so characters long (after 255 charcters, Excel stops looking for the closing quotes and gets confused...?) – Concatenation

curiously, while this works: 000,"=""001,002""" this does not: 000, "=""001,002""" – Hatching

@eyaler: Excel only checks for a "delimiter-protection" quote in the very first character. If a quote doesn't appear in the first character, the field is parsed as usual, with newlines being end-of-record, commas being end-of-field, and everything else literal (including quotes). Also note that the corresponding end-quote for delimiter protection does NOT have to be the last character in the field; it just has to match the opening quote. After the end-quote, parsing rules are back to normal for the rest of the field. – Win

hmm, as noticed above, this method indeed fails when the string is over 253 characters. any suggestions? – Hatching

@eyaler: You can split your long string into smaller chunks and concatenate them using the & operator. For example, "=""123""&""456""&""789""" results in the string 123456789. Beware that Excel 2003 and earlier had a limit of 1024 characters in a formula. Excel 2007 and later increase the limit to 8192. – Win

@johnY that worked thanks! and the limit should be 255 not 253 in my remark above. – Hatching

Thank you for this. One question. I am doing some EDI, and if the target company wants to view the excel file visually, this is perfect. What if they want to then take the same file and programatically insert it into their database. Will the =" be included and mess with their algorithms? – Dail

@JClark4321: It depends on how they are reading in the data, but most likely yes, I would expect that the equals sign and quotes will show up and the data won't pass their validation. – Win

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

Fives

Note that while a prefixed space won't work, a prefixed non-breaking-space "\xA0" will - and has the advantage that it's not a visible character. (Nor does it mess up editing the way a prefixed tab character does). – Mahlstick

Why won't "E" work? In understand it's used in numbers for scientific notation, but never as the first character, so as a prefix it should trigger interpretation as a string, like all other letters, surely? – Mahlstick

Answer 3

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.

Attenweiler

Answer 4

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

Balcer

Answer 5

Pretty old thread but why don't you just add whitespace after your value. It will be then treated as string and no leading zeros will be stripped.

"001,002"." "

Winsor

So, yeah - changed my sql query to: s.TextColumnThatLooksLikeANumber + ' ' – Inconvincible

Answer 6

Since no-one mentioned it already, figured it was worth mentioning it in this old post.

If you add a horizontal tab character \t before the number, then MS Excel will also show the leading zero's. And the tab character doesn't show in the excel sheet. Even if it's surrounded by double-quotes. (F.e. \"\t001,002\")

It also looks nicer in Notepad++, compared to putting a \0 aka NULL before such number.

Scala

I tried nulls, they didn't work. Tab characters produce a problem: If you try to edit the field, the cursor position is off (to hard to explain here, just try it!). If initial whitespace is not a problem, you can prefix a "\xA0" non-breaking-whitespace character instead - that will work, and it isn't a visible character. – Mahlstick

Answer 7

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.

Attenweiler

Answer 8

I was fiddling around with CSV to Excel (i use PHP to create the CSV, but i guess this solution works for any language. When you spot that a leading characters (such as + , - or 0 are disappearing, create the CSV with chr(13) as a prefix. This is a non printable character and it works wonders for my Excel Office 2010 version. I tried other non printable characters, but with no luck.

so i use Chirp Internet solution but tweaked with my prefix:

if (preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
    $str = chr(13)."$str";
}

Blank

Answer 9

If you are using "Content-Disposition" and exporting from asp to excel using HTML tags,then you have to add "style='mso-number-format:\@;'" to that tag and making it to accept only Text values ,thereby leading zeroes omission will be avoided,If Forward slash"\" is accepted use double forward slash "\"

Obsolescent

Answer 10

All the suggested answers don't seem to work for me right now ("=""blahblah""" and others) in all current Excel versions or Numbers app on OS X.

The only solution I found to be working by fiddling around is to add an escaped null character at the beginning of the string (which is \0 in PHP or C based languages). Everything ends up treated as is without being calculated or processed by the software when opening the calc sheet.

echo "\0" . $data;

Uribe

Answer 11

Excel uses a default formatting for CSV columns depending on the content. So if you have 001 in a csv, excel will automatically turn it to 1.

The only way to keep the leading zeros in excel from a csv file is by changing the extension of the csv file to .txt, then just open excel, click on open, select the txt file, and you'll see the Text Import Wizard. Select your csv format (separated by commas), then just make sure you select "Text" as the format.

And that's it, now you can export that previous csv data to any other while keeping the leading zeros.

Riojas

Answer 12

This is straightforward using Excel's Power Query functionality that allows you to perform step-by-step transformations.

Original File:

Add a Custom Column:

Barracuda