Python Doc & Data Automation

Fix CSV Leading Zeros Lost in Excel

The export looks perfect in Python and in a text editor:

customer_id,postcode,phone,ean
00417,01234,07700900123,0012345678905

Double-clicked in Excel it becomes 417, 1234, 7.70009E+11 and 12345678905. The recipient sends the file back with the damaged values, a lookup against the master data matches nothing, and someone proposes storing the codes without zeros "since Excel drops them anyway".

Root Cause

A CSV has no types. Every field is text, and the program that opens it decides what each value means. Excel's default behaviour when opening a .csv by double-click is to run each column through its general-format parser: a value consisting only of digits becomes a number, and numbers have no leading zeros, so 00417 becomes 417. Long digit strings such as phone numbers and EAN codes become floating-point numbers and lose precision or switch to scientific notation beyond 15 significant digits. Nothing in the file can override this — the format has no place to say "this column is text". Writing the file differently does not help either: quoting does not make Excel treat a value as text on open, and neither does padding with spaces. The fix must either change the file format, or change what the value looks like so Excel's parser refuses to convert it.

Minimal Diagnostic

Find which columns are at risk before exporting: values that are all digits and would change if parsed as numbers.

# pip install "pandas>=2.2"
from pathlib import Path
import pandas as pd

SOURCE = Path("out/customers.csv")

def at_risk(frame: pd.DataFrame) -> pd.DataFrame:
    rows = []
    for col in frame.columns:
        values = frame[col].dropna().astype(str).str.strip()
        if values.empty:
            continue
        digits = values.str.fullmatch(r"\d+")
        leading_zero = (digits & values.str.startswith("0")).sum()
        long_numbers = (digits & (values.str.len() > 15)).sum()
        rows.append({
            "column": col,
            "all_digit_values": int(digits.sum()),
            "would_lose_zeros": int(leading_zero),
            "would_lose_precision": int(long_numbers),
            "example": values[digits & values.str.startswith("0")].iloc[0] if leading_zero else "",
        })
    return pd.DataFrame(rows).query("would_lose_zeros > 0 or would_lose_precision > 0")

if __name__ == "__main__":
    frame = pd.read_csv(SOURCE, dtype="string")
    print(at_risk(frame).to_string(index=False))

    column  all_digit_values  would_lose_zeros  would_lose_precision example
customer_id              4820              4820                     0   00417
   postcode              1204              1204                     0   01234
      phone              4790              4790                     0 07700900123
        ean              4820                 0                  4820

Four columns are damaged the moment the file is opened. Reading with dtype="string" is essential for this check — reading with defaults would already have destroyed the evidence.

Ways to preserve leading zeros Writing an xlsx file with a text number format keeps zeros on double-click and is read correctly by other tools. A plain CSV loses zeros on double-click although the file itself is correct. The equals sign and quotes trick keeps zeros in Excel but corrupts the value for every other reader. Importing the CSV through Excel's text import wizard with the column set to text keeps zeros but requires the recipient to follow instructions. Method Zeros on double-click Other tools read it xlsx with text format kept yes Plain CSV lost yes "00417" in CSV kept corrupted CSV + import wizard kept yes

Fix: Send an .xlsx with the Column Formatted as Text

When the recipient opens the file in Excel, give them a file that carries types. An .xlsx with the column formatted as text keeps 00417 as text, and every other tool still reads it correctly. Changed lines carry comments.

# pip install "pandas>=2.2" xlsxwriter
from pathlib import Path
import pandas as pd

TEXT_COLUMNS = ["customer_id", "postcode", "phone", "ean"]

def export_excel_text_safe(frame: pd.DataFrame, dest: Path, text_cols: list[str]) -> Path:
    out = frame.copy()
    for col in text_cols:
        out[col] = out[col].astype("string").str.strip()          # changed: keep as text in pandas too
    dest.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        out.to_excel(writer, sheet_name="Data", index=False)
        book, ws = writer.book, writer.sheets["Data"]
        text_fmt = book.add_format({"num_format": "@"})           # changed: Excel's text format
        for col in text_cols:
            idx = out.columns.get_loc(col)
            width = max(len(col), int(out[col].str.len().max() or 0)) + 2
            ws.set_column(idx, idx, width, text_fmt)              # changed: whole column as text
        ws.freeze_panes(1, 0)
    return dest

