Notes › Tech Notes › Excel to JSON trusts only two numeric regexes: 0012 becomes 12, 1e5 stays a string, and the 8-cell switch matrix lands between 99 and 192 bytes

Excel to JSON trusts only two numeric regexes: 0012 becomes 12, 1e5 stays a string, and the 8-cell switch matrix lands between 99 and 192 bytes

convertValue runs on two numeric regexes and three lowercase comparisons: of 24 samples only 10 leave the string tier, 0012 becomes 12 while 1e5 stays a string. The 8-cell switch matrix, header overwrites, and three stats denominators are measured too.

The decision chain is just two numeric regexes plus three lowercase comparisons

On the tab-separated text to JSON page, every type decision lives inside a dozen or so lines of convertValue. Its toolkit: two numeric regexes and three comparisons against the lowercased value, namely true, false and null; every remaining branch returns a string. The template, read back whole, is 9481 bytes across 223 lines, the script body is 4829 characters, and convertValue itself is a compact block of 412 characters. All three checkboxes are ticked by default, mode defaults to array, and each switch decides whether the chain touches the cell at all.

The tool itself lives on the Excel to JSON page; this page only inspects the characters already sitting in the input box. Measured on 24 neutral samples: with switches on, only 10 leave the string tier, namely three numbers, five booleans and two nulls.

  • Number tier, three hits: 0012 collapses into 12 because the leading zero is gone; -0 lands as a plain 0 on the JSON side; " 42 " is squeezed into 42.
  • Boolean tier, five hits: TRUE, True and true, together with FALSE, all turn into booleans, while null and NULL both turn into null.
  • A string of pure spaces collapses into an empty string while the mapping switch stays on (verbatim in JSON: ""); with type mapping turned off, all 24 samples come back untouched and "0012" keeps its leading zero intact.
  • Still parked in the string tier: 1e5, .5, 5., +7, 0x1F, Infinity, NaN, yes, on, 12,345, 1 234. The two regexes know none of these shapes.

A spaced string that misses every pattern falls through to the fallback and enters JSON as the untrimmed original. Trim is not bolted onto the end of the chain unconditionally; it hangs off a switch. The intro blurb claiming automatic recognition of numbers, booleans and strings measures out as only partly true: numbers match just the two admitted shapes, and the chain additionally recognises null and collapses space-only strings into emptiness, which the copy never mentions.

24 samples enter the chain only 10 leave the string tier decision 0012 becomes 12, -0 lands as 0, TRUE flips bool 1e5 .5 5. +7 0x1F all still strings mapping off: all 24 return as-is

Figure 1 The exit ledger of the decision chain: out of 24 samples only 10 leave the string tier.

The 2×2×2 matrix of three switches: byte totals for eight cells

One sample of 73 bytes and four lines, run through all eight switch combinations, yields json sizes 184, 184, 99, 99, 190, 192, 105, 107 bytes with line counts of 22 against 1. Pretty output is the master switch: throwing it cuts the payload close to half in every row. trim only shows a difference when type mapping is already off, and even then the gap is two bytes; with mapping on, the " 25 " cell is already collapsed into a number by the integer regex, so trim never touches it.

mapping / pretty / trimjson bytesjson lineshtml bytesstats count
on / on / on184229764
on / on / off184229764
on / off / on9918914
on / off / off9918914
off / on / on190229824
off / on / off192229844
off / off / on10518974
off / off / off10718994

Switching modes on the same sample tells a different story: array returns 184 bytes, header 224 bytes, and kv only 72 bytes. In kv each row survives purely as the second column's value, and the key side never passes through convertValue, so the key of '1\tone' stays the string "1". The opposite direction, JSON to Excel, is a separate page; the two directions do not validate each other.

The header ledger: same-name overwrite, empty-string keys, columns dropped without a word

Header mode keeps four quiet ledgers. Duplicated headers: three columns share one name, the later value overwrites the earlier ones, and the object ends with two keys. A blank first cell makes the key name the empty string, verbatim {"":1,"age":2,"city":3}. A row shorter than the header gets empty strings padded into the missing slots. A row longer than the header loses the surplus column: the loop bound is the header length. Neither case produces any notice. One mercy remains: when a header cell carries surrounding spaces and trim is on, cells are trimmed before entering convertValue, so 'h1 ' is repaired into h1.

The three denominators behind the stats line differ from each other

