How to Convert Nested JSON to Excel Without Losing Data

If you’ve ever pulled an order feed from Shopify, a product catalog from an API, or analytics from a marketplace and tried to open it in Excel, you know the pain: half the fields show up as [object Object] or get silently dropped. Nested JSON and spreadsheets are fundamentally different data shapes, and naive converters lose data every time. This guide walks you through converting nested JSON to Excel properly with Python and pandas — flattening objects, exploding arrays, and preserving the parent-child relationships you’d otherwise destroy.

By the end, you’ll have a complete, reusable script you can point at any nested JSON file.

Why Nested JSON Can’t Be Represented as One Table

Excel is a two-dimensional grid: rows and columns, one value per cell. JSON has no such constraint. A single order record can contain a customer object, an array of line items, each line item with its own array of discounts. That’s a tree, not a table.

When you force a tree into one grid, you face three structural problems:

1. Nested objects (order.customer.email) need flattening into dot-notated columns.
2. Arrays (order.line_items[]) need either one row per element or serialization into a single cell.
3. Mixed depth — some records have three line items, others have zero — breaks any fixed column layout.

Online converters typically handle problem #1 and ignore #2 entirely, which is how you end up with an Excel file missing 40% of your order data. The fix is deliberate flattening, not blind conversion.

Sample Nested JSON File

Save this as orders.json. We’ll use it throughout — it contains every problem you’ll hit in the wild: nested objects, arrays, missing keys, and empty lists.

[
  {
    "order_id": "ORD-1001",
    "created_at": "2026-02-14T09:32:00Z",
    "customer": {
      "id": "CUST-88",
      "name": "Alicia Gomez",
      "email": "alicia@example.com",
      "address": {
        "city": "Austin",
        "country": "US"
      }
    },
    "line_items": [
      {"sku": "TSHIRT-BLK-M", "qty": 2, "price": 24.99},
      {"sku": "MUG-CER-01", "qty": 1, "price": 12.50}
    ],
    "total": 62.48
  },
  {
    "order_id": "ORD-1002",
    "created_at": "2026-02-14T11:05:00Z",
    "customer": {
      "id": "CUST-91",
      "name": "David Park"
    },
    "line_items": [],
    "total": 0.00
  }
]

Notice ORD-1002 is missing email, address, and has an empty line_items array. Any robust pipeline must survive this.

What You Need

• Python 3.10 or newer
• pandas and openpyxl installed: pip install pandas openpyxl
• Your JSON file (array-of-objects format works best; we’ll handle line-delimited JSON too)

Step 1: Flatten Objects with pandas.json_normalize()

pandas.json_normalize() is the workhorse for nested objects. It walks the tree and produces dot-notated columns.

import pandas as pd
import json

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

df = pd.json_normalize(data, sep="_")
print(df.columns.tolist())

Output columns:

['order_id', 'created_at', 'customer_id', 'customer_name',
 'customer_email', 'customer_address_city',
 'customer_address_country', 'line_items', 'total']

Two important choices here:

• sep="_" instead of the default "." — dots in column names cause headaches later if you ever push this data to SQL or back to JSON.
• Missing keys (customer_email for ORD-1002) become NaN, not errors. That’s what you want.

But look at line_items — it’s still a list of dicts sitting inside a single cell. That’s Step 2’s job.

Step 2: Handle Arrays with explode()

You have two legitimate strategies for arrays, and picking the wrong one is the most common data-loss bug:

For anything you’d want to sum, filter, or pivot in Excel, explode. Here’s the full pattern:

df_items = pd.json_normalize(
    data,
    record_path="line_items",      # the array to explode
    meta=["order_id",              # parent fields to carry down
          ["customer", "id"],     # nested parent fields
          "total"],
    meta_prefix="order_",
    errors="ignore"
)

The record_path / meta combo is the key insight most tutorials skip: meta preserves parent context on every exploded child row. Each line item row carries its order_id, so you can always join back.

Caveat: orders with empty line_items arrays (ORD-1002) vanish from this output. If you need them, do a second pass on the parent-level dataframe for rows where the array is empty and append placeholder rows.

Step 3: Recursive Flattening for Deeply Irregular JSON

json_normalize struggles when your JSON is deeply nested and irregular — think product feeds where attributes vary per category. A recursive flattener gives you total control:

def flatten_json(obj, parent_key="", sep="_"):
    items = {}
    for k, v in obj.items():
        new_key = f"{parent_key}{sep}{k}" if parent_key else k
        if isinstance(v, dict):
            items.update(flatten_json(v, new_key, sep))
        elif isinstance(v, list):
            # leave lists intact for explode() later
            items[new_key] = v
        else:
            items[new_key] = v
    return items

flat_records = [flatten_json(record) for record in data]
df = pd.DataFrame(flat_records)

This approach shines when you need custom rules — e.g., truncating keys deeper than four levels, or serializing lists under a certain key but exploding others. It’s also easier to debug: wrap the loop in a try/except and log the offending key path.

Step 4: Export with to_excel()

Export both tables — parent orders and exploded line items — to separate sheets in one workbook. This mirrors the JSON’s natural structure and is far more useful than a single denormalized sheet:

