Skip to main content
dev-tools

CSV to JSON Without Losing Your Data Types

CSV carries no types. Every cell is a string until something decides otherwise, and that decision is where pipelines break. A converter that guesses wrong on one column corrupts the whole dataset silently: no error, no warning, just a ZIP code of 1234 that lost its leading zero in a report nobody re-checked.

What CSV actually promises

RFC 4180 defines the format loosely: comma-separated fields, optional double-quote wrapping, CRLF line endings. It says nothing about numbers, dates, booleans or null. So 01234, 1,234.56 and 31.12.2026 are all just text. Every type you see in the resulting JSON was invented downstream, by a converter, using a guess.

That is the mental model to keep: CSV to JSON is not a conversion, it is a series of decisions about inference. Write those decisions down or they will be made for you.

Six failures that show up in real exports

  • Leading zeros. ZIP codes, phone numbers, SKUs, invoice numbers. "01234" becomes 1234 the moment a converter calls it a number.
  • Integers above 2^53. JavaScript numbers lose precision past 9007199254740992. Order IDs and bank references regularly exceed that. In one warehouse export a 19-digit reference ended in ...000 for 40k rows and every lookup against it failed.
  • Ambiguous dates. 03/04/2026 is March 4th in the US and April 3rd in most of Europe. Neither side errors; both produce valid dates.
  • Locale decimal separators. German and Baltic exports write 1.234,56. Parsed with a dot-decimal parser you get 1.234, or a NaN that quietly becomes null.
  • Booleans with five spellings. true, TRUE, yes, y, 1. And no, which is falsy but not false unless someone mapped it.
  • Null versus empty versus the word NULL. An empty cell, a cell containing NULL, and a cell containing "" mean three different things to the person who exported the file. Pick one mapping and stick to it, or your averages will include zeroes that were never measured.

Quoting, delimiters and the BOM

Real files are messier than the spec. Three things break naive parsers more than anything else:

  • Embedded delimiters and newlines. A quoted field can contain commas and line breaks. Any parser that splits on commas or on \n before handling quotes will shred those rows. Never parse CSV with split(","); use a real reader.
  • Escaped quotes. Inside a quoted field a literal quote is doubled: "He said ""ok""". Half the hand-rolled regexes on the internet get this wrong on the last field of the line.
  • The UTF-8 BOM. Excel on Windows prepends \ufeff. Your first header key becomes \ufeffid, and every lookup of row["id"] returns undefined for the whole file. One strip() at the top fixes a bug that can cost an afternoon.

Semicolon-delimited files are the norm in much of Europe, and Excel picks the delimiter from the system locale, not from the file. Sniff the first line for the candidate with the highest consistent count across rows instead of trusting the extension.

Headers are the schema

Duplicate column names, trailing spaces, mixed case and blank header cells are common. Two columns named email collapse into one key and the second silently overwrites the first. Normalise headers before converting: trim, lower-case, replace spaces with underscores, and de-duplicate by appending an index. Do it deterministically so the same file always produces the same JSON keys, which matters if anything downstream keys off them.

If you only need a quick look at a small file, the CSV to JSON converter handles quoting, BOM and delimiter detection and shows the resulting array immediately. For a file that feeds a database, still run it through your own code once and diff the output.

Nested data does not fit in a table

The moment a CSV cell holds a|b|c or a JSON blob, you have a modelling decision, not a parsing one. Two workable options:

  1. Flatten. Join nested keys with dots: address.city, items.0.sku. Good for analytics, bad for round-tripping.
  2. Normalise. Split into a parent array and a child array linked by an id. Correct for APIs, more work up front.

Do not invent a third option halfway through the pipeline. Mixed shapes in one JSON array are the single most common cause of "works for 9 files out of 10".

Stream it, do not load it

A 400 MB CSV expanded into a JSON array can take 2 GB of RAM in Python or Node, because every string becomes an object with overhead. For anything over roughly 50 MB, write JSON Lines: one object per line, no wrapping array, no trailing comma. It streams, it diffs cleanly in git, and a truncated file loses one line instead of everything. Only assemble a real array at the very end, if the consumer demands it.

Validate before the data leaves your machine

A pipeline that produces valid-looking JSON can still be wrong. Run three checks, every time:

  1. Parse the output with a strict parser — the JSON validator catches trailing commas, unescaped control characters and duplicate keys that lenient parsers swallow.
  2. Compare row counts: lines in the CSV minus the header should equal objects in the JSON. A mismatch means a quoted newline was mis-split.
  3. Spot-check the columns that carry leading zeros, money and dates. Those are the three that break.

Then round-trip: convert the JSON back with JSON to CSV and diff against the original with a normalised sort. Fields that come back as 1234 instead of 01234 prove the type inference was wrong, and the diff tells you exactly which column.

When you need to eyeball structure before writing a mapper, formatting the JSON with sorted keys makes missing fields obvious in seconds.

A checklist that fits on a sticky note

  • Strip the BOM, detect the delimiter, use a real CSV reader.
  • Force text for anything with leading zeros, IDs and phone numbers.
  • Parse dates with an explicit format and an explicit timezone.
  • Map true/yes/1 and false/no/0 yourself; do not let the library guess.
  • Decide what empty and NULL mean, once, and document it.
  • Emit JSON Lines above 50 MB.
  • Validate, count rows, round-trip, diff.

None of this is exotic. It is the difference between a conversion that ran and a conversion that is correct.