XLS to JSON — the old workbook as a machine-readable array
Legacy workbooks often hold data a modern system needs as JSON. The conversion reads the .xls record stream, takes the first row as keys and emits one object per remaining row. Two decisions in that mapping are stated plainly: keys come from whatever the first row contains, and an empty cell becomes null rather than an empty string.
Cell formatting, colours, column widths and merged ranges are dropped; only stored values survive.
Processed on your device. Files aren’t sent anywhere.
Keys from the first row
There is no header concept in a worksheet, so the converter treats row 1 as the key row. If the sheet starts with a title line, that title becomes the keys and the real header becomes the first record. Nothing in the file can tell the converter which line was meant as a header.
Values and blanks
- Numbers are emitted as strings, because the reader reports displayed text.
- A blank cell becomes null, so a missing value is distinguishable from an empty one.
- A cell containing only spaces stays a string of spaces.
- A date cell becomes the displayed date text, not an ISO timestamp.
- A cached formula value becomes the value; the formula text is dropped.
Duplicate and awkward keys
Two columns named Total produce two keys, and the later one overwrites the earlier. The converter does not rename them, because any renaming would invent a convention a reader could not predict. Keys with spaces or accents are kept as-is, since JSON object keys allow them; consumers expecting camelCase need their own transformation step.
Questions
Why do blank cells become null?
Are numbers emitted as JSON numbers?
What if two columns share a name?
Is the formula recalculated?
Sheet choice and record count
| Field | Meaning |
|---|---|
| sheets | worksheets read from the workbook |
| rows | rows read from the selected sheet, including the key row |
| objects | records emitted, rows minus one |
Only one sheet becomes objects
The JSON output is a flat array, so it comes from one worksheet. A multi-sheet workbook needs a deliberate per-sheet conversion rather than an automatic merge.
Verifying the array
Parse the JSON and check three things: the number of objects, whether the keys look like the real header, and whether a column you expected to be numeric is a string. Printing the first object and comparing it with the visible sheet catches most problems.
Limits
- Convert the .xls file and download the JSON.
- Validate it with a parser before feeding a pipeline.
- Confirm the keys came from the intended header row.
- Convert numeric strings to numbers in the consumer.
A legacy workbook is a container, not a contract. Its encoding, cached values and header conventions all belong to the application that wrote it.
Honesty note for legacy sources