The same stats line is computed three ways. array reports the row count after filtering (the sample prints four). header reports the row count minus one: the sample prints three, and a single header row prints zero with output verbatim []. kv reports the deduplicated key count: a mapping of k to 1, k to 2 and j to 3 spans three rows yet prints two, because the later entry silently overwrites the duplicate key and the value 1 vanishes.

Row splitting and the error path: two throws measured unreachable

Rows are split with /\r?\n/ and then whitespace-only lines are filtered out. The \r of a CRLF pair never reaches a cell; a lone \r does not split anything either, so a line mixing tabs with a bare carriage return counts as one row of three columns, the middle cell keeping the carriage return. Trailing newlines and pure whitespace lines stay out of the statistics: the split hands over three segments while the stats line prints two. The code contains two throw statements, and both measured unreachable. Empty strings, pure spaces, all-Tab lines, a byte order mark (U+FEFF) and the rest of the 12 inputs all fall into the empty-state placeholder branch; errBox display stays none, and the red parse-error frame never lights up. This page's filter order sees to that. Ironically, a "42" padded with invisible trailing spaces is repaired by trim into the number 42.

highlightJSON rebuilds the string: 135 bytes in, 769 bytes out

Highlighting does not slice up the original text and reuse it; it re-splices the string token by token. With every switch on, the highlighted html weighs 976 bytes against 184 bytes of json, and the difference of 792 is exactly 33 spans carrying a 24-byte shell each. Another json of 135 bytes emits 769 bytes of HTML; the gap of 634 equals 26 spans times 24 plus an escaping increment of 10. Escaping rules: ampersands and angle brackets become entities first while double quotes stay untouched; the punctuation regex then runs a second pass over HTML that already contains spans, wrapping a comma inside a string once more. The smallest case, ["12,345"], grows from 10 bytes to 106 bytes inside 4 spans. The conclusion is booked only in byte totals.

json 135 bytes html 769 bytes rebuild +634 26 spans × 24 bytes + escaping 10 smallest case: 10 bytes in, 106 out commas inside strings get wrapped twice

Figure 2 The rebuild ledger: 135 bytes become 769, the increment sitting on span shells and the escaping pass.

The boundary with the earlier article, plus an idempotence ledger

First, draw a boundary. Date serial numbers, the last digits of long numbers and floating point precision are already altered before the moment of pasting; that territory belongs to the earlier article about pasting, and this post neither repeats nor reuses those figures. What is measured here is idempotence. Running parseAndRender three times on the same input yields byte-identical concatenated output (1100 bytes merged), and each render performs exactly one JSON.stringify call. clearInput wipes the data but never the settings. Clicking copy in the empty state shows a nothing-to-copy notice; the count of UU.copy calls stays at zero. The default example is 71 bytes of four lines across three columns, and the first screen parses it into 184 bytes of json with a 129-byte stats line.

Q: Why is 1e5 not converted into a number?

A: The two numeric regexes do not know scientific notation, nor shapes like .5, +7 or thousands separators. A miss falls to the fallback and enters JSON untouched. The boundary of this step is not a question of intelligence; it is a question of which shapes a 412-character chain was written to recognise.

Q: What do you get in exchange for turning the type-mapping switch off?

A: Everything comes back preserved as written: all 24 samples keep their shape, and neither leading zeros nor pure-space strings are touched. Only then does trim begin to matter, and even then the visible effect is two bytes.

Q: Does pasting a duplicate header raise an error?

A: No. The later value silently overwrites the earlier one and the object keeps two keys; a row longer than the header loses its surplus column; a lone header row prints zero with output verbatim []. Both throw statements remain unreachable and the red frame never appears.

📎 Related articles

Is Pasted-Back SQL Byte-Identical After Formatting: 8 String Syntaxes Cross-Checked, 2 Unclosed Backticks Paired Silently 2026-11-20 84 Field Names Across 7 Tabs: 2nd_field Comes in 6 Spellings and 5 Switches Move Only the Java Column 2026-11-20 A YAML alias is a copy: 10 references expand to 697 characters, and on a name clash own beats merged 2026-11-20 3.7 becomes 3 and 1e3 becomes 1: this fraction calculator truncates at the input, then switches display rules at 14 characters 2026-11-14 The cut lands on the last dot only: archive.tar.gz yields gz, photo. and .gitignore share one cell, and the rebuilt column drops a dot 2026-11-14