16 Keys In, 4 Columns Out: How json-to-excel Builds CSV Headers

Three numbers that never line up

Paste one block of JSON and three counters start arguing: the column count in the status line, the size of the downloaded file, and the size of the clipboard text. Do extra keys reach the header? Why is the download bigger than the copy by exactly 25 bytes here? Why does a record carrying 16 keys come back with 4 columns? I lifted generateCSV, csvCell and copyTable out of the tool template word for word and drove them under Node and headless Chrome.

The header is the union of every key in every record

Lines 138–144 walk all records, collect every key they meet, and append a name only the first time it shows up; nothing about the first record is privileged. Feed in [{"a":1,"b":2},{"a":3,"c":4}] and the result is 3 columns, allFields=["a","b","c"], and a csv of 19 bytes. Take only the first record's keys and the same input yields 2 columns, because c disappears as a whole column. Records with fully disjoint keys are the extreme case: 50 of them produce 50 columns.

The sample used here holds 3 records whose keys union to 24 names, and the count stays at 24 whether flattening is ticked or not: addr splits into two keys and adds one column, while empty:{} yields no leaf and takes one away. A cell whose record lacks the key is handled on lines 159 and 200 — the preview renders an empty <td></td>, the csv writes an empty string between two commas. The column itself is never dropped.

Figure 1: the union count against the two ways of miscounting it.

Dots build the path, arrays are never indexed

Line 113 hardcodes prefix + '.' + key, so the separator is a dot and nothing else: {"u":{"id":{"v":1}}} flattens to u.id.v. A key repeating through N levels costs 2N − 1 characters, so 1000 levels measure out as a 1999 character name. Arrays never take that branch: lines 116–117 hand the whole array to JSON.stringify, so the key of {"a":[1,2,3]} stays a and its value comes back as "[1,2,3]", quoted because the joined content carries commas. No index column such as a.0 is ever produced.

The recursion has no depth parameter, so the ceiling belongs to the JavaScript stack rather than to any setting: 1000 levels and 3000 levels both render a table, while 6000 levels fail every run with RangeError: Maximum call stack size exceeded. The same payload nested 100,000 levels deep still parses through JSON.parse, because parsing is not what gives out. What overflows is the template's own recursion, yet the red box still titles itself JSON 解析错误 and sends the reader hunting for a bad comma.

Four characters are the entire quoting rule

Line 208 tests one character class and nothing more: /[",\r\n]/, meaning double quote, comma, carriage return or line feed. Hit it and the cell gets a quote on each end; a double quote inside the value is doubled per line 209, so a " costing 1 byte leaves as 4 bytes. Miss it and the value is written raw. Headers go through the same csvCell on line 195, which is why the key a,b opens the file as "a,b" rather than splitting the header row.

Everything outside those four characters passes through untouched, and the list is longer than most people expect: a value padded with spaces such as 前置空格 , a value full of semicolons such as 甲;乙;丙, a value carrying a tab such as 甲\t乙, and the four formula-shaped prefixes =1+1, +86138, -123 and @mention all leave the page unquoted.

Three exit paths, three byte counts

The byte order mark is not part of generateCSV: line 193 starts from an empty string, and the mark appears only on line 219, inside new Blob(['\uFEFF' + csv]). That is why the download equals the csv size plus 3 bytes across all 12 runs I measured, without exception. Line endings differ as well: the csv joins rows with CRLF, from the two + '\r\n' on lines 195 and 201, while the clipboard joins cells with a tab and rows with a bare LF (lines 241 and 246) and never calls csvCell. Line 248 then runs text.trim(), shaving whitespace off both outer ends.

All three figures come from the same sample: 3 records, 24 columns, header on, flattening off. The 25 byte gap splits into 3 bytes of BOM, 4 row terminating carriage returns and 18 quote characters, minus whatever trim() shaved off the tail.

The cost of missing escapes shows up in a mixed sample. [{"a":"x","b":"甲\t乙","c":"d"},{"a":"p\nq","b":"r","c":"s"}] yields a csv of 31 bytes where every row keeps its column count, because the quotes hold the tab and the newline inside one cell. The clipboard copy of the same data puts 2,3,0,2 tabs on its lines and spreads over 4 physical lines. Padding is lost just as quietly: [{"a":" x "}] keeps both spaces in a 7 byte csv with the header switch off, while the copied text collapses to 1 byte. Before I take the copy route in JSON to Excel, I check that no value carries a tab or a newline.

Figure 2: one input, three outputs that are not interchangeable.

16 keys in, 4 columns out

Lines 139 and 142 use a plain {} as the deduplication set, so every lookup walks the prototype chain. A key that also names a member of Object.prototype reads as truthy before anything has been written into it, counts as already seen, and never enters fields. Measured on one record, 12 of those names get swallowed, among them constructor, toString, valueOf and __proto__. Put them in one record alongside four ordinary keys such as length, name, value and x and the arithmetic is blunt: 16 keys go in, 列: 4 comes back.

Once the union has been eaten down to nothing, line 191 degrades into taking the first record's keys only. Input [{"constructor":1},{"toString":2},{"valueOf":3}]: the preview shows 0 columns, yet the csv runs to 90 bytes and contains function Object() { [native code] } twice, 33 characters each time, printed by String(v) on line 200. Line 111 assumes hasOwnProperty is always present, so [{"hasOwnProperty":1,"a":2}] with flattening ticked throws obj.hasOwnProperty is not a function.

What a value becomes while flattening stays off

The checkbox on line 21, j2e-flatten, is off by default, so line 134 never runs and a nested value is stringified by String(v) into the 15 characters of [object Object]. Both cells of the addr column hold exactly that, which means Beijing and the value 100,000 are nowhere in the export; an array turns into quoted text such as "1,2".

Empty objects are subtler, because they look like data rather than like an error. {"a":{},"b":1} with flattening on takes two keys in and gives one column back: the empty object produces no leaf key, so the whole column disappears. A null only blanks the value and keeps its column. In the csv there is no way to tell an absent key, a null and an empty string "" apart, since all three render as a,b\r\n1,\r\n, identical byte for byte.

What this page produces is a CSV

The first 8 bytes of the download measure ef bb bf 6e 61 6d 65 2c: a BOM and then name, straight after it. The ZIP signature 50 4b 03 04 and the OLE signature d0 cf 11 e0 both fail to match, and the blob type is the 23 character string text/csv;charset=utf-8;. The deliverable is a text file, not a workbook, and reading it back into a spreadsheet is the separate problem the import JSON into Excel note works through.

escHtml marks the line between preview and export. Lines 176–178 touch only &, <, > and the double quote, four characters, and it is called from the <th> on line 151 and the <td> on line 159 and nowhere else: the export path calls it 0 times. It can distort what you look at but never what you download, just as text.trim() only distorts the clipboard text. Neither one accounts for a byte of the file size.

Questions

Q: Why are there fewer columns than the keys in my records?

Two silent mechanisms. Either a key name collides with one of the 12 members of Object.prototype and its column is eaten during deduplication, or the value is an empty object {}, which yields no leaf once flattening runs.

Q: Why is the download 25 bytes bigger than the copy?

Because of 3 bytes of BOM, 4 terminating carriage returns and 18 quote characters, less whatever trim() shaved off the tail. The rule that always holds is the simple one: download size equals csv size plus 3 bytes.

Q: Can I get it to quote semicolons or values that start with = as well?

You cannot. The character class on line 208 has no switch, no setting widens it, and the page does not escape every field.