Three symptoms, one rule each
An API returns a block of JSON, you drop it into a spreadsheet for a pivot, and three things usually hit first: one cell shows [object Object]; a column you were sure existed is missing entirely; and Chinese text opens as garbage. They look like three bugs but are three unrelated rules: how keys are collected, how an object is turned into a string, and at which step the encoding mark is attached.
The sample has three records: the first has id and name, the second switches to addr and tags, the third has only name with a null value, and the input is 92 characters in total. With default options the header is id,name,addr,tags, the stats read rows: 3 | columns: 4, and the whole text contains 4 CRLF pairs.
The header is the union of every key, but it only looks one level up
Field collection walks each record's keys and deduplicates, so the header is the union of all keys in first-seen order. A field that appears in only some records does not shrink the column set: it still enters the header and missing cells are filled with an empty string. The third record's name is null, normalised to an empty string, so that row becomes ,,,. The real cause of a missing column is different: with flattening off, a nested object counts as one key in one level, and its child keys never reach the header.
Turn flattening off and a cell becomes one String call
With flattening unchecked, a non-scalar value goes straight to String(): the addr cell comes from String({city:"北京"}) and gives [object Object]; the tags cell comes from String(["a","b"]) and gives a,b, with brackets and quotes gone and the delimiter embedded, so auto wraps the whole cell. Checking flattening takes the other branch: objects are split level by level into dotted paths, arrays are written as real JSON strings, and tags becomes "[""a"",""b""]".
| Position | Flattening off | Flattening on |
|---|---|---|
| Header row | id,name,addr,tags | id,name,addr.city,tags |
| Data row 1 | 1,"张三,Co.""A""",, | same as left |
| Data row 2 | 2,,[object Object],"a,b" | 2,,北京,"[""a"",""b""]" |
| Data row 3 | ,,, | ,,, |
In the sample addr has only one child key, so both modes give 4 columns and differ only in column names and values. Column counts really diverge when there are more child keys: [{"a":{"x":1,"y":2}}] gives one column a with the value [object Object] when off, and two columns a.x and a.y when on; the missing column is lost at exactly this point. One level deeper is the same: {"a":{"b":{"c":1}}} is a with [object Object] when off, and a.b.c with 1 when on. A top-level object rather than an array is wrapped as a single record, producing 1 row.
Figure 1 | 69 characters is 73 bytes in UTF-8, 76 bytes when downloaded with a BOM, and copying is always 73 bytes. Bars are scaled by bytes.
Quotes have three modes, and the extra fields come from never quoting
The page describes quoting as whether to add quotes to every field, while the code has three modes, auto, always and never, defaulting to auto. auto builds a character class from the quote, the current delimiter and CR, LF, adds quotes only on a hit, and doubles inner quotes; always quotes everything, writing even an empty field as ""; never returns the raw value without any escaping. So row 1 under never becomes 1,张三,Co."A",,, which splits into 5 fields against a 4-column header, so the preview shows that row as 5 cells, and row 2 is 1 over as well. The delimiter has three choices, comma, semicolon and tab; switching only changes the joining character, and line endings stay CRLF.
Bytes have three accounts, and the BOM only appears in one of them
The same CSV is 69 characters (counting UTF-16 code units), 73 bytes in UTF-8, and 76 bytes when downloaded with the BOM. The extra 4 bytes sit in those two characters: each Chinese character is 3 bytes, and E5 BC A0 is the first one. The BOM is the 3 bytes EF BB BF, attached only at the download step, and the content itself never contains it: download 76 bytes, copy 73 bytes, even though the checkbox is on by default.
Line endings are CRLF, 4 of them: 1 for the header plus 1 for each of the 3 data rows. The end is also CRLF, so after splitting the last segment is an empty string, which is dropped and the preview shows 4 rows. Windows recognises CRLF as a line break and RFC 4180 requires it; counting LF on Unix also gives 4, but the residual CR at the end of each segment travels into the last field.
At 1000 rows the same cell matters a thousand times
Scaled to 1000 records by 6 fields, default options and flattening off: stats rows: 1000 | columns: 6, 39316 characters, 43316 bytes in UTF-8, 43319 with the BOM, 1001 CRLF pairs. The addr column is [object Object] on every row, 1000 times, and the column has no content from top to bottom. With flattening on the fields become id,name,age,addr.city,tags,active, still 6 columns, characters drop to 35321 and bytes rise to 43321: that cell goes from pure ASCII to a Chinese city name, so the two accounts no longer move together. To see which levels carry child keys first, JSON tree view is more direct than staring at the CSV.
Figure 2 | Flattening off: 39316 characters against 43316 bytes, 43319 with the BOM; flattening on gives fewer characters and more bytes, and [object Object] fills 1000 cells.
The filename has nothing to do with any of this: data_1700000000000.csv is the fixed-timestamp form, 22 characters in total: 5 for the prefix, 13 for the millisecond timestamp, 4 for the extension. I checked all of these rules on JSON to CSV with the same input, one by one, and the difference between checked and unchecked is exactly those two column values.
The error sentence comes from the engine, not from the tool
- A top-level scalar throws a fixed sentence,
data is not an array or object, and the message is identical word for word for all four inputs 123, true, null and "abc". - Syntax errors show the engine's own text: for
{a:, Node v24 and Chrome 154 were identical in testing,Expected property name or '}' in JSON at position 1 (line 1 column 2), and the interface writes it in verbatim, so changing browsers changes the wording. - After the input is cleared the stats read rows: 0 | columns: 0, and clicking download asks for valid JSON data first.
Q: Why does one row have more fields than the table has columns?
Because quoting was set to never. Any value containing the current delimiter splits into an extra piece: the sample row splits into 5 fields against a 4-column header. Switch back to auto or always and it lines up again.
Q: Should the BOM be on or off?
It depends on the exit path. Turning it on only affects the download: the file starts with the 3 bytes EF BB BF, so this example goes from 73 to 76; the copy is always the 73-byte version. So do not count on that checkbox when pasting into an open window; save the file and double-click it and the default is preserved. The import JSON into Excel note covers the CSV garbling traps separately.
Q: How are objects inside an array handled?
Neither mode produces extra rows. With flattening off the array goes through String and gives a,b with brackets and quotes stripped; with it on, JSON stringification gives ["a","b"] and auto doubles the inner quotes. An object is [object Object] when off, and split into dotted paths when on.
Q: Do I need to check each record's key set before exporting?
At the top level, no; nested levels deserve a look. Top-level keys are unioned by the code and missing ones are filled with an empty string; what needs confirming is keys below the second level, because only flattening turns them into columns.