Create a CSV file with leading zeros which Excel never removes

We all know Excel's annoying behavior of removing leading zeros from imported CSV files

I am a developer and I'd like to program an export feature that creates a CSV file that forces Excel to keep the leading zeros.

I hoped "012345" would result in 012345, but even then Excel 2010 strips the leading zero ;(

Naming the file *.txt instead of *.csv forces Excel to use the wizard as suggested. But I really doubt that the user knows how to operate the wizard. He must change the data type from "General" to "Text" at one of 30 columns. I believe the user will just click Next, Next, Finish.

Any better idea that avoids using the wizard?

Is there an official character to "mask" a zero while importing a CSV file into Excel? I've tried ' (Typewriter apostrophe). But it's not the same to enter '0123 in Excel compared to open a CSV with '0123.

3 Answers

Thanks for all the comments and answers! Credit goes to user Ron Rosenfeld whose recommendation led to this implementation:

This works, and the number is still treated as a number when referenced by formulas:

Example number to display in CSV is 00141.
CSV file line:

field1,"=""00141""",field3

The only further ideas I can come up with are:

• Distribute Excel files instead of CSV

• For your users, install your own viewer for CSV files. There are many such free viewers, or you may write your own as a script or a program that will correctly import the CSV to a temporary Excel spreadsheet.

(This does no longer work:)

The only way to avoid Excel removing the zeroes when importing numerical data, is not to have numerical fields.

You may achieve this by enclosing these fields with double-quotes so Excel will interpret them as text, like this : "000123".

You can do this, as you control the export.