You just got a fresh data export from your engineering team. You open it up, expecting a nice spreadsheet, and instead, you see curly braces { }, brackets [ ], and indented text.
It’s JSON. And while developers love it, it is a nightmare for business analysis.
You try opening it in Excel. It breaks.
You try a generic online converter. It gives you a column called [Object object].
You try to explain to your manager why you can’t just “filter by city” because the city is buried three levels deep in an address object.
In this guide, we’ll explain the logic of “Flattening” and how to turn complex, nested API data into a clean CSV that Excel understands immediately.
The Problem: JSON is 3D, Excel is 2D
The fundamental disconnect is geometry.
- Excel (CSV) is two-dimensional. It has Rows and Columns. It is flat.
- JSON is multi-dimensional. It is a tree structure. A single “User” row can contain a list of “Orders,” and each order can contain a list of “Items.”
When you try to force a Tree into a Table, you usually lose data. Most basic converters simply give up when they hit a nested object, leaving you with useless cell data like:
| ID | Name | Address |
| 101 | Raaj | [Object object] |
The Solution: Dot Notation Flattening
To make JSON readable in Excel without losing data, you need to “flatten” the tree using Dot Notation. This technique pulls nested fields up to the top level by combining their parent names.
Example: Nested Objects
Input (JSON):
JSON
{
"user": "Raaj",
"contact": {
"email": "raaj@example.com",
"phone": {
"mobile": "555-0199",
"home": "555-0100"
}
}
}
Output (Flattened CSV):
Instead of one “Contact” column, a smart converter creates specific columns for every leaf of the tree:
| user | contact.email | contact.phone.mobile | contact.phone.home |
| Raaj | raaj@example.com | 555-0199 | 555-0100 |
The Edge Case: Arrays (Lists)
This is where 90% of converters fail. What happens if a user has multiple roles?
JSON
"roles": ["Admin", "Editor", "Billing"]
If you create a separate row for every role, you duplicate the user 3 times (messing up your pivot tables). If you create columns (role_1, role_2), your schema becomes unstable.
The Best Practice:
The cleanest way to handle arrays for Excel is Delimited Flattening. We squash the list into a single cell, separated by semicolons.
| user | roles |
| Raaj | Admin;Editor;Billing |
This keeps your row count accurate while preserving all the data.
Tutorial: Convert JSON to CSV in 3 Clicks
We built a JSON ⇄ CSV Converter specifically to handle these edge cases. It automatically detects nested objects and arrays, flattening them into a perfect spreadsheet format.
Step 1: Input Your Data
Paste your raw JSON output into the left panel of the tool.
- Tip: Look for the Validation Dot. If it’s red, you likely have a syntax error (like a missing comma). The tool checks this in real-time.
Step 2: Auto-Flatten
Click the Right Arrow (→) button.
The tool’s engine recursively walks through your JSON tree.
- It identifies keys like
contact.email. - It detects arrays and joins them with
;. - It generates a standard CSV header row.
Step 3: Download for Excel
Click the Download Button to get your .csv file. You can now double-click this file to open it in Excel, Google Sheets, or Numbers with zero formatting issues.
Reverse It: CSV back to JSON
The best part? This tool is Bi-Directional.
If you edit that spreadsheet in Excel—changing raaj@example.com to sarah@example.com—you can paste the CSV back into the right panel, click the Left Arrow (←), and it will reconstruct the original JSON structure, complete with nested objects.
[Try the Enterprise JSON <-> CSV Converter]
- Handles Deep Nesting
- Smart Array Formatting
- 100% Client-Side Privacy
Stop manually formatting your data. Flatten it instantly.