CSV to JSON with Python and pandas

A minimal script is import pandas as pd; df = pd.read_csv("in.csv", dtype=str); df.to_json("out.json", orient="records", indent=2). The records orientation writes an array of objects, one object per row.

The important part is dtype=str. Without it, pandas may infer integers, floats, booleans, and dates. That can remove leading zeros, change large IDs, or turn blank cells into missing values. Infer types only after deciding which columns should be typed.

Pass the delimiter explicitly when needed: pd.read_csv("in.csv", dtype=str, sep=";"). For tab-separated files, use sep=" ". Do not assume every .csv file uses commas.

Encoding should also be explicit when data comes from Excel or legacy systems. Use encoding="utf-8-sig" for UTF-8 CSV files with a BOM, or a known legacy encoding such as encoding="cp1252" when that is truly the source. Guessing can produce mojibake.

orient="records" is common for APIs: [ {"name":"Ana"}, {"name":"Bo"} ]. lines=True with records writes JSONL, one object per line, which is better for large exports and streaming.

Other orientations serve different consumers. split stores columns, index, and data arrays. table includes a schema-like wrapper. Do not pick an orientation only because the file opens; pick the one the destination expects.

CSV cannot distinguish every intent. An empty field might mean unknown, not applicable, or an empty string. Pandas may convert blanks to NaN unless configured. If blanks must remain empty strings, use options such as keep_default_na=False and test the output.

For money and decimals, text preservation may be safer during conversion. Convert to decimal types later in the system that owns validation. JSON numbers do not encode scale, and JavaScript consumers can lose precision with very large integers.

A flat CSV naturally becomes flat JSON records. If you need nested JSON, define grouping rules. For example, group line items by invoice number and build arrays manually rather than expecting to_json to infer hierarchy.

For the reverse path, nested JSON to CSV needs flattening. That is a different problem from CSV to JSON and requires choices about arrays, child records, and repeated fields.

For large files, consider chunked reading instead of loading the full CSV into memory. pd.read_csv("in.csv", dtype=str, chunksize=100000) lets you process and write batches, though writing one valid JSON array then needs comma management. JSONL is often simpler for chunked output.

Keep a schema note even if the conversion preserves text. Document which columns are identifiers, dates, money, booleans, and free text. That note helps the next step cast values deliberately instead of repeating pandas inference surprises downstream.

For repeatable jobs, put read and write options in code instead of relying on pandas defaults. Include sep, encoding, dtype, keep_default_na, and the JSON orientation. Defaults are convenient for exploration but risky for scheduled conversions.

Add a small fixture file to the project with leading zeros, accented names, blank cells, quoted delimiters, and one long number. Run the script against that fixture whenever options change. A fixture catches the quiet data changes that do not raise Python exceptions.

If the JSON feeds an API, validate one object against the API contract before converting the full file. A valid JSON file can still use field names or null handling the receiver rejects.

A final script should log input rows, output records, delimiter, encoding, and JSON orientation. Those facts make failed imports easier to reproduce without sharing the full dataset.