Utilities

JSON to CSV: The Practical Guide for API Responses and Exports

May 16, 20266 min read

Why You Need a Proper JSON to CSV Tool

Every developer reaches this moment: a stakeholder asks for "just the data, in a spreadsheet." You have a JSON array from an API or a database dump. You think *I'll just paste it into Excel* — and a minute later you're staring at a single cell containing the raw JSON, with no idea where to start.

JSON and CSV look superficially similar (both are rows of records), but they have fundamentally different shapes. JSON is **hierarchical** — values can be nested objects, arrays, or arrays of objects. CSV is **strictly flat** — every row has the same set of columns, and every cell holds a single string. Converting cleanly between the two means deciding how to handle the parts that don't translate directly.

The Three Hard Parts

1. Nested objects

Given `{"id": 1, "address": {"city": "Paris", "country": "FR"}}`, you have two reasonable choices:

Flatten with dot notation.: Columns become `id`, `address.city`, `address.country`. This is what you almost always want — it makes the data sortable and filterable in a spreadsheet.

Serialize as JSON.: The whole `address` object becomes a JSON string in one cell. Useful when you need to round-trip the data back to JSON later without losing structure.

Our [JSON to CSV converter](/json-to-csv) lets you toggle between these with a single switch.

2. Arrays inside fields

What do you do with `"tags": ["admin", "beta"]`? You can't break it into multiple columns without making every row a different shape. The common conventions:

Join with a separator: (semicolon is standard, since commas would conflict with the column delimiter): `admin; beta`.

Serialize the whole array as JSON: `["admin","beta"]` — preserves structure, slightly harder to read.

For arrays of objects (`[{...},{...}]`), serializing as JSON is usually the only sane choice.

3. Escaping special characters

This is where most home-grown converters break. CSV has three special characters: the delimiter (comma), the double quote, and the newline. If any of them appear inside a value, the value **must** be wrapped in double quotes, and any double quote inside must be doubled (`"` → `""`).

Example: a name field containing `Chiara, the third` must become `"Chiara, the third"` in the CSV. A quote inside a quote: `He said "hi"` becomes `"He said ""hi"""`. Get this wrong and Excel will misalign every row from that point on.

Picking a Delimiter

The default is comma. But:

In Germany, France, and most of continental Europe: , Excel expects **semicolon** because comma is the decimal separator. Open a comma-CSV in German Excel and every row lands in column A.

Tab-separated: is great when your data contains many commas — it avoids most quoting.

Pipe: (`|`) is a niche but useful choice for free-text content that contains both commas and tabs.

When in doubt: comma for the US/UK, semicolon for Europe, tab for "this data is messy."

A Worked Example

Input:

```

[

{"id": 1, "name": "Alice", "address": {"city": "Paris"}, "tags": ["admin"]},

{"id": 2, "name": "Bob, Jr.", "address": {"city": "Berlin"}, "tags": ["user","beta"]}

]

```

Output (comma, headers on, flatten on):

```

id,name,address.city,tags

1,Alice,Paris,admin

2,"Bob, Jr.",Berlin,user; beta

```

Notice three things: the nested `address.city` became its own column, `Bob, Jr.` got quoted because of the embedded comma, and the tags array got joined with `; `.

Edge Cases Worth Knowing

Missing keys across rows.: If row 1 has `email` but row 2 doesn't, row 2 gets an empty cell — which is correct CSV behavior.

Null values.: Usually written as empty cells. Some tools write the literal string `null` — be explicit about what you want.

Numbers and booleans.: Written as their string form (`42`, `true`). Excel will auto-detect the type when you open the file.

UTF-8 BOM.: Old Excel for Windows needs a BOM at the start of the file to detect UTF-8 correctly. Modern Excel handles it fine without.

When Not to Use CSV

If your data has deeply nested structures, many array-of-objects fields, or rich types (dates with timezones, very large integers, binary blobs), CSV is the wrong format. Consider XLSX (real types, multiple sheets), Parquet (columnar, typed, compressed) or just keeping it as JSON. CSV shines for **flat, tabular, human-friendly** data — and not much else.

Conclusion

JSON to CSV is one of those tasks that looks like a one-liner until you have to handle nested objects, embedded commas, and locale-specific delimiters at the same time. A good tool makes the decisions you'd make yourself, gets the escaping right, and runs locally so your data stays private. Our [JSON to CSV converter](/json-to-csv) does all three — paste, click, download.

Ready to try it?

Use our free tool — no signup, no watermarks, no limits.

Conversion