Open huge JSON files instantly. Free & private.๐Ÿ”’ Your data never leaves your browser

How to Convert Nested JSON to CSV (and Open It in Excel)

CSV is a flat table: rows and columns. JSON is a tree: objects inside objects, arrays inside arrays. Converting one to the other means deciding how to squeeze the tree into a table. This guide shows the standard approach (flattening with dotted column names), what to do with arrays, and three ways to do the conversion.

The example

[
  {
    "id": 1,
    "customer": { "name": "Ann", "city": "Cairo" },
    "items": [ { "sku": "A1", "qty": 2 }, { "sku": "B7", "qty": 1 } ],
    "total": 25.5
  },
  {
    "id": 2,
    "customer": { "name": "Bob", "city": "Berlin" },
    "items": [ { "sku": "C3", "qty": 4 } ],
    "total": 12
  }
]

Rule 1: Each object in the array becomes a row

Your JSON should be an array of similar objects. Each object becomes one CSV row. If your data is wrapped, for example { "data": [ ... ] }, use the inner array. (A JSONPath query like $.data helps you pull it out.)

Rule 2: Nested objects become dotted columns

"customer": { "name": "Ann" } becomes a column called customer.name. This is called flattening, and it keeps every value while showing where it came from:

idcustomer.namecustomer.cityitemstotal
1AnnCairo[{"sku":"A1","qty":2},โ€ฆ]25.5
2BobBerlin[{"sku":"C3","qty":4}]12

Rule 3: Decide what to do with arrays

Arrays are the tricky part, because one row can contain many items. There are three common choices:

  1. Keep the array as JSON text in one cell (as above). Nothing is lost, and the row count stays the same. This is the safest default.
  2. One row per array item ("exploding"). Order 1 becomes two rows, one for A1 and one for B7, with the order fields repeated. This is best for analysis, such as counting quantities per SKU.
  3. Numbered columns like items.0.sku, items.1.sku. This only works when arrays are short and have a fixed length.

Method 1: Online, no code

  1. Paste or open your JSON in JSON to CSV.
  2. Keep Flatten nested objects on. Nested objects become dotted columns and arrays are kept as JSON text.
  3. Check the preview table, then click Download CSV.
Try it: JSON to CSVPaste nested JSON and get a flat CSV with a live preview.
Open JSON to CSV โ†’

Want a real Excel file instead? CSV / JSON to Excel accepts the same JSON and downloads an .xlsx file, with numbers stored as numbers and columns sized to fit.

Method 2: Python with pandas

pandas.json_normalize flattens nested objects with the same dotted naming. With record_path it can also explode an array into rows:

import json
import pandas as pd

with open("orders.json") as f:
    data = json.load(f)

# One row per order (customer.name, customer.city, ...)
orders = pd.json_normalize(data)
orders.to_csv("orders.csv", index=False)

# One row per item, with order fields repeated
items = pd.json_normalize(
    data,
    record_path="items",
    meta=["id", ["customer", "name"]],
)
items.to_csv("order_items.csv", index=False)

Method 3: jq on the command line

jq's @csv filter writes properly quoted CSV. You choose the columns yourself:

jq -r '["id","name","city","total"],
       (.[] | [.id, .customer.name, .customer.city, .total])
       | @csv' orders.json > orders.csv

And one row per item:

jq -r '.[] | .id as $id | .items[] | [$id, .sku, .qty] | @csv' orders.json

Opening the CSV in Excel

If Excel shows everything in one column or breaks accented letters, the cause is the delimiter or the text encoding. This guide explains the fix. The simplest way around it is to create an .xlsx file directly.

๐Ÿ’ก Objects that do not all have the same keys are fine: the converter creates a column for every key it finds and leaves cells empty where a key is missing.

FAQ

Can I convert the CSV back to JSON?

Yes, with CSV to JSON, but dotted columns stay as flat keys like "customer.name". Flattening loses the exact nesting, so keep the original JSON if you need it.

My JSON is one object, not an array

Then the result is a single row. If the object holds a list somewhere inside, convert that inner list instead.

Tools used in this guide

More guides