with pd.ExcelWriter("orders_export.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="orders", index=False)
    df_items.to_excel(writer, sheet_name="line_items", index=False)

Three Excel-specific gotchas:

• Timezone-aware datetimes throw ValueError: Excel does not support datetimes with timezones. Strip them first: df["created_at"] = pd.to_datetime(df["created_at"]).dt.tz_localize(None).
• Any remaining dict/list values in cells must be serialized: df[col] = df[col].apply(lambda x: json.dumps(x) if isinstance(x, (dict, list)) else x).
• Sheet names are capped at 31 characters and can’t contain []:*?/\.

Step 5: Preserve Parent and Child IDs

This is where most conversions quietly destroy your data’s usefulness. Before flattening, guarantee every level has a stable identifier:

for record in data:
    for idx, item in enumerate(record.get("line_items", [])):
        item["line_index"] = idx  # synthetic child ID if none exists

Rules I follow on every pipeline:

1. Never rely on row position alone — exploded rows get reordered by sorts and filters in Excel.
2. Carry the parent key into every child row via meta (as in Step 2).
3. Synthesize child IDs (order_id + line_index) when the source doesn’t provide them. A composite key like ORD-1001_0 survives any downstream join.

If you’re exporting for someone who’ll analyze in Excel, add these keys explicitly — a VLOOKUP between the orders and line_items sheets on order_id takes them ten seconds and answers 90% of their questions.

Common KeyError Problems (and Fixes)

The failures you’ll actually hit:

• KeyError in meta: a nested parent field like ["customer", "email"] is missing in some records. Fix: add errors="ignore", or pre-fill missing keys with record.setdefault("customer", {}).
• record_path KeyError: some records lack the array entirely. Fix: filter first — [r for r in data if r.get("line_items")].
• TypeError: unhashable type in DataFrame construction: a list snuck into a flattened field. Fix: your recursive flattener’s isinstance(v, list) branch is being bypassed — check for tuples or nested lists-of-lists.
• Silent column loss: json_normalize on records with wildly different keys produces a sparse frame, which is correct behavior — but verify with df.notna().sum() before assuming columns disappeared.

Complete Python Script

import json
import pandas as pd

def flatten_json(obj, parent_key="", sep="_"):
    items = {}
    for k, v in obj.items():
        new_key = f"{parent_key}{sep}{k}" if parent_key else k
        if isinstance(v, dict):
            items.update(flatten_json(v, new_key, sep))
        else:
            items[new_key] = v
    return items

def nested_json_to_excel(input_path, output_path, array_key, parent_keys):
    with open(input_path) as f:
        data = json.load(f)

    # Sheet 1: flattened parent records (arrays serialized)
    parents = []
    for r in data:
        flat = flatten_json(r)
        flat[array_key] = json.dumps(r.get(array_key, []))
        parents.append(flat)
    df_parents = pd.DataFrame(parents)

    # Sheet 2: exploded child rows with parent IDs
    children_src = [r for r in data if r.get(array_key)]
    for r in children_src:
        for i, item in enumerate(r[array_key]):
            if isinstance(item, dict):
                item["line_index"] = i
    df_children = pd.json_normalize(
        children_src, record_path=array_key,
        meta=parent_keys, errors="ignore"
    )

    # Clean datetimes and residual objects
    for df in (df_parents, df_children):
        for col in df.columns:
            if pd.api.types.is_datetime64tz_dtype(df[col]):
                df[col] = df[col].dt.tz_localize(None)
            elif df[col].apply(lambda x: isinstance(x, (dict, list))).any():
                df[col] = df[col].apply(
                    lambda x: json.dumps(x) if isinstance(x, (dict, list)) else x
                )

    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        df_parents.to_excel(writer, sheet_name="records", index=False)
        df_children.to_excel(writer, sheet_name=array_key, index=False)

if __name__ == "__main__":
    nested_json_to_excel(
        input_path="orders.json",
        output_path="orders_export.xlsx",
        array_key="line_items",
        parent_keys=["order_id", ["customer", "id"], "total"],
    )

Change array_key and parent_keys to match your file — everything else is reusable.

FAQ

Can I convert JSON to Excel without Python? Excel’s Power Query (Data → Get Data → From File → From JSON) handles moderately nested JSON and lets you expand columns via the UI. It works for simple cases, but arrays-of-objects still require manual expansion steps, and refreshable queries on large files get slow.

What’s the difference between json_normalize and flatten_json? json_normalize is a built-in pandas function optimized for uniform records with a known array path. Recursive flattening is custom code that handles irregular, deeply nested structures where every record may have different keys. Use json_normalize first; fall back to recursion when it breaks.

Why does my Excel file show lists or dicts inside cells? Some column still contains Python objects. Serialize them with json.dumps() before calling to_excel(), or explode them with record_path if they’re arrays you want as rows.

How do I handle JSON Lines (.jsonl) files? Replace json.load(f) with data = [json.loads(line) for line in f]. Everything else in the pipeline is identical.

Is there a file size limit when exporting JSON to Excel? Excel caps worksheets at 1,048,576 rows and 16,384 columns. Exploding large arrays can blow past the row limit fast — if you’re near it, export to CSV or split across sheets.

Wrapping Up

Converting nested JSON to Excel without losing data comes down to three decisions: flatten objects with dot notation, explode arrays into rows with parent IDs attached, and export to multiple sheets that mirror your data’s real structure. The script above handles all three — point it at your feed, adjust array_key and parent_keys, and you’ll get a workbook that actually preserves everything. If you’re building recurring exports from platforms like Shopify or Amazon SP-API, wrap this in a scheduled job and you’ll never hand-clean a spreadsheet again.