Convert Nested JSON to Excel: Developer's Guide

"Can you send me that data in an Excel sheet?"

If you build APIs, you've heard this request. Stakeholders—PMs, marketers, clients—don't speak JSON. They live in Excel.

Converting a simple object is easy. But real-world data is messy. It has deep nesting, arrays of objects, and specific character encoding requirements. If you just copy-paste, you'll end up with [object Object] in your cells or a broken file that Excel can't read.

Here is how to bridge the gap between JSON trees and Excel grids without losing your sanity.

The Challenge: Mapping Trees to Grids

JSON is hierarchical (trees). Excel is 2-dimensional (grids). The mismatch happens when you try to force one into the other.

1. The Nested Object Problem

When you have a simple nested object, the standard solution is Dot Notation Flattening.

JSON Input:

json{
  "user": { "id": 101, "name": "Alice" },
  "status": "active"
}

Excel Output:

Keys become column headers combined with dots.

user.id	user.name	status
101	Alice	active

2. The Array Problem (The Tricky Part)

This is where most custom scripts fail. How do you represent a list of items inside a single row?

json{
  "order_id": 550,
  "items": [
    { "sku": "A1", "qty": 2 },
    { "sku": "B2", "qty": 1 }
  ]
}

You have two main strategies, depending on your goal:

Strategy A: Multi-Row Expansion (SQL Join Style)

Duplicate the parent data for each child item. Best for Pivot Tables and detailed analysis.

csvorder_id, items.sku, items.qty
550,      A1,        2
550,      B2,        1

Strategy B: Column Expansion (Wide Style)

Create new columns for each item. Best when the array length is fixed or small (e.g., exact 2 addresses).

csvorder_id, items.0.sku, items.0.qty, items.1.sku, items.1.qty
550,      A1,          2,           B2,          1

The "Excel Gotchas" You Didn't Expect

Even if you flatten the data correctly, Excel itself has quirks that can corrupt your data upon import.

⚠️ The Case of Vanishing Zeros

Specific strings like Zip Codes ("02110") or Credit Card last-4 ("0042") are dangerous. Excel tries to be helpful and interprets them as numbers, stripping the leading zeros (2110, 42).

The Fix: A robust converter must force these cells to be treated as Text, often by quoting them in CSVs or setting specific cell types in XLSX files.

⚠️ The UTF-8 Nightmare

Does your data contain emojis (🚀) or non-English characters (中文, Español)? If you simply save standard text as .csv and open it in Excel, you will likely see garbage characters (e.g., é).

The Fix: You must include a BOM (Byte Order Mark)—specifically \uFEFF—at the very beginning of your CSV file. This invisible marker tells Excel, "Hey, this is UTF-8 encoding, please render it correctly."

How to Do It Right (In Seconds)

You could write a Python script to handle recursion, flattening strategies, BOM addition, and cell-type enforcement. Or you can save 2 hours and use a dedicated tool.

Our tools handle these edge cases automatically:

1. Open JSON to Excel or JSON to CSV.
2. Paste your JSON. The tool automatically detects nested structures.
3. Smart Flattening. Arrays are handled intelligently to preserve data relationships.
4. Download. result files include the correct BOM headers and formatting.

FAQ

Q: Should I use CSV or Excel (XLSX)?

CSV is best for database migrations (importing into SQL) or programmatic processing. It's lightweight and text-based. Excel (XLSX) is best for humans. It preserves formatting, keeps columns wide enough to read, and ensures "02110" stays "02110".

Q: Is my data secure?

Yes. Our converters run client-side in your browser. Unlike many server-based tools, your sensitive JSON data never leaves your computer.

---

Don't let formatting block your team. Convert your JSON properly and get back to coding.