Keep Leading Zeros in CSV: Excel and JSON Fixes

1. Check whether the original digits still exist

Open a copy of the CSV in a plain-text editor before opening it in a spreadsheet. Find a known record. If the file contains 00123 but the spreadsheet shows 123, keep that source copy: you can reimport it with the correct column type.

If the file itself contains 123, conversion will preserve 123. Do not guess how many zeros to add. Recover the identifier from an authoritative export or record. Likewise, compare long IDs digit by digit; scientific notation on screen alone does not tell you whether the stored value is still intact. A display format that adds zeros is not proof that the original identifier survived.

2. Import identifier columns as Text in Excel

Use Data → From Text/CSV and choose Transform Data. Before loading, set postal-code and ID columns to Text; if prompted, replace the existing type conversion. Confirm the preview against your untouched source. Menu names vary by Excel version.

Excel numbers retain only 15 significant digits. Formatting an already-converted number as Text does not restore lost digits, so use the original CSV and repeat the import. Some versions also offer automatic data-conversion settings; they do not repair existing values. See Microsoft’s guidance on leading zeros and large numbers.

3. CSV quotes do not determine JSON types

A CSV field such as "00123" can still be interpreted as a number by a converter. CSV quotation handles field boundaries; it is not a promise to preserve the field as a JSON string.

In JSON, "00123" is a string, while the unquoted token 00123 is not a valid number. A long identifier is also best kept as a string when exact digits matter across systems. JSON implementations can differ in numeric precision. See RFC 8259, section 6: Numbers.

4. Convert the sample without changing identifiers

Open the CSV to JSON converter and use this fictional sample. The smaller ZIP-code example can also be loaded directly.

1. Set Delimiter to Comma and leave First row is header checked.
2. Uncheck Infer numbers and booleans. Leave Empty cells → null unchecked.
3. Choose Objects for Output shape and 2 spaces for Indentation.
4. Paste the CSV into Input. Review the result, then choose Copy or Download.

postal_code,customer_id,quantity,active
00123,000742,2,true
90210,9007199254740993,3,false

[
  {
    "postal_code": "00123",
    "customer_id": "000742",
    "quantity": "2",
    "active": "true"
  },
  {
    "postal_code": "90210",
    "customer_id": "9007199254740993",
    "quantity": "3",
    "active": "false"
  }
]

Disabling inference applies to the whole table: quantity and active also become strings. This tool has no per-column type setting. Empty cells become empty strings with the selected settings; enabling Empty cells → null changes them to null.

The converter protects integers outside JavaScript’s safe integer range even with inference enabled, but that does not protect ZIP codes: 00123 can still become 123. Disable inference for identifiers and exact decimal text.

5. Keep IDs as strings, but make quantity an integer

If an import requires mixed types, save the fictional CSV above as sample.csv and run this Python 3 script from the same folder. It reads the file without changing it and prints JSON to the terminal. Only quantity becomes an integer; postal_code, customer_id, and active remain strings.

Python’s csv.DictReader maps fields to headers and normally returns strings. The script checks the expected headers, rejects rows with missing or extra fields, and limits quantities to safe integers for common JSON consumers. It does not infer Boolean flags or decimal amounts. See the Python csv documentation.

import csv
import json
import re
import sys

expected = {"postal_code", "customer_id", "quantity", "active"}
records = []

# 只读取原文件；utf-8-sig 接受带 BOM 的 UTF-8，newline="" 交给 CSV 模块处理换行。
with open("sample.csv", encoding="utf-8-sig", newline="") as source:
    reader = csv.DictReader(source, strict=True)
    headers = reader.fieldnames
    # 拒绝缺列、多列和重复表头，防止 DictReader 静默覆盖同名字段。
    if headers is None or len(headers) != 4 or set(headers) != expected:
        raise ValueError("Expected exactly: postal_code, customer_id, quantity, active")
    for row in reader:
        # 多余字段位于 None 键下；缺少的字段值为 None，二者都不能继续转换。
        if None in row or any(value is None for value in row.values()):
            raise ValueError(f"Wrong field count near CSV line {reader.line_num}")
        if re.fullmatch(r"[+-]?[0-9]+", row["quantity"]) is None:
            raise ValueError(f"Quantity must be an integer near CSV line {reader.line_num}")
        quantity = int(row["quantity"])
        # 限制到常见 JSON 消费端可精确表示的安全整数范围；ID 始终保留原字符串。
        if abs(quantity) > 9007199254740991:
            raise ValueError(f"Quantity exceeds the safe integer range near CSV line {reader.line_num}")
        row["quantity"] = quantity
        records.append(row)

# 全部校验成功后才输出到终端，不覆盖或创建任何用户数据文件。
json.dump(records, sys.stdout, ensure_ascii=False, indent=2, allow_nan=False)
sys.stdout.write("\n")

For the first record, verify "postal_code": "00123", "customer_id": "000742", "quantity": 2, and "active": "true". Check the second record’s long ID remains "9007199254740993".

6. Check before importing the result

• Compare record counts and a few known identifiers with the untouched source.
• Confirm identifier lengths and every digit, including zeros.
• Check whether the receiving system expects strings, numbers, Boolean values, empty strings, or nulls.
• Import a small sample first and inspect it in the destination before processing the rest.

The browser converter processes input and output locally and does not include that text in analytics. Website visits still generate analytics requests. Read the privacy policy for details.