Keep leading zeros and long IDs safe in a Python CSV workflow

Read identifiers as text, convert only quantities, and verify the file you write.

Original synthetic tutorial, checked with pandas 2.2.3. The accompanying demonstration passes 10 automated tests. No real customer data is used, and no Excel application was run.

1. Decide which fields are identifiers

A customer code such as 000123 names a record. Its leading zeros may matter. A quantity such as 2 is meant for arithmetic. Treating both as numbers confuses their meanings.

Our four-row example contains three identifier fields: customer_id, tracking_id, and region_code. One region is the literal string NA; another is empty. This example keeps them distinct. Keep an untouched source copy before adapting the method to your own data.

2. Reproduce the two different risks

In the verified run, a plain pd.read_csv call inferred customer IDs as [123, 4207, 0, 18], dropping their leading zeros. It also interpreted the literal NA and the empty region as missing values. A default can be convenient for analysis and still be wrong for your field contract.

A separate, deliberately unsafe conversion demonstrates floating-point precision loss:

original = "9007199254740993"
changed = str(int(float(original)))
print(changed)             # 9007199254740992
assert original != changed

This does not mean every pandas CSV import rounds long integers. Python integers can represent large integers exactly, but converting an identifier to an integer still drops leading zeros. Passing through a float adds another risk. Converting a damaged value back to text cannot restore its original characters.

3. Run the complete small example

Use a fresh working folder with Python and pandas available. Save the following as preserve_ids.py, then run python preserve_ids.py. It creates synthetic-id-demo-output.csv in the current folder; use an empty folder so you do not overwrite an existing file. No download, account, or network request is required by this script.

The input is included in the code so you can reproduce all four rows without downloading a dataset.

from pathlib import Path
from io import StringIO
import pandas as pd

sample = ("customer_id,tracking_id,region_code,quantity\n"
          "000123,123456789012345678,NA,2\n"
          "004207,9007199254740993,EU,5\n"
          "000000,999999999999999999,US,1\n"
          "000018,100000000000000001,,3\n")
df = pd.read_csv(StringIO(sample), dtype=str,
                 keep_default_na=False)
ids = ["customer_id", "tracking_id", "region_code"]
expected = df[ids].copy(deep=True)
assert df.customer_id.str.fullmatch(r"[0-9]{6}").all()
df["quantity"] = pd.to_numeric(df["quantity"], errors="raise")
assert df.quantity.sum() == 11
df.to_csv("synthetic-id-demo-output.csv", index=False)
back = pd.read_csv("synthetic-id-demo-output.csv", dtype=str,
                   keep_default_na=False)
pd.testing.assert_frame_equal(back[ids], expected)
print("PASS: all identifier fields preserved")

Expected terminal output from the tested code:

PASS: all identifier fields preserved

4. Understand the safeguards

dtype=str establishes text handling at the read boundary, before numeric conversion can discard characters.
keep_default_na=False, with no custom missing-value list here, keeps NA and the empty field as strings. This is our chosen policy, not a universal missing-data rule.
[0-9]{6} checks exactly six ASCII digits across the whole customer ID. Six digits is only this synthetic example's contract. It rejects 123, 12A456, and 00123. Do not pad, strip, or rewrite a real ID without an agreed rule.
Only quantity is explicitly converted with errors set to raise. Its verified total is 11. A real workflow must also define allowed ranges, negative values, and fractions.
index=False prevents an extra index column. The second read uses the same text policy, and the exact frame comparison checks every identifier, selected columns, and row order.

The pandas options are documented in the official read_csv reference. This code uses dtype=str; it does not depend on whatever type inference a newer pandas release chooses by default. Test again when changing versions or data rules.

5. Cross-check with the standard CSV reader

Python's standard CSV reader keeps ordinary field values as strings unless you explicitly request a numeric conversion mode. Add this after the preceding script to independently compare the identifier characters against the embedded input:

import csv

original_rows = list(csv.DictReader(StringIO(sample)))
with open("synthetic-id-demo-output.csv",
          newline="", encoding="utf-8") as handle:
    written_rows = list(csv.DictReader(handle))

assert [
    {key: row[key] for key in ids} for row in written_rows
] == [
    {key: row[key] for key in ids} for row in original_rows
]
print("PASS: standard-library cross-check")

See Python's CSV documentation for string handling and the recommended newline setting. This is a decoded-field comparison, not a claim that the exported file is byte-identical to its input.

6. Test the Excel handoff separately

A CSV can contain correct identifier characters while Excel interprets them as numbers. Numeric Excel values have a 15-significant-digit precision limit. Quoting a CSV field does not universally force spreadsheet readers to treat it as text.

Use an import workflow that sets identifier columns to Text before automatic conversion, then verify the imported values. Menus and conversion settings vary by Excel version. For an Excel-specific deliverable, a workbook that stores ID cells as text may be more suitable, but it needs its own end-to-end test. A display format cannot reconstruct digits already discarded.

Microsoft's guidance on leading zeros and large numbers explains this boundary. Python round-trip success is not proof of correct Excel import. If the source is already damaged, return to a trustworthy original.

What was actually verified?

The full synthetic demonstration checked all 12 identifier fields across four rows, matched them with the standard CSV reader, and confirmed its input file hash stayed unchanged. Its 10 tests cover leading zeros, long identifiers, literal NA, empty strings, malformed codes, inference and float negative controls, and output structure. The complete on-page code plus cross-check was also executed successfully.

These results cover this sample and code path, not arbitrary files, large-data performance, every pandas version, or Excel behavior. If a real pipeline intentionally sorts or filters rows, define and test that transformation explicitly.

Explore the CSV cleanup example or start with a general, non-sensitive question.