JSON to CSV

Make a CSV that does not lose fields

JSON and CSV disagree about shape, and the disagreement is where most converters fail quietly. Papaparse — the library behind most JSON-to-CSV pages in JavaScript, this one included — takes its header row from the first object only. Measured this session: Papa.unparse([{a:1}, {b:2}, {a:3,c:4}]) produces a\r\n1\r\n\r\n3 — columns b and c are gone, with no header, no warning, and no error. That is the ordinary shape of an API dump where records don't all share every field. This page computes the column union across the entire array before writing a single cell, so a field that first appears on row 4,000 still gets a column, filled empty for every row that lacks it — and the full column list renders above the output so a field you expected is easy to confirm.

Nested JSON has no native CSV form, and papaparse doesn't invent a good one on its own — handed an object value directly, it writes the literal string [object Object] into the cell. This page flattens first: object keys become dotted columns and array indices become bracketed ones, down to 8 levels, using the same user.address.city / tags[0] path syntax jq uses, which matters because the FAQ below points people at jq for the same job.

CSV has no type system, and this page doesn't pretend otherwise: null and an empty string collapse into the same empty cell, and a boolean or number reads back as plain text on the other end — that loss is the format's, not this converter's, and the notes strip under the output says so rather than hiding it. The one thing this page does add on top of a plain conversion is formula defence: a cell beginning with =, +, -, or @ is what Excel, LibreOffice, and Google Sheets treat as a formula the instant the file opens — OWASP documents this as CSV Injection — and quoting alone does not stop it. This page prefixes a formula-shaped cell with a leading apostrophe only when the value is not a finite JSON number, so -5 stays a plain negative number and =1+1 gets defused, and it says how many cells it touched.

Do it without this tool

Same conversion, your own tools. The jq one-liner is the version to read carefully — the naive form only works when every row has the same keys.

jq -r '(map(keys) | add | unique) as $cols |
  ($cols), (.[] | [.[$cols[]]]) | @csv' data.json > data.csv

# map(keys)|add|unique builds the column union across every row first.
# jq -r '.[0] | keys_unsorted, (.[] | [.[keys_unsorted[]]]) | @csv'
# is the one-liner most people reach for, and it silently drops any
# column that isn't in the FIRST row — the exact bug this page fixes.

import csv, json

rows = json.load(open("data.json"))
fields = list(dict.fromkeys(k for row in rows for k in row))  # union, first-seen order

with open("data.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=fields, extrasaction="ignore")
    writer.writeheader()
    writer.writerows(rows)

# encoding="utf-8-sig" writes a BOM, which is what makes a double-clicked
# CSV with non-ASCII text open correctly in Excel on Windows.

import Papa from 'papaparse';

const columns = [...new Set(rows.flatMap((row) => Object.keys(row)))];
const csv = Papa.unparse(rows, { columns });
// Passing `columns` explicitly is what fixes the first-row-only bug —
// Papa.unparse(rows) with no options takes headers from rows[0] alone.

mlr --ijson --ocsv --allow-ragged-csv-output cat data.json > data.csv
# --allow-ragged-csv-output is what stops Miller erroring on the same
# heterogeneous-row case this page's column union handles.

What the format is actually specified to do

RFC 4180 §2 defines CSV's record and field structure — CRLF line endings, comma delimiters, and the doubled-quote escaping this page's output follows exactly, which is table stakes and not a differentiator; papaparse already gets it right and this page doesn't reimplement it. The format has no type system at all: RFC 4180 says nothing about how a reader should interpret a cell's text, which is the root cause of both the null/empty-string collapse above and the formula-injection problem, since a spreadsheet application's choice to treat =1+1 as executable is an application convention, not something CSV itself specifies one way or the other. OWASP's CSV Injection page documents that convention and the apostrophe-prefix mitigation across the major spreadsheet applications; it is cited here rather than re-measured, since verifying spreadsheet behaviour firsthand wasn't done this session — what was measured is that papaparse emits these cells unescaped, and that the apostrophe prefix survives a round trip back through the parser.

FAQ

Why is a column missing from another converter's output?

Because most CSV writers, including papaparse — the library this page and most others in JavaScript are built on — take the header row from the first object in the array only. Measured: Papa.unparse([{a:1}, {b:2}, {a:3,c:4}]) produces a\r\n1\r\n\r\n3 — columns b and c are simply absent, no header, no warning, no error. That is the normal shape of an API export where later records have fields earlier ones didn't. This page computes the column union across every row before writing anything, so a field that only shows up on row 4,000 still gets its own column, filled in as empty for every row that doesn't have it. The full column list renders above the output specifically so a field you expected is easy to confirm rather than something you have to notice is missing.

Why did Excel show 2 where my data said "=1+1"?

Because a CSV cell whose text starts with =, +, -, or @ is a formula to Excel, LibreOffice, and Google Sheets the moment the file is opened — the spreadsheet application evaluates it; the CSV format itself has no way to mark a cell as “this is text, not a formula.” This is documented by OWASP as CSV Injection, and it is a real vector, not a cosmetic glitch: a malicious CSV can run arbitrary formulas, including ones that call out to external servers in some spreadsheet configurations. This page prefixes any cell that starts with one of those four characters and is not a finite JSON number with a leading apostrophe, which every major spreadsheet application treats as “force this cell to text” — the value round-trips back to the original string on a re-import.

Why did an apostrophe appear at the front of a cell?

That's the formula defence from the question above, and it's specific to this cell's content, not a general escaping rule. Measured: a naive fix — quoting the cell — does not work, because "=1+1" opened in Excel is still evaluated as a formula even inside quotes; only the leading apostrophe reliably forces text interpretation. The rule isn't simply “prefix anything starting with those four characters,” either, because that would also catch -5 and +44 20 7946 0000. This page's rule prefixes a cell only when it starts with =, +, -, or @ and is not a finite JSON number — so a negative number stays a plain -5, a formula-shaped string gets the apostrophe, and a phone number starting with + gets it too, because a leading + on a non-numeric string really is evaluated as a formula by Excel.

Why do non-English characters look garbled when I open the download in Excel?

This page does not add a UTF-8 byte-order mark (BOM) to the file it downloads, and Excel on Windows falls back to guessing the encoding — usually wrongly — for a CSV that has none. The bytes are correct UTF-8; Excel is misreading them. The fix on Excel's side is to import rather than double-click open: Data → From Text/CSV, and choose UTF-8 explicitly, rather than letting Excel's file-association default handle it. If you'd rather the file carried a BOM so a plain double-click works, either prepend the three bytes EF BB BF yourself, or use Python's csv module with encoding="utf-8-sig" — the second snippet above — which writes one for you.

How do nested objects and arrays become columns?

Object keys join with a dot and array elements get an index in brackets, down to 8 levels deep — the same path syntax jq uses for --arg-style filters, which is deliberate, since the snippet above points to jq for the same job. {"id":1,"user":{"name":"Ada","tags":["a","b"]}} becomes the columns id, user.name, user.tags[0], user.tags[1]. An empty object or empty array flattens to an empty cell rather than being dropped or stringified as [object Object] — which is what papaparse does on its own if you hand it a nested value without flattening it first, and is the second reason (after the column-union problem above) this page exists rather than being a one-line library call.