if __name__ == "__main__":
    customers = pd.read_csv("out/customers.csv", dtype="string")
    print(export_excel_text_safe(customers, Path("out/customers.xlsx"), TEXT_COLUMNS))

The @ number format tells Excel the column contains text, so values keep their zeros when the file is opened, when a user edits a cell, and when the file is saved again. Reading the data back with pd.read_excel(..., dtype={"customer_id": "string"}) returns the original strings. This is the only option that survives a round trip through a recipient who opens, edits and returns the file — which is what usually happens.

Round trip through a recipient The job exports data containing customer id 00417. With a CSV, Excel converts it to the number 417 on open, the recipient edits other cells and saves, and the returned file contains 417, which no longer matches the master data. With an xlsx whose column carries the text format, the value stays 00417 through opening, editing and saving, and the returned file still matches. Export 00417 as text Recipient opens Excel parses Edits and saves other columns Returns file lookup against master Match only if still 00417 CSV path 00417 becomes 417

Variant Fix 1: CSV Is Required — Make the Values Non-Numeric

Some recipients and systems require CSV. Then the value has to stop looking like a number. The formula-style trick keeps zeros visible in Excel:

# pip install "pandas>=2.2"
import csv
from pathlib import Path
import pandas as pd

def export_csv_excel_text(frame: pd.DataFrame, dest: Path, text_cols: list[str]) -> Path:
    out = frame.copy()
    for col in text_cols:
        values = out[col].astype("string").str.strip()
        out[col] = values.map(lambda v: f'="{v}"' if pd.notna(v) else v)   # Excel reads it as a formula
    dest.parent.mkdir(parents=True, exist_ok=True)
    out.to_csv(dest, index=False, quoting=csv.QUOTE_ALL, encoding="utf-8-sig")
    return dest

This is a trade, not a fix: the file now contains ="00417", which pandas, databases and other systems read literally. Use it only for files a human will open in Excel and nothing else will parse, and strip the wrapper when reading such files back:

# pip install "pandas>=2.2"
import re
import pandas as pd

EXCEL_TEXT = re.compile(r'^="(.*)"$')

def unwrap_excel_text(frame: pd.DataFrame) -> pd.DataFrame:
    out = frame.copy()
    for col in out.select_dtypes(include=["object", "string"]).columns:
        out[col] = out[col].astype("string").str.replace(EXCEL_TEXT, r"\1", regex=True)
    return out

Variant Fix 2: Keep the CSV Clean and Tell Excel How to Open It

The cleanest CSV option leaves the data untouched and moves the type decision to the import. Excel's "From Text/CSV" import (Data ribbon) lets the recipient set columns to text, and Power Query remembers it. A short instruction sheet shipped with the file — or a .txt extension, which forces the import wizard instead of a direct open — avoids the double-click path entirely:

# pip install "pandas>=2.2"
import csv
from pathlib import Path
import pandas as pd

def export_csv_for_import(frame: pd.DataFrame, dest: Path, text_cols: list[str]) -> tuple[Path, Path]:
    dest.parent.mkdir(parents=True, exist_ok=True)
    frame.to_csv(dest, index=False, quoting=csv.QUOTE_MINIMAL, encoding="utf-8-sig", lineterminator="\r\n")
    readme = dest.with_name(dest.stem + "-open-in-excel.txt")
    readme.write_text(
        "Do not double-click this file.\n\n"
        "In Excel: Data > From Text/CSV > select the file > Transform Data,\n"
        f"set these columns to Text: {', '.join(text_cols)} > Close & Load.\n\n"
        "Double-clicking converts codes such as 00417 to 417.\n",
        encoding="utf-8")
    return dest, readme

For internal pipelines, the better answer is that nothing should double-click the file at all: machine-to-machine transfers read the CSV with explicit dtypes, as in fix pandas DtypeWarning mixed types, and the zeros never come under threat.

