Converting Excel to CSV Without Losing Leading Zeros
pandas turned a text cell 02134 into 2134 and in2csv did too; qsv kept it. A number formatted 00000 loses its zeros everywhere unless you rebuild them.
"Convert this spreadsheet to CSV" arrives with a hidden assumption that the conversion is mechanical. It is not, because a cell holds two things, a stored value and a way of displaying it, and CSV can hold only one of them. A zip code typed as 02134 and stored as the number 2134 with a format that pads it to five digits shows 02134 on screen and exports as 2134. This article builds a small workbook with every variety of the problem, runs it through four readers, and ends with a script that exports what the sheet displays and tells you what it could not trust.
The workbook
Three people and nine columns. Two zip columns hold the same five digits in different ways: zip_text holds text cells, and zip_number holds the numbers 2134, 10001 and 0 with the number format 00000, which displays them as 02134, 10001 and 00000. The others are the usual suspects: an account number of 19 digits, a percentage, a date, a decimal, a formula and a boolean. The cells are what you would see in a spreadsheet:
import datetime as dt
import xlsxwriter
OUT = "/batches/csv-0001-0200/work/conv/zips.xlsx"
wb = xlsxwriter.Workbook(OUT)
ws = wb.add_worksheet("data")
zip_fmt = wb.add_format({"num_format": "00000"})
txt = wb.add_format({"num_format": "@"})
pct = wb.add_format({"num_format": "0.0%"})
day = wb.add_format({"num_format": "yyyy-mm-dd"})
money = wb.add_format({"num_format": "#,##0.00"})
ws.write_row(0, 0, ["name", "zip_text", "zip_number", "account", "ratio", "joined", "amount", "total", "flag"])
data = [
("Ana", "02134", 2134, 1234567890123456789, 0.125, dt.date(2025, 3, 1), 1234.5, 3, True),
("Bo", "10001", 10001, 12345678901234567890, 1 / 3, dt.date(2025, 3, 2), 0.1 + 0.2, 4, False),
("Cy", "00000", 0, 99, 0.5, dt.date(2025, 12, 31), 100, 5, True),
]
for i, (n, z, zn, acct, ratio, d, amt, tot, flag) in enumerate(data, start=1):
ws.write_string(i, 0, n)
ws.write_string(i, 1, z, txt)
ws.write_number(i, 2, zn, zip_fmt)
ws.write_number(i, 3, acct)
ws.write_number(i, 4, ratio, pct)
ws.write_datetime(i, 5, dt.datetime.combine(d, dt.time()), day)
ws.write_number(i, 6, amt, money)
ws.write_formula(i, 7, f"=B{i+1}+0*1+{tot}", None) # no cached value supplied
ws.write_boolean(i, 8, flag)
wb.close()
print("made", OUT)pandas
import re, sys
import pandas as pd
from openpyxl import load_workbook
PATH = "/batches/csv-0001-0200/work/conv/zips.xlsx"
print("1. pandas.read_excel with defaults")
df = pd.read_excel(PATH)
print(df[["name", "zip_text", "zip_number", "account", "ratio", "joined", "amount"]].to_string())
print(df.dtypes.to_dict())
print("\n2. pandas.read_excel(dtype=str)")
print(pd.read_excel(PATH, dtype=str)[["zip_text", "zip_number", "account", "ratio", "joined"]].to_string())python lab/conv/t0150.py1. pandas.read_excel with defaults
name zip_text zip_number account ratio joined amount
0 Ana 2134 2134 1234567890123457024 0.125000 2025-03-01 1234.5
1 Bo 10001 10001 12345678901234569216 0.333333 2025-03-02 0.3
2 Cy 0 0 99 0.500000 2025-12-31 100.0
{'name': <StringDtype(na_value=nan)>, 'zip_text': dtype('int64'), 'zip_number': dtype('int64'), 'account': dtype('uint64'), 'ratio': dtype('float64'), 'joined': dtype('<M8[us]'), 'amount': dtype('float64'), 'total': dtype('int64'), 'flag': dtype('bool')}
2. pandas.read_excel(dtype=str)
zip_text zip_number account ratio joined
0 02134 2134 1234567890123457024 0.125 2025-03-01 00:00:00
1 10001 10001 12345678901234569216 0.3333333333333333 2025-03-02 00:00:00
2 00000 0 99 0.5 2025-12-31 00:00:00
3. openpyxl: the value and the number format are separate
B2 s '02134' format '@'
C2 n 2134 format '00000'
C4 n 0 format '00000'
D2 n 1.234567890123457e+18 format 'General'
D3 n 1.234567890123457e+19 format 'General'
E3 n 0.3333333333333333 format '0.0%'
F2 d datetime.datetime(2025, 3, 1, 0, 0) format 'yyyy-mm-dd'
G3 n 0.3 format '#,##0.00'
4. Rebuilding what the sheet displays for a number with a 00000 format
['02134', '10001', '00000'] <- zip_number as displayed
['02134', '10001', '00000'] <- zip_text as stored
5. A formula that was written without a cached value
data_only=False: H2 = '=B2+0*1+3'
data_only=True: H2 = 0The first block of the output is read_excel with defaults, and it is not good. zip_text, whose cells are text and contain 02134, came back as int64 with the values 2134, 10001 and 0. pandas (read_excel) runs the same type inference on an Excel column that it runs on a CSV column, so digit-only text becomes numbers even though the file said otherwise. zip_number (numbers) became 2134, 10001 and 0 too. The account number 1234567890123456789 came back as 1234567890123457024, which I come back to.
The second block uses dtype=str. zip_text is now right (02134, 10001, 00000), because the cells were text. zip_number is 2134, 10001 and 0: pandas reads the stored value and ignores the number format, so the padding is lost. The date comes back as 2025-03-01 00:00:00 and the ratio as 0.3333333333333333, which is the value and not the 33.3% that the sheet displays.
openpyxl: the value and the format are separate
The lower-level library shows the cell as it is stored:
B2 s '02134' format '@'
C2 n 2134 format '00000'
C4 n 0 format '00000'
E3 n 0.3333333333333333 format '0.0%'
F2 d datetime.datetime(2025, 3, 1, 0, 0) format 'yyyy-mm-dd'(Those are lines from the third block of the output.) B2 is a string; C2 is the number 2134 and its format is 00000; E3 is a third and its format makes it display as a percentage. Because the format is separate, you can reconstruct the display. For a format made only of zeros, the number of digits shown is the number of zeros:
def shown(cell):
"""What a zero-padding format displays; anything else falls back to the stored value."""
v, fmt = cell.value, cell.number_format
if isinstance(v, (int, float)) and re.fullmatch(r"0+", fmt):
return str(int(v)).zfill(len(fmt))
return v if v is not None else ""
print("\n4. Rebuilding what the sheet displays for a number with a 00000 format")
print(" ", [shown(ws[r]) for r in ("C2", "C3", "C4")], "<- zip_number as displayed")
print(" ", [shown(ws[r]) for r in ("B2", "B3", "B4")], "<- zip_text as stored")The fourth block of the output: ['02134', '10001', '00000'] for the number column and the same for the text column. That is what the sheet shows. It is a rule for one format family (0, 00, 00000), and it is a rule about this column. Formats like 0.00, #,##0 or @ need other rules, and a general version means implementing the number-format mini-language, which I would not do: decide per column which ones are identifiers and handle those.
Command-line converters
The tools that turn a workbook into a CSV in one command make their own choices, and the first two differ:
in2csv work/conv/zips.xlsxname,zip_text,zip_number,account,ratio,joined,amount,total,flag
Ana,2134,2134,1.234567890123457E+18,0.125,2025-03-01,1234.5,False,True
Bo,10001,10001,1.234567890123457E+19,0.3333333333333333,2025-03-02,0.3,False,False
Cy,0,0,99,0.5,2025-12-31,100,False,Trueqsv excel work/conv/zips.xlsxname,zip_text,zip_number,account,ratio,joined,amount,total,flag
Ana,02134,2134,1234567890123457024,0.125,2025-03-01,1234.5,0,true
Bo,10001,10001,1.234567890123457e+19,0.3333333333333333,2025-03-02,0.3,0,false
Cy,00000,0,99,0.5,2025-12-31,100,0,true
3 9-column rows exported from "data" sheet| Column | in2csv | qsv excel |
|---|---|---|
zip_text (text cells) | 2134 | 02134 |
zip_number (format 00000) | 2134 | 2134 |
account (19 digits, first row) | 1.234567890123457E+18 | 1234567890123457024 |
total (formula, no cached value) | False | 0 |
flag (boolean) | True | true |
in2csv wrote the text cell without its zero, so it applies number inference to text. qsv excel kept the text as text. Neither reconstructs the 00000 format. For the formula, in2csv wrote False and qsv wrote 0, and for the 19-digit account number one used scientific notation. Neither tool said anything about it. The lesson is not which tool is right; it is that a single in2csv call is a conversion with defaults you have not looked at.
The numbers Excel already rounded
An account number of 19 digits cannot be stored in a spreadsheet cell as a number. Excel stores numbers as IEEE doubles, which hold about 15 significant digits exactly (Microsoft documents the limit). The workbook above holds 1.234567890123457e+18; pandas prints it as 1234567890123457024 (the double converted to an integer), and the last digits are noise. If the number came in from a typed cell, the rounding happened when it was typed, and no converter can recover the original digits. Identifiers longer than 15 digits belong in text cells, and a column of them that is already numeric means the source needs fixing. The script below warns on any number at or above 2^53 so the loss is visible.
Formulas and their cached values
A formula cell holds the formula and, if the file came from Excel, the last result Excel calculated. A library that writes a workbook without calculating it stores no result. Reading the workbook:
data_only=False: H2 = '=B2+0*1+3'
data_only=True: H2 = 0With data_only=False, openpyxl returns the formula text. With data_only=True, it returns the cached value, which was 0 here because the writer supplied none. That is what in2csv and qsv also reported (False and 0). A file saved by Excel will normally have real cached values; a file generated by a script may not, and the wrong answer arrives as a plausible number. If a column is a formula, check a few values by hand.
A script that exports what the sheet shows
Putting the rules together, with a warning for each place where it had to trust something:
"""Excel to CSV that keeps what the sheet displays for identifiers, and says what it could not trust."""
import csv, datetime as dt, re, sys
from decimal import Decimal
from openpyxl import load_workbook
path, sheet, out = sys.argv[1:4]
wb_f = load_workbook(path)[sheet] # formulas as written
wb_v = load_workbook(path, data_only=True)[sheet] # the values Excel cached
warnings = []
def text(cell, cached):
v, fmt = cached.value, cell.number_format
if cell.data_type == "f":
warnings.append(f"{cell.coordinate}: formula {cell.value!r}, using the cached value {v!r}")
if v is None:
return ""
if isinstance(v, bool):
return "true" if v else "false"
if isinstance(v, dt.datetime):
return v.date().isoformat() if v.time() == dt.time() else v.isoformat()
if isinstance(v, dt.date):
return v.isoformat()
if isinstance(v, (int, float)):
if re.fullmatch(r"0+", fmt): # 00000: a zero-padded identifier
return str(int(v)).zfill(len(fmt))
if isinstance(v, float) and v.is_integer() and abs(v) < 2**53:
return str(int(v))
if abs(v) >= 2**53:
warnings.append(f"{cell.coordinate}: {v!r} has more digits than a double holds")
return repr(v)
return str(v)
with open(out, "w", newline="", encoding="utf-8") as f:
w = csv.writer(f, lineterminator="\n")
for row_f, row_v in zip(wb_f.iter_rows(), wb_v.iter_rows()):
w.writerow([text(a, b) for a, b in zip(row_f, row_v)])
for m in warnings:
print("warning:", m, file=sys.stderr)python lab/conv/xlsx2csv.py work/conv/zips.xlsx data work/conv/zips_out.csv 2>&1
echo ---
cat work/conv/zips_out.csvwarning: D2: 1.234567890123457e+18 has more digits than a double holds
warning: H2: formula '=B2+0*1+3', using the cached value 0
warning: D3: 1.234567890123457e+19 has more digits than a double holds
warning: H3: formula '=B3+0*1+4', using the cached value 0
warning: H4: formula '=B4+0*1+5', using the cached value 0
---
name,zip_text,zip_number,account,ratio,joined,amount,total,flag
Ana,02134,02134,1.234567890123457e+18,0.125,2025-03-01,1234.5,0,true
Bo,10001,10001,1.234567890123457e+19,0.3333333333333333,2025-03-02,0.3,0,false
Cy,00000,00000,99,0.5,2025-12-31,100,0,trueThe output file has 02134, 10001, 00000 in both zip columns, dates as 2025-03-01 (midnight dates lose the time), booleans as true/false and the percentage as its stored value 0.125. It printed five warnings, one for each of the two account numbers and one for each of the three formula cells. The warnings go to standard error, so they do not end up in the data. I would send them to the person who owns the workbook.
How to tell it worked
Open the workbook in the spreadsheet and the CSV in a text editor, and compare a few rows by eye, with your attention on the identifier columns. Compare the number of data rows with the sheet's row count (the last used row, not the highest row number). Read the CSV back with dtype=str and check that no value has lost its width. Search the sheet for cells that contain formulas, merged cells, hidden rows and columns, and multiple sheets, because a converter handles each of them in its own way. And keep the workbook next to the CSV with its checksum.
What a workbook made by Excel may add
Everything above came from a file made by a script, so it lacks things a real workbook has: a cached result for every formula, styles from a template, hidden sheets, defined names, text that is wrapped across merged cells, rich text with several formats in one cell and dates in either of two calendar systems. Those are the reasons to repeat the test on a real file and to keep the warning list. For a one-off conversion with a handful of identifier columns, set those columns to text in the source before saving as CSV. For a recurring conversion, treat the workbook as an input with a contract, and validate the CSV it produces. The reverse direction is in converting CSV to an Excel workbook.
