Modern REST APIs, webhooks (Stripe, Shopify, HubSpot, Supabase), MongoDB exports, and analytics logs store data as deeply nested JSON (JavaScript Object Notation) hierarchies—where a single customer record contains nested objects for billing_address, metadata, and arrays of line_items. Yet when data analysts, accountants, or operations managers try to convert a nested JSON file into a flat CSV spreadsheet for Microsoft Excel or Google Sheets using basic online converters or a quick `Object.values()` script, every nested field collapses into the infamous, useless string: [object Object].
Even worse, if the JSON contains commas inside product descriptions, newline characters inside notes, or international accented characters (such as é, ü, or £), naive CSV exporters break row alignment and turn UTF-8 text into garbled mojibake (`é`) when double-clicked in Windows Excel. This 2026 data engineering guide explains how recursive dot-notation JSON flattening works, how to handle nested arrays without duplicating parent rows, and how to convert large JSON payloads to Excel-ready CSV 100% locally in your browser RAM.
- Flatten deeply nested JSON objects into clean Excel CSV columns using our Free Client-Side JSON to CSV Converter.
- Validate and repair malformed API JSON first with the Offline JSON Formatter & Validator.
- Convert Kubernetes or CI/CD YAML configs to JSON via the YAML to JSON Converter.
- Export wide finished spreadsheets to single-page landscape PDFs using the Excel to PDF Converter.
Quick Jump Navigation (Table of Contents)
1. Why Naive JSON-to-CSV Scripts Output `[object Object]`
A CSV (Comma-Separated Values) file governed by IETF RFC 4180 is strictly a two-dimensional grid of rows and scalar text columns. JSON, by contrast, is an arbitrary-depth tree structure supporting objects within objects (`{}`) and ordered lists (`[]`).
When a basic converter iterates over top-level keys of a JSON record and coerces each value to a string (`String(row[key])`), JavaScript invokes `Object.prototype.toString()` on any nested sub-object—which literally returns the string "[object Object]", destroying 100% of the nested data:
// Input Nested JSON Payload
[
{
"order_id": "ORD-9042",
"customer": {
"name": " Nadia Mansour ",
"address": { "city": "Dubai", "country": "AE" }
},
"tags": ["priority", "wholesale"]
}
]
// BROKEN Naive CSV Output:
order_id,customer,tags
ORD-9042,[object Object],"priority,wholesale"
// CORRECT Dot-Notation Flattened CSV Output:
order_id,customer.name,customer.address.city,customer.address.country,tags.0,tags.1
ORD-9042,Nadia Mansour,Dubai,AE,priority,wholesale
2. The 3 Core Algorithms for Flattening Nested JSON Objects & Arrays
Depending on how you plan to analyze the resulting spreadsheet in Excel (PivotTables, VLOOKUP/XLOOKUP, or database import), there are three distinct strategies for handling nested structures:
| JSON Structure Type | Flattening Method | Resulting CSV Column Header | Best Excel / Analytics Use Case |
|---|---|---|---|
| Nested Child Objects (`{a: {b: 1}}`) | Recursive Dot-Notation (`a.b`) | user.profile.email, user.address.zip |
Preserves 1-row-per-record cardinality for CRM imports and VLOOKUPs. |
| Primitive String/Number Arrays (`[“a”,”b”]`) | Pipe/Semicolon Join (`a | b`) or Indexed (`tags.0`) | tags ("vip | wholesale") or tags.0, tags.1 |
Keeps tag lists searchable inside a single filterable Excel column. |
| Heterogeneous Sparse Keys | Two-Pass Schema Union Discovery | Union of all keys across rows 1..N |
Ensures optional fields that only appear on row #450 are not silently dropped! |
Why Two-Pass Schema Discovery Is Critical for Real-World API Exports
In SQL tables, every row has the exact same columns. In NoSQL databases (MongoDB, Firestore, DynamoDB) and Stripe/Shopify API responses, optional fields are frequently omitted when null. If Row 1 has 5 keys, and Row 87 introduces an optional discount.coupon_code key, a single-pass converter that only inspects Row 1 to build the CSV header will silently delete `discount.coupon_code` from your entire spreadsheet!
Trencada’s JSON to CSV Converter performs a full **Two-Pass Schema Union** across every object in your JSON array before generating the header row, guaranteeing zero dropped columns even on sparse NoSQL exports.
3. Fixing Broken Excel Rows & Garbled Characters: RFC 4180 + The 3-Byte UTF-8 BOM
Even after flattening nested keys, two formatting traps frequently corrupt CSV files when opened in Microsoft Excel on Windows:
- Unescaped Embedded Commas, Quotes & Newlines (RFC 4180 Rule): If a JSON field contains a comma (`”Austin, TX”`), double quotes (`”15″ Laptop”`), or a line break (`n`), outputting it raw shifts every subsequent column to the right. Under RFC 4180, any cell containing `,`, `”`, `r`, or `n` must be wrapped in double quotes (`”…”`), and any internal double quote must be escaped by doubling it (`””` → `""15"" Laptop"`).
- The Windows Excel UTF-8 Byte Order Mark (`EF BB BF`) Fix: When you double-click a standard UTF-8 `.csv` file in Windows File Explorer, Excel’s legacy CSV loader assumes the file is encoded in `Windows-1252 (ANSI)` unless the file begins with the 3-byte UTF-8 Byte Order Mark (`0xEF, 0xBB, 0xBF` / `uFEFF`). Prepending `uFEFF` forces Excel on both Windows and macOS to render currency symbols (`€`, `£`, `¥`), smart quotes, and international names accurately on the first click.
4. Step-by-Step: Converting Nested JSON to Clean Excel CSV Locally
- Step 1 — Validate Your JSON (If Copying from Logs): If your JSON payload was copied from a console log or truncated webhook, paste it into the Offline JSON Formatter & Validator to verify syntax.
- Step 2 — Load Into the Client-Side Converter: Open the Trencada JSON to CSV Converter and paste your JSON array (or drop your `.json` file). All parsing executes locally in your browser’s V8/SpiderMonkey engine—sensitive customer PII, emails, and transaction amounts are never transmitted over the internet.
- Step 3 — Preview the Flattened Table: Inspect the live preview grid to verify that nested objects (`customer.address.city`) have expanded into distinct dot-notation columns.
- Step 4 — Download Excel-Ready `.CSV`: Click Download CSV and open the file directly in Microsoft Excel, Google Sheets, or Power BI.
Frequently Asked Questions (FAQ)
Why does my JSON to CSV conversion show `[object Object]` in Excel?
[object Object] appears when a converter tries to output a nested JSON object (`{“city”: “Austin”, “zip”: “78701”}`) into a single CSV cell without flattening it first. Using a recursive dot-notation flattener like Trencada’s JSON to CSV Converter splits each nested property into its own column (`address.city`, `address.zip`).
How do I convert a JSON object whose root is not an array (`{ “data”: […] }`)?
Many REST APIs wrap the array of records inside a root metadata envelope such as { "status": 200, "results": [ ... ] }. Our converter automatically detects and extracts the primary array of records (or flattens a single root object into a 1-row table) so you don’t have to manually strip the outer wrapper first.
Is it safe to convert production database JSON exports with customer emails online?
Uploading production JSON dumps containing customer names, emails, or Stripe payment metadata to server-side converters violates GDPR and SOC 2 data handling rules. Trencada’s JSON to CSV Converter runs 100% client-side in your browser memory with zero network requests.
How do I stop Excel from removing leading zeros from ZIP codes or IDs in CSV files?
When you double-click a `.csv` file, Excel automatically parses numeric strings like "02138" as the integer 2138. To preserve leading zeros on ZIP codes, SKUs, or 16-digit IDs in Excel, open a blank workbook and go to Data → From Text/CSV, select your CSV file, and set the column data type to Text before clicking Load.
Can I convert YAML configuration files to CSV or JSON as well?
Yes. First convert your `.yaml` file into structured JSON using our client-side YAML to JSON Converter, then pass the output directly into the JSON to CSV tool.








