JSON to Excel / CSV

JSON to CSV: How Headers Are Decided, How Fields Are Escaped, What Happens to Nested Objects

The conversion rule: the header is the union of all keys

The tool first walks every object in the array and collects every key that appears, using first-occurrence order as the column order. This detail is important:

• If only the first object's keys were taken, then a field absent from the first item but present later would be dropped from the entire column.
• Taking the union guarantees every field is preserved, at the cost of some rows having empty cells (that row lacks the key).

So when objects disagree on their fields, no data is lost, but empty cells are expected behavior, not a bug. If you need strictly aligned output, normalize the data first so every object carries every field.

The escaping rule (RFC 4180)

CSV looks like merely comma-separated text, but it has a precise escaping rule. The tool follows the standard:

The cost of not following this rule is severe: a field containing a comma shifts the column count of the entire row, and every following column is misaligned; a field containing a quote that is not doubled makes the parser close the quote early and swallow what follows. Hand-concatenating CSV almost inevitably hits these two cases, which is exactly why this tool exists.

The limits on nested objects and arrays

This is the one point to know in advance: CSV is only two-dimensional, so nesting cannot be expressed directly.

The enhanced English and Chinese exporter serializes nested objects and arrays as JSON text in one cell. It does not flatten nested paths into columns. Paste JSON or import a local UTF-8 JSON file (up to 2 MiB), select Read fields, choose a records array and its columns, then convert and download. Pasted input has the same size limit; up to 20,000 records and 500 fields are supported. Importing replaces input only after confirmation and successful validation.

There are three ways to handle nested fields:

1. Flatten first. Turn {"user":{"name":"Bob"}} into {"user.name":"Bob"}, joining names with a dot. jq's flatten or similar tooling can do this.
2. Serialize into a cell. Drop the nested value in as a JSON string inside one cell. It stays reversible but unreadable, and you lose the ability to sort or filter it in Excel.
3. Split into tables. Export the parent and child as two separate tables joined by a foreign key. This is the most relational-model-friendly answer and suits importing into a database.

Which to pick depends on the downstream use: human viewing and pivoting → flatten; programmatic restoration → serialize; database import → split tables.

Choosing a delimiter and avoiding Chinese garbling

The tool supports four delimiters — comma, semicolon, tab, and pipe:

• Comma. The default and the most broadly compatible choice.
• Semicolon. Many European locales configure Excel to use the semicolon as the list separator (because the comma is the decimal point). If your users are in Europe, producing the semicolon version avoids the "everything crammed into one column" problem.
• Tab. This is actually TSV; pasting it into Excel or a spreadsheet needs no import wizard and splits into columns directly — the least fussy way to "quickly paste into a sheet".
• Pipe. An alternative when the content itself is full of commas.

On Chinese garbling: a CSV file carries no encoding information of its own. When you double-click it, Excel on Windows reads a UTF-8 file using the system default code page (GBK under Simplified Chinese), and the Chinese shows up as garbage. Three fixes:

1. Do not double-click; use Excel's "Data → From Text/CSV" import and explicitly choose UTF-8 in the wizard. This is the most reliable approach.
2. Have the file carry a BOM (byte-order mark EF BB BF); Excel sees the BOM and reads UTF-8 correctly. The cost is that some other tools treat the BOM as part of the data, leaving an invisible character on the first column name.
3. After exporting, re-save from a text editor as UTF-8 with BOM.

How to use this tool

1. Paste an array of JSON objects or an API response containing one. If several record arrays are found, select the one to export.
2. Pick a delimiter and click convert.
3. Use the copy button to take the result.

Every row must be an object. Nulls, scalar rows and nested array rows are rejected rather than silently skipped. After editing input, read fields again to select columns. Download produces UTF-8 with BOM; copying produces plain text. Formula-like cells are prefixed by default; this changes the text, so inspect the output before using it. All processing is local.