CSV to JSON: structures, types, and the gotchas that bite

Converting CSV to JSON looks like a syntax change and is actually a semantics change: CSV has rows of untyped text; JSON has structures of typed values. Every converter, including ours, makes decisions on your behalf in that gap — and the ones that decide badly produce JSON that parses fine and is quietly wrong. This guide is the map of those decisions.

The shape: array of objects, almost always

The natural mapping takes the header row as keys and each data row as one object:

[
  { "order_id": 1001, "customer": "Ada Lovelace", "amount": 42.5 },
  { "order_id": 1002, "customer": "Grace Hopper", "amount": 17.25 }
]

This is what config loaders, test fixtures and REST endpoints expect. Its one structural requirement is a sane header row: keys come from it verbatim, so Order ID (2024) becomes a key you’ll be quoting awkwardly in code forever, and duplicate header names collapse — later columns overwrite earlier ones in each object. Rename headers before converting (snake_case is the kindest to your future self), and if the CSV has no header at all, make sure the converter generates col_1-style keys rather than eating the first data row as names.

The alternative shape — NDJSON (one complete object per line, no wrapping array) — wins whenever the consumer is a pipeline: MongoDB’s import, Elasticsearch bulk loads, BigQuery, log tooling. It streams: a million-row NDJSON can be consumed line by line in constant memory, while a million-entry array must be parsed whole. Rule of thumb: read-at-once consumers get the array; databases and pipelines get NDJSON; past ~100k rows, default to NDJSON unless something specifically demands an array.

What no flat converter can honestly produce is nesting — {"address": {"city": …}}. A CSV row is flat; guessing nesting from dotted column names corrupts data in the cases where a column legitimately contains a dot. Convert flat, reshape in code.

The types: where good JSON goes bad

CSV’s 42 is two characters. Should the JSON say 42 or "42"? Both are defensible — which is why it must be a visible option (ours is the “typed values” toggle), not a silent behavior. Typed output converts:

• numeric-looking strings → JSON numbers (42, 9.99, -3, 1e5)
• true/false (any case) → booleans
• empty cells → null

…and the exceptions are the whole game:

Leading zeros. 02134 is a Boston ZIP code, and "zip": 2134 is corrupted data. Any value that only looks numeric because digits are involved — ZIPs, phone numbers, account codes — must stay a string. A converter that turns 007 into 7 has destroyed information it can’t get back; ours deliberately refuses.

Long digit strings. JSON numbers are, in practice, IEEE 754 doubles: 15–16 significant digits. A 16-digit order ID converted to a number rounds its last digit — 4111111111111111 becomes 4111111111111110 in the first JavaScript that touches it. Anything past 15 digits must remain a string. (This is the same rounding Excel inflicts on CSVs, wearing a different costume.)

Null vs empty string. Typed mode writes empty cells as null — the JSON idiom for “absent”, and what databases and APIs usually want. But null and "" are different values to a schema validator: if your consumer distinguishes “no middle name provided” from “middle name is empty”, CSV cannot carry that distinction, and you should know it was flattened before the file ever reached the converter.

Dates stay strings. JSON has no date type; the useful convention is ISO-8601 strings ("2024-03-31"). A converter shouldn’t guess at 03/04/2025 — normalize ambiguous date columns before converting (regex find & replace does it in one pass), so the strings you emit are the strings you mean.

The quiet gotchas

• Quoted CSV structure leaks in. A field with embedded commas, quotes or newlines is fine — if the converter parses CSV properly. Converters built on split(',') shred “Acme, Inc.” into two keys’ worth of garbage. Check one nasty row after converting.
• BOM in the first header. Files from Excel often start with an invisible byte-order mark; lazy converters emit a first key of "id". (Ours strips it.)
• Big arrays choke consumers. The converter may stream fine and then a downstream JSON.parse eats 2 GB of RAM. That’s the NDJSON decision again — make it at export time, not after the failure.
• Key collisions from renaming. If you rename columns for JSON-friendliness, two columns renamed to the same key silently merge. Rename, then eyeball the first object.

A five-minute recipe that gets it right

1. Open the CSV and fix the headers — short, unique, snake_case.
2. Profile the columns (stats panel): confirm which are genuinely numeric, and spot the “numeric except three cells” columns that will convert unevenly.
3. Normalize date columns to ISO with find & replace.
4. Choose array vs NDJSON by consumer; choose typed vs strings by whether numbers will be computed on.
5. Convert, then read the first and last object and one tricky row (ZIP, long ID, quoted text). Thirty seconds of looking catches ninety percent of conversion bugs.

The theme across all of it: CSV-to-JSON is a typing ceremony for data that never had types. Do the ceremony deliberately — with the exceptions for zeros, long IDs and dates enforced — and the JSON you emit means exactly what the CSV did. Do it by default settings and you’re trusting a stranger’s guesses about your data. That’s the entire difference between converters, including this one’s reason for existing.