Create a CSV file with leading zeros which Excel never removes

Question

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 https://www.youtube.com/watch?v=9KDK1FRcmSo 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.

record yourself importing the csv with the wizard, making the column with the numbers text on import. Then you can modify that code to do it repeatedly. — Scott Craner, Jul 12 '19 at 16:50
How the user operates Excel is out of my control. Most users will just double click the CSV file and then it's too late... — OneWorld, Jul 12 '19 at 16:52
Then the answer from your linked question is the answer to this question. It is not possible. One needs to use the wizard where one can set the format of the column BEFORE opening the text file. — Scott Craner, Jul 12 '19 at 16:53
Prepend the numeric string with some character that won't show up to the end user. You could use the `NBSP`. Experimentation suggests that the `tab` (`CHAR(9)`) as well as multiple other characters in the `<32` code range (but not all) will also work. Or you could save it as `Unicode text` and use the Unicode `ZWSP` character. — Ron Rosenfeld, Jul 12 '19 at 18:40
`TAB` is most promising! I now have to double check that it doesn't cause harm at any other point. — OneWorld, Jul 12 '19 at 19:30
@RonRosenfeld Works for Excel. However, my application to print labels reads the tab and now the ZIP is printed at another place. At least now I can give the user the option "Prepend tab to preserve leading zeros in Excel". Haven't found any better character — OneWorld, Jul 12 '19 at 19:58
So the problem has to do with your label printing routine. Maybe change to ignore the character immediately preceding the zipcode. or save it as Unicode with `ZWSP` pre-pended. Also, you could use the `NBSP` with a standard csv which would only offset the Zip code printing by a single space. — Ron Rosenfeld, Jul 12 '19 at 21:21
@OneWorld,, since you are a developer then better go with VBA to import CSV file and apply *Leading Zero cell format to an appropriate Column,,* ☺,,otherwise the *You Tube* link is the solution !! — Rajesh Sinha, Jul 15 '19 at 08:44
@RajeshS Thanks for your input! However, it's out of my control how the user imports the CSV file. In many companies VBA is not accepted to solve such minor issues due to the security issues that come with VBA — OneWorld, Jul 15 '19 at 09:30

score 3 · Accepted Answer · answered Jul 15 '19 at 15:31

3

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

answered Jul 15 '19 at 15:31

OneWorld

171
2
11

score 3 · Answer 2 · edited Jul 06 '21 at 02:35

3

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

edited Jul 06 '21 at 02:35

zx485

2,170
11
17
24

answered Jul 02 '21 at 00:29

TimmyB

31
1

Interesting, but looks even less suitable for further processing in programs other then Excel. My feature is in production now with the TAB-Version. Won't change it now – OneWorld Jul 05 '21 at 15:11

score 1 · Answer 3 · edited Jul 12 '22 at 18:27

1

Try:

="0123"

So:

{equal}{quote}{numbers}{quote}

BTW: no colon, no blanks

edited Jul 12 '22 at 18:27

Greenonline

2,235
11
24
30

answered Jul 12 '22 at 18:17

click2flush

11
2

1

Formatting your post correctly makes it easier to read and understand – Greenonline Jul 12 '22 at 18:28

harrymc · Answer 4 · 2019-07-12T18:21:53.190

0

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.

edited Jul 12 '19 at 18:21

answered Jul 12 '19 at 17:05

harrymc

455,459
31
526
924

OP says *"I hoped "012345" would result in 012345, but even then Excel 2010 strips the leading zero"*. Maybe using a single quote (`'012345`) might work? – cybernetic.nomad Jul 12 '19 at 17:10
@cybernetic.nomad that results in `'012345` which isn't also good for further processing like printing labels for letters where the ZIP should be `012345` and not `'012345` – OneWorld Jul 12 '19 at 17:19
Some more ideas added. – harrymc Jul 12 '19 at 18:22

Create a CSV file with leading zeros which Excel never removes

4 Answers4