How to Convert Nested JSON to Excel Without Losing Data

Why Nested JSON Is a Problem for Spreadsheets

A spreadsheet is two-dimensional: rows and columns. JSON is a tree. Consider one order:

{
  "orderId": "A-1001",
  "customer": { "name": "Sara Malik", "address": { "city": "Karachi", "country": "PK" } },
  "tags": ["gift", "express"],
  "items": [
    { "sku": "MUG-01", "qty": 2, "price": 8.5 },
    { "sku": "TEA-04", "qty": 1, "price": 12 }
  ]
}

customer is an object nested two levels deep, tags is an array of strings, and items is an array of objects. Each needs a different treatment. A naive converter produces cells containing [object Object] or silently drops the arrays.

Nested Objects: Flatten Into Columns

Objects are the easy part. Each nested key becomes its own column, with the path joined by dots:

This keeps one row per order and makes every value filterable. The only downside is long column names, which you can rename after flattening.

The alternative is to keep customer as JSON text in one cell. That's useful when the nested data is irrelevant to your analysis but must be preserved.

Arrays of Simple Values: Four Options

For tags: ["gift", "express"]:

Joining is usually the friendliest. Numbered columns get unwieldy if one record has 50 tags and the rest have two.

Arrays of Objects: The One-to-Many Problem

items is the hard case. One order has many items, so there are three common approaches:

1. Separate Sheet With a Link (Recommended)

Put orders on one sheet and items on another, with a column linking each item to its order's row:

This mirrors how a database would store the data. You can pivot items, sum quantities and still trace each one back to its order with a lookup.

2. One Row Per Item (Explode)

Repeat the order's fields on every item row. It's easy to filter but duplicates data, and summing an order-level field such as shipping cost will double-count it.

3. Keep as JSON Text

Store the whole items array in one cell. Nothing is lost, but you can't analyse it in Excel without further work.

Doing It With a Converter

The JSON to Excel converter offers each of these approaches as a setting. Flatten objects into dotted columns or keep them as JSON; join arrays, expand them into numbered columns, keep them as JSON, or move arrays of objects to their own sheet with a parent_row column. You can then drop columns you don't need in the mapping table, and the preview updates as you change settings, so you can see the shape before downloading. It runs in your browser, so the data stays on your device.

Doing It in Python

pandas handles both object flattening and one-to-many expansion:

import json
import pandas as pd

orders = json.load(open("orders.json", encoding="utf-8"))

# One row per order, nested objects flattened
orders_df = pd.json_normalize(orders).drop(columns=["items"])

# One row per item, with the order ID carried along
items_df = pd.json_normalize(orders, record_path="items", meta=["orderId"])

with pd.ExcelWriter("orders.xlsx") as xl:
    orders_df.to_excel(xl, sheet_name="orders", index=False)
    items_df.to_excel(xl, sheet_name="items", index=False)

Here the link is the orderId column rather than a row number, which is often better when the data has a natural key.

Common Mistakes to Avoid

• Losing arrays silently. Check that every top-level key appears somewhere in the output.
• Exploding without noticing. If your row count jumped from 1,000 orders to 3,400 rows, you've exploded an array.
• Inconsistent records. If some records have address as an object and others as a string, flattening produces mixed columns. Look at a few odd records before converting.
• Very deep nesting. Ten levels of dotted names are unreadable. Consider which levels you actually need.

If you're not sure the JSON is valid in the first place, how to convert JSON to Excel covers validation and type handling, and exporting API JSON deals with wrapper objects around the records.

Summary

Flatten objects into columns, choose deliberately for arrays, and use a linked second sheet for arrays of objects. Check row counts and columns afterwards to make sure nothing was dropped or duplicated.