CSV to JSON in Python: Preserve Leading Zeros and Empty Fields

Keep leading-zero IDs as JSON strings

In the sample, id identifies a product and quantity is a count. They can both contain digits while requiring different JSON types. Converting 00123 to the number 123 would remove part of the identifier. Preserving quantity as the string 4 is reversible, although a downstream numeric calculation may require an explicit conversion.

Start with string-preserving output when the schema is unknown. Then ask the receiving system which columns must be numbers, booleans or null. Do not infer that a field named date has one universal date format, or that the literal text null should automatically become a missing value.

Related help: Check whether an earlier Excel import already removed zeros

Check strings, empty fields, and missing keys

The first output object should have three properties, with id equal to the five-character string 00123. The second object also has all three properties; its empty quantity and note are empty strings, not omitted properties.

That distinction matters to APIs and importers. A missing property, an empty string and JSON null can trigger different behavior. If your destination requires a property to be absent instead, implement that as a separate documented transformation rather than assuming an empty CSV field tells you to remove it.

[
  {"id":"00123","quantity":"4","note":"Blue mug"},
  {"id":"00456","quantity":"","note":""}
]

Run the Python converter without pandas

Save the sample as input.csv and the complete code below as csv_to_json.py. From that folder, run python csv_to_json.py input.csv output.json for string-preserving output. Use a new destination name for each run; existing files are not overwritten.

The script uses Python 3 and its standard library. The source CSV is expected to be comma-separated UTF-8, with an optional byte-order mark. Output JSON is UTF-8. Neither file is uploaded, and no account or application integration is required.

import csv, json, re, sys

if len(sys.argv) not in (3, 4):
    raise SystemExit("Usage: python csv_to_json.py input.csv output.json [--quantity-as-number]")
typed = len(sys.argv) == 4
if typed and sys.argv[3] != "--quantity-as-number":
    raise SystemExit("Unknown option")
with open(sys.argv[1], encoding="utf-8-sig", newline="") as f:
    reader = csv.reader(f, strict=True)
    header = next(reader, None)
    if not header or any(not h.strip() for h in header):
        raise ValueError("Nonempty headers required")
    if len(header) != len(set(header)):
        raise ValueError("Duplicate headers would overwrite JSON properties")
    if typed and "quantity" not in header:
        raise ValueError("Typed mode requires a quantity column")
    result = []
    for number, row in enumerate(reader, 2):
        if len(row) != len(header):
            raise ValueError(f"Record {number}: wrong number of fields")
        item = dict(zip(header, row))
        if typed:
            value = item["quantity"]
            if value == "":
                item["quantity"] = None
            elif re.fullmatch(r"0|[1-9][0-9]*", value):
                item["quantity"] = int(value)
            else:
                raise ValueError(f"Record {number}: invalid quantity")
        result.append(item)
with open(sys.argv[2], "x", encoding="utf-8") as f:
    json.dump(result, f, ensure_ascii=False, indent=2, allow_nan=False)
    f.write("\n")
print(f"Wrote {len(result)} objects")

Convert blank quantities to null with an explicit rule

Run python csv_to_json.py input.csv typed.json --quantity-as-number to apply the example schema. The quantity column must exist. Its accepted nonempty values are plain nonnegative integer strings such as 0 or 24. Spaces, decimal fractions, signs, leading-zero quantities and text labels are rejected for review.

This deliberately narrow rule prevents a converter from silently deciding what 1,000, 04 or unavailable means. If your quantities can be fractional or negative, specify that requirement and update the parser and tests. Do not reuse an identifier-normalization rule for amounts.

Reject ambiguous headers and malformed tables

The heading row becomes the set of JSON property names. Two identical headers would compete for the same property, so the script rejects them. Blank headings and records with the wrong number of fields are rejected as well. Fix the schema or source record before trying again.

Header spelling, case and surrounding whitespace are preserved. The script does not silently make Customer ID equivalent to customer_id. Agree any renaming with the destination, and ensure two different source columns do not map to one property accidentally.

Related help: Locate CSV records with the wrong number of fields

Validate the receiving system separately

The generated document is serialized with nonstandard numeric output disabled. In the current typed mode only integers are created, but that guard remains useful if the schema later adds numeric fields. The script keeps the resulting objects in memory, so it is a modest-file example rather than a streaming data pipeline.

Python can represent integers larger than many receiving systems can preserve exactly. A common interoperable exact-integer range ends at 9007199254740991 (2^53 - 1). Confirm the destination limit before enabling numeric quantities; keep larger exact values as strings or apply its documented numeric representation.

The examples and failure cases were run in Python. That verifies this transformation, not acceptance by an arbitrary API. Compare the output against the receiving schema before importing it. Column Harbor does not convert CSV to JSON or submit records to another service.