Choosing an export format The root asks who opens the file. Excel users who may edit and return it get an xlsx with the column formatted as text. Excel users who only read it can get a CSV plus import instructions or a txt extension. Files that are opened in Excel and never parsed by other software can use the equals-quote trick. Files consumed by other systems keep a plain CSV, with the reading side using explicit dtypes. Who opens this file? and does it come back? Excel, edits, returns xlsx text format survives round trip Excel, read only CSV + instructions or .txt extension Excel only, never parsed ="00417" trick corrupt for other tools another system Plain CSV reader sets dtypes

Recovering Values Already Damaged

Files returned with damaged codes can often be repaired when the original width is known and the codes are zero-padded to a fixed length:

# pip install "pandas>=2.2"
import pandas as pd

WIDTHS = {"customer_id": 5, "postcode": 5, "phone": 11, "ean": 13}

def repad(frame: pd.DataFrame, widths: dict[str, int]) -> tuple[pd.DataFrame, pd.DataFrame]:
    out = frame.copy()
    problems = []
    for col, width in widths.items():
        if col not in out.columns:
            continue
        values = out[col].astype("string").str.strip()
        numeric = values.str.fullmatch(r"\d+")
        too_long = numeric & (values.str.len() > width)
        scientific = values.str.contains(r"[eE]\+?\d+", na=False)      # 7.70009E+11: digits already lost
        out.loc[numeric & ~too_long, col] = values[numeric & ~too_long].str.zfill(width)
        for mask, reason in ((too_long, "longer than expected width"), (scientific, "scientific notation")):
            if mask.any():
                problems.append(pd.DataFrame({"column": col, "value": values[mask], "reason": reason}))
    return out, (pd.concat(problems, ignore_index=True) if problems else pd.DataFrame(columns=["column", "value", "reason"]))

Zero-padding recovers 417 to 00417 only when every code is exactly five digits. Values that reached scientific notation have lost digits permanently — the returned file cannot be repaired, and the exchange has to be repeated with a format that preserves them. That is the argument for the .xlsx export: repair is not always possible.

Verification

Assert that exported values round-trip unchanged by reading the exported file back the way the recipient's tools will.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd

def verify_export(original: pd.DataFrame, path: Path, text_cols: list[str]) -> None:
    if path.suffix.lower() in {".xlsx", ".xlsm"}:
        back = pd.read_excel(path, dtype={c: "string" for c in text_cols})
    else:
        back = pd.read_csv(path, dtype={c: "string" for c in text_cols}, encoding="utf-8-sig")
    for col in text_cols:
        expected = original[col].astype("string").str.strip().fillna("")
        actual = back[col].astype("string").str.strip().fillna("")
        changed = expected.compare(actual)
        assert changed.empty, f"{col}: {len(changed)} value(s) changed, e.g. {changed.head(1).to_dict()}"
        zeros = expected.str.startswith("0").sum()
        assert (actual.str.startswith("0").sum()) == zeros, f"{col}: leading zeros lost"
    print(f"{path.name}: {len(text_cols)} text column(s) round-trip unchanged")

For the .xlsx path, also open the file once in Excel and check a padded code visually before promising a supplier that their codes will survive — the automated check proves the file is right, not that the recipient's Excel settings are.

FAQ

Does quoting the value in the CSV help? No. Excel ignores quotes when deciding a column's type on open; quotes only protect delimiters and line breaks inside fields.

What about prefixing values with an apostrophe?'00417 displays as 00417 in Excel and keeps the zeros, but the apostrophe is part of the value for every other reader — the same trade as the ="..." trick.

Why do some recipients see the problem and others not? Regional settings and previously used import settings differ. Power Query remembers per-file settings, so a colleague who once imported the file correctly keeps getting correct results.

Can I stop Excel converting long numbers to scientific notation? Only by making the column text — the same fix. Excel switches to scientific notation above 11 digits in a default-width column and loses precision beyond 15 significant digits, which is why EAN and IBAN columns must never travel as plain CSV numbers.

Is there a CSV convention for types? Not in the format itself. Schema sidecars such as CSV on the Web or Frictionless Data describe types, but Excel does not read them.

Part of Exporting Data to CSV Formats.