How to Convert JSON to CSV Without Losing Nested Data

CSV is flat. JSON is not. That single sentence explains every difficulty in converting between them.

A JSON document can nest objects inside arrays inside objects to arbitrary depth. A CSV file is a rectangle of rows and columns with no notion of hierarchy. Getting from one to the other means making explicit decisions about how structure collapses — and if you skip those decisions, a converter makes them for you, usually badly. This post covers the decisions worth making. For JSON's own rules, see The Complete Guide to JSON.

Step one: find the array of rows

CSV is a list of records, so the conversion needs a JSON array of objects to work with. Most real API responses do not hand you one directly — they wrap it:

{
  "status": "ok",
  "page": { "next": "cur_9f2a", "total": 4821 },
  "data": [
    { "id": 1, "name": "Ada",   "team": { "name": "Platform", "id": "t_1" }, "tags": ["admin", "billing"] },
    { "id": 2, "name": "Grace", "team": { "name": "Data",     "id": "t_2" }, "tags": ["editor"] }
  ]
}

The rows are in data, not at the top level. Point your converter at that array, or extract it first — a JSONPath expression like $.data[*] does it in one step, which is one of the more common uses of JSONPath queries.

If you are not sure where the rows live in an unfamiliar response, open it in a JSON Tree Viewer and collapse the top level. The array you want is usually obvious once the structure is visible: it is the one node with many similar children.

Step two: flatten nested objects

Nested objects are the easy case. Dot notation turns each path into a column name:

id	name	team.name	team.id
1	Ada	Platform	t_1
2	Grace	Data	t_2

This is lossless and reversible — given the header row, you can rebuild the original nesting exactly. It is the default in most good converters and the one to prefer.

Two things to watch. Deep nesting produces long column names like user.profile.settings.notifications.email — unwieldy in a spreadsheet but still correct; rename them after import if a human is reading the file. Key collisions happen when a key literally contains a dot, so two different paths flatten to the same name. Rare, but it silently overwrites data, so pick a different separator such as __ if your keys contain dots.

Step three: decide what to do with arrays

This is where conversions go wrong, because there is no correct answer — only trade-offs. Take the tags field above.

Index each element into its own column. tags.0, tags.1, and so on. Lossless and reversible, but the column count is set by the longest array in the dataset — a record with forty tags adds forty columns to every row. Fine for arrays with a small, predictable maximum length, such as a coordinate pair.

Join into a single cell. tags becomes "admin, billing" or admin|billing. Keeps the table narrow and human-readable, and it is what people usually want for a report. The cost is that you cannot reliably reverse it — if a tag itself contains your delimiter, the join is ambiguous. Pick a delimiter that cannot appear in the data, such as | or a semicolon, rather than a comma.

Explode into multiple rows. One row per array element, with all the parent fields repeated. Ada's record becomes two rows, identical except for the tag. This is the right choice when the array holds the entities you actually care about — order line items, transactions, events — because it gives you a proper fact table that pivots and groups correctly. The cost is duplicated parent data and a row count that no longer matches the record count, so any COUNT(*) needs care.

Drop it. Sometimes the array is not needed in the export. Do that deliberately, rather than letting a converter silently emit [object Object].

Choose based on what happens next. Feeding a pivot table or a BI tool? Explode. Sending a summary to a colleague? Join. Round-tripping back to JSON later? Index.

Step four: get the CSV escaping right

CSV looks trivial and has a surprising number of rules, defined loosely by RFC 4180 and interpreted slightly differently by every tool.

A field must be wrapped in double quotes if it contains a comma, a double quote, or a line break. Inside a quoted field, a literal double quote is written as two double quotes. So the JSON string She said "hi", loudly becomes:

"She said ""hi"", loudly"

Most CSV corruption traces back to a converter that did not do this. A single unquoted comma in an address field shifts every subsequent column on that row, and because the file still parses, nobody notices until the numbers are wrong.

Other details that bite:

• Line endings. RFC 4180 specifies CRLF; Unix tools emit LF. Most parsers accept both, some Windows tools do not.
• Encoding. Write UTF-8. Excel on Windows historically assumed the system codepage unless the file carries a UTF-8 BOM — the one place a BOM helps, the opposite of the JSON situation where it causes parse errors.
• Null versus empty. CSV cannot distinguish JSON null from "". Pick a convention, usually an empty cell for null, and be consistent — it matters once the file reaches a database.

If a CSV you have been given is already misaligned or inconsistently quoted, a CSV Formatter will normalise the quoting and delimiters so you can see the real shape before converting anything.

Doing the conversion

For a one-off — an API response you need in a spreadsheet in the next two minutes — paste it into a JSON to CSV Converter. It finds the record array, flattens nested objects with dot notation, applies the CSV quoting rules, and gives you text you can drop straight into Excel, Sheets, or Numbers.

Because it runs entirely in the browser, the data never leaves your machine — which matters unusually much for this task. The JSON people most often need as a spreadsheet is an export of customer records, order history, or user analytics: exactly the data you should not paste into an unknown server.

The reverse direction is just as common — a spreadsheet arrives and you need it as an API fixture. A CSV to JSON Converter does that, and dot-notation columns can be rebuilt into real nesting rather than a flat object per row.

For anything recurring, script it: jq handles the common cases from the command line, and pandas' json_normalize implements dot-notation flattening with a configurable separator and depth limit.

Opening the result in Excel without breaking it

The last mile ruins more exports than the conversion does. Excel auto-detects column types on import and is aggressive about it: leading zeros vanish from postcodes and product codes, long numeric IDs turn into scientific notation and lose precision permanently, and anything resembling a date gets reformatted according to your locale — which is how 03/04 becomes ambiguous between March and April.

The fix is to stop double-clicking the file. Use Data → From Text/CSV, then set the affected columns to Text in the import preview before loading. Google Sheets has the same trap and the same escape hatch under File → Import, with "Convert text to numbers and dates" turned off.

If precision matters — financial identifiers, integers above 2^53 — keep those fields as strings on the JSON side too, since JavaScript numbers lose precision at the same threshold. What JSON is at the type level and what a spreadsheet infers from a text file are different systems, and the conversion is where the mismatch surfaces.

A checklist that holds up

1. Locate the array of records; extract it if it is wrapped in an envelope.
2. Flatten nested objects with dot notation — lossless, reversible, boring.
3. Decide arrays deliberately: index, join, explode, or drop.
4. Verify the escaping on a row containing a comma and a quote.
5. Import into the spreadsheet as text where precision matters.

Get those five right and the conversion stops being lossy. Skip step three and you will discover months later that the column of [object Object] was the data you needed.