Keep leading zeros in csv and excel in Python

Question

I want the '000100' value keep the same in csv file but it changes to 100 in Excel.

Answer 1

The problem is with excel not considering those zeros, not really the csv. I found this somewhere, perhaps add it and see if it works?

df['column']=df['column'].apply('="{}"'.format)

Answer 2

The csv module isn't doing this, it's the spreadsheet program you loaded it with (the csv module was passed a string after all; it can't even consider dropping leading zeroes). If you open the CSV file in a plain text editor you'll see this.

Given that, your options are:

1. Figure out if your spreadsheet program can customize the import rules to avoid screwing with the data (or after loading, change the column's interpretation rules to something that explicitly says it's text, not a numeric or auto-deduced type).
2. If the program supports recognizing quoted fields as non-numeric, pass quoting=csv.QUOTE_NONNUMERIC to the csv.writer so it quotes all the fields that aren't of numeric type (e.g. these strings) and hopefully stop the editor from treating them as numeric (causing the leading zero stripping).

Answer 3

The csv is probably fine. But you should open it in a text editor (not Excel) and see for yourself.

---

Excel will try to remove leading zeros, but you can force them back by adding a custom format to your column:

A filter of 000000 will force 6 digits to be shown:

---

1. Highlight all the cells you want to format:

2. Click on text text format box

3. Select more number formats

4. Select custom

5. In the Type: box, enter 000000: