JSON to CSV

CSV is rectangular and JSON is a tree, so every converter has to make a series of judgement calls. Most make them silently. This page makes them, states them, and lets you change them.

There is no file size cap and no daily limit, because there is no server to impose one.

Columns are the union, not the first object

The single most consequential decision. A converter that reads the keys of element zero silently drops every field that only appears in later records, and you discover it downstream when a column is missing from a report.

This one collects every path across every row, in the order each was first seen. It is slower and it is correct. Papa Parse, the most popular JavaScript CSV library, takes the first object by default and needs an explicit columns option to do otherwise, which is worth knowing if you are building this yourself.

Arrays inside a row: four policies, one default

This is the genuinely ambiguous case, and there is no right answer, only a right default.

Index columns, the default

tags.0, tags.1 and so on. Lossless and round-trips. A single long array explodes the column count, which is the cost.

Join into one cell

Values joined with a semicolon. Readable, lossy, and unsafe if a value contains the separator.

JSON in one cell

The array serialised as JSON text. Lossless and compact, but needs re-parsing downstream.

Explode into rows

One output row per array element, repeating the scalar columns. This is pandas json_normalize record_path. Correct for a one-to-many relationship and wrong for everything else, so it is opt-in and applies to a named path.

Two things about Excel that are not optional

The UTF-8 byte order mark

Excel does not detect UTF-8 in a CSV. Without a BOM at the start of the file it reads the bytes in the system code page and every accented character and emoji arrives corrupted. The BOM is on by default here for exactly that reason, and off is one click away for pipelines that choke on it.

Formula injection

A cell beginning with =, +, - or @ is executed as a formula by Excel, Google Sheets and LibreOffice. A value of =HYPERLINK("http://evil","click") in a CSV you generated becomes a live link in someone else's spreadsheet. OWASP calls this CSV injection. Such cells get an apostrophe prefix by default, and the tool tells you when it did it.

null against empty string

These are different values in JSON and Excel shows both as blank, so most converters flatten them together and the distinction is lost. Here null becomes an empty unquoted cell and an empty string becomes an empty quoted cell, which means the round trip survives. It costs two characters per empty string and it is worth it.

How to do this in code

Converting in code, with the arguments that decide whether it is correct.

import pandas as pd

# Flatten nested objects to dotted columns
df = pd.json_normalize(records)
df.to_csv('out.csv', index=False, encoding='utf-8-sig')

# One row per element of a nested array
df = pd.json_normalize(records, record_path='items', meta=['id'])

# Union of keys as the header, then the rows
jq -r '(map(keys) | add | unique) as $c
       | $c, (.[] | [.[$c[]]])
       | @csv' records.json > out.csv

# @csv quotes and escapes correctly; @text does not

import Papa from 'papaparse';

// Pass the union explicitly. Without it, Papa takes the keys of
// the first object and silently drops the rest.
const columns = [...new Set(records.flatMap(Object.keys))];
const csv = Papa.unparse(records, { columns });

w := csv.NewWriter(f)
w.Write(columns)
for _, rec := range records {
    row := make([]string, len(columns))
    for i, c := range columns {
        row[i] = fmt.Sprint(rec[c])
    }
    w.Write(row)
}
w.Flush()

Questions

Why does my CSV have strange characters in Excel?

The file is UTF-8 and Excel read it as the system code page. Keep the byte order mark option on. If the file is going somewhere other than a spreadsheet, turn it off, since some parsers treat the BOM as part of the first column name.

Why do some cells start with an apostrophe?

Because they began with =, +, - or @, which spreadsheets execute as formulas. The apostrophe neutralises that. Turn off the option if you need the raw value and you trust where the file is going.

What if my data is not an array of objects?

A single object becomes one row. An array of scalars becomes one column. A wrapper like {"data": [...]} uses the inner array and tells you it made that choice, because it is a guess rather than a rule.

Which delimiter for European Excel?

Semicolon. Excel picks its delimiter from the system list separator, which is a semicolon in locales where the comma is the decimal mark. That is why a comma-separated file opens as a single column on a German or French machine.