Convert CSV to JSON (and Back)

You’ve got a CSV and something wants JSON: an API, a config file, a data import. Or the reverse: a JSON export and you need it as CSV so you can open it in a spreadsheet. It sounds trivial, and for clean data it is. For real-world data, a naive converter mangles it.

The Simple Case

A CSV with a header row maps directly to an array of objects. This:

• name,role,active
• Ada,Engineer,true
• Grace,Admiral,true

becomes:

• [{"name":"Ada","role":"Engineer","active":true},
•  {"name":"Grace","role":"Admiral","active":true}]

If your data is that tidy, a five-line script does it. In Python: import csv, json; json.dump(list(csv.DictReader(open('data.csv'))), open('out.json','w')). Most spreadsheet apps and code editors can do it too.

The Gotchas That Break Naive Converters

• Commas inside fields. "Smith, Jr." is one field, not two; a naive split on commas destroys the row. Proper CSV parsing respects quotes.
• Newlines inside fields. A quoted field can contain line breaks (addresses, notes). Splitting on line breaks shreds it.
• Types. Should active be the boolean true or the string "true"? Is 007 the number 7 or the string "007"? Getting this wrong silently corrupts data downstream.
• Inconsistent columns and stray blank lines, which break row alignment.
• Encoding & BOM: a UTF-8 byte-order mark can turn your first header into name and break the key.

Going the Other Way (JSON → CSV)

JSON to CSV has its own trap: nesting. A flat array of flat objects maps cleanly to rows and columns. But if your objects contain nested objects or arrays ("address": {"city": "..."}), there’s no single obvious CSV shape: you have to flatten (e.g. address.city as a column) or serialize the nested part. Decide that on purpose rather than letting a converter guess.

Everything in a CSV is a string

This is the source of most conversion bugs. CSV has no type system whatsoever: every field is text, and the converter has to guess what you meant. Those guesses go wrong in predictable, damaging ways:

• Leading zeros vanish. ZIP code 02134 and part number 0071 become 2134 and 71 the moment something decides they’re numbers. For identifiers, you want them to stay strings.
• Long digit strings lose precision. A 19-digit order ID parsed as a float silently rounds. It still looks like a number; it’s just the wrong one.
• Empty versus null. An empty CSV cell could mean "", null, or “unknown”, and the choice changes how downstream code behaves.
• Booleans and dates. TRUE, yes and 1 may or may not become true; 03/04/2026 is March 4th or April 3rd depending on where the file was made.

Encoding and delimiters, the quiet killers

Two file-level details break more conversions than any parsing logic. First, encoding: a CSV saved from Excel may carry a UTF-8 byte-order mark, so your first column name silently becomes \ufeffid instead of id, and every lookup against it fails while the file looks perfect. Non-UTF-8 exports mangle accented names outright.

Second, the delimiter isn’t always a comma. In locales where the decimal separator is a comma, Excel writes semicolon-separated files, still called .csv. Hand that to a comma-splitting parser and you get one giant column. And a comma inside a quoted field ("Smith, Jane") is why you should never split on commas yourself; use a real parser that understands quoting and escaped quotes.

Flattening nested JSON back to CSV

Going the other direction has one genuinely hard problem: CSV is flat and JSON nests. The workable convention is dot notation: {"user":{"name":"Ada"}} becomes a column called user.name. Arrays are the messy case, with three honest options: join them into one cell ("red|blue"), explode them into numbered columns (tags.0, tags.1), or emit one row per array item and repeat the parent fields. Each loses something. Pick deliberately based on what reads the file next, because there is no lossless answer here.

Where converters go wrong

The classic symptom is a name arriving split across two columns. That is a converter splitting on every comma instead of respecting quoted fields: "Smith, Jr." is one value, and only a real CSV parser knows it. Any tool that fails this test will fail on addresses, on currency, and on free-text notes, which is to say on most real data.

For a clean file with a header row, a short script using Python’s csv and json modules converts in seconds, and most editors have the conversion built in. Nesting is the one thing that does not round-trip: going from CSV you get a flat array of objects, and going the other way nested structures have to be flattened or serialised, because there is no lossless single-table form for them.

The short version

For clean data, a few lines of code convert CSV↔JSON for free; use them. When the data has quoted commas, embedded newlines, mixed types, or you just want it done right in one step, use the $1 CSV↔JSON converter, or see the full $1 tools catalog.