How to Convert JSON to CSV: Flattening Nested Data the Right Way
By The Smart Data Converter Team · 11 min read ·
Converting JSON to CSV sounds simple — until you hit nested objects, arrays, and records that don't all share the same keys. This guide shows you how to flatten JSON correctly so the resulting spreadsheet is complete, aligned, and ready for Excel, Google Sheets, or a database import.
The core challenge: JSON is a tree, CSV is a grid
CSV has exactly two dimensions: rows and columns. JSON can nest objects inside objects and arrays inside those. Converting therefore means flattening a tree into a grid — and there's more than one valid way to do it.
[
{
"id": 1,
"name": "Alice",
"address": { "city": "Paris", "zip": "75001" },
"tags": ["vip", "newsletter"]
}
]How should address and tags become columns? That decision is the whole game.
Strategy 1: Flatten nested objects with dotted keys
The most widely understood convention joins nested keys with a dot:
| id | name | address.city | address.zip |
|---|---|---|---|
| 1 | Alice | Paris | 75001 |
This keeps every value and is easy to reverse later. Here's a small flattener in JavaScript:
function flatten(obj, prefix = "", out = {}) {
for (const [key, value] of Object.entries(obj)) {
const path = prefix ? `${prefix}.${key}` : key;
if (value && typeof value === "object" && !Array.isArray(value)) {
flatten(value, path, out);
} else {
out[path] = Array.isArray(value) ? value.join("; ") : value;
}
}
return out;
}Strategy 2: Handle arrays deliberately
Arrays are where most conversions go wrong. You have three reasonable options:
- Join into one cell:
tags→"vip; newsletter". Simple, keeps one row per record. - Expand into columns:
tags.0,tags.1. Works when arrays have a small, fixed length. - Explode into rows: one output row per array element (a "join"). Best when each element is itself a record, such as order line items.
Pick based on how you'll use the CSV. For spreadsheets, joining into one cell is usually the least surprising.
Strategy 3: Reconcile inconsistent keys
Real JSON arrays often contain objects with different keys:
[
{ "name": "Alice", "city": "Paris" },
{ "name": "Bob", "country": "France" }
]CSV needs one consistent header, so collect the union of all keys and leave missing values blank:
| name | city | country |
|---|---|---|
| Alice | Paris | |
| Bob | France |
The no-code method
If you'd rather not write a flattener, Smart Data Converter parses your JSON, builds a unified set of columns from all records, and exports CSV in one click — entirely in your browser. Paste the JSON, preview the table, choose your delimiter (comma, semicolon, or tab), and download.
Convert JSON to CSV in Python
For repeatable pipelines, pandas handles the union-of-keys problem for you:
import json, pandas as pd
with open("data.json", encoding="utf-8") as f:
data = json.load(f)
# json_normalize flattens nested objects using dotted paths
df = pd.json_normalize(data)
df.to_csv("data.csv", index=False, encoding="utf-8")Choosing the right delimiter
The "C" in CSV is comma, but it isn't always the best choice:
| Delimiter | Use when |
|---|---|
Comma , | Default; widely compatible. |
Semicolon ; | Your values contain commas, or you're in a European locale where Excel expects it. |
| Tab | Values may contain both commas and semicolons (TSV). |
Pitfalls to watch for
- Lost nesting: Flattening is one-way unless you keep a consistent key convention. Dotted keys make it reversible.
- Numbers as text: Leading zeros (
007) and long IDs may be reformatted by Excel. Keep them as text or import carefully. - Quoting: Any value containing your delimiter, a quote, or a newline must be quoted. Use a real CSV writer, not string concatenation.
- Encoding: Save as UTF-8 to preserve accents and symbols.
Frequently asked questions
How do I convert nested JSON to CSV?
Flatten nested objects into dotted columns (address.city) and decide how to treat arrays — join them, expand them, or explode them into multiple rows.
Why does my CSV have empty cells after conversion?
Because some JSON records were missing keys that others had. The converter fills the gaps with blanks so every row aligns to the same header.
Can I go from CSV back to JSON?
Absolutely — see our CSV to JSON guide.
The secret to clean JSON → CSV is making a deliberate choice about nesting and arrays before you export. Get that right and your spreadsheets will line up every time.