Convert XLSX to JSON

This conversion exists because of a pattern that repeats in every organisation: the authoritative version of something — a price list, a product catalogue, a translation table, a set of opening hours, a mapping of codes to names — lives in a spreadsheet maintained by somebody who is not a developer, and it has to reach a program that expects data.

That is a perfectly reasonable arrangement. A spreadsheet is a good editing interface, business users already know it, and building a small admin tool to replace it is rarely worth the effort. What it needs is a reliable conversion at the boundary, and that is the whole job here.

The mapping is the obvious one. The first row is treated as the header and supplies the key names; every row after it becomes one object; every cell becomes a value on that object. The result is an array of records, which is the shape a consuming program almost always wants.

It is worth thinking about the header row deliberately, because those cells become key names in your code. Short, lowercase, no spaces and no punctuation makes for keys that are pleasant to work with; a header that reads "Unit price (£, ex VAT)" produces something nobody wants to type twice.

A spreadsheet does not store dates as dates. It stores a number counting days from an origin, and the cell formatting is what makes 45678 appear as a date on screen. Converting hands back what is actually in the file, which is the number.

The two ways round it are both deliberate. Format the column as text in the spreadsheet and type ISO dates into it, which makes the file self-describing and survives every conversion. Or transform the values after conversion, applying the day-count arithmetic yourself. Either is fine; discovering the problem after the data reaches production is not.

A postcode, a product code, a bank sort code or a national identifier stored in a cell formatted as a number has already lost its leading zeros — Excel did that when the value was typed or imported, and the file contains a number.

The same applies to identifiers past fifteen digits, which lose their last digits to floating point in the spreadsheet itself. No converter can restore either, because the information is not in the file. The fix is upstream: those columns must be formatted as text in the spreadsheet, and it is worth telling whoever maintains it once rather than repairing the output every month.

Formulas, and you get their results — which is what you wanted, since a program has nothing to do with a formula. Formatting, colours, conditional rules and column widths, none of which is data. Charts, comments and merged cells, which have no equivalent.

Merged cells are the one worth watching. A merged header spanning three columns is a presentational device, and it produces an object with one key and two empty ones — which is a strange-looking record that came from a perfectly sensible-looking sheet. Flat headers, one cell per column, are what make this conversion boring, and boring is the goal.

The common arrangement is to keep the spreadsheet as the editable source, convert to JSON as a step, and commit the JSON alongside the code. The data is then reviewable in a pull request, versioned, and available at build time without any runtime dependency on a file somebody might move.

Two habits make that work over time. Convert the same way every time so a diff shows real changes rather than reordering. And validate the result against a schema before it is committed, so a column renamed by a well-meaning colleague fails a check rather than silently producing objects with a missing field.

If the destination is a database import, an analysis tool or a data pipeline, CSV is usually the better intermediate: it streams, every system reads it, and it does not impose a nesting structure on data that has none.

JSON earns its place when the data is going into application code — a front end, a configuration file, an API fixture — where an array of objects is what the language wants and the parse is one line. Choose by what reads the file rather than by which format is more modern.

The conversion runs in the browser tab on your own processor, so nothing is uploaded and no server holds a copy. There is no account, no queue and no daily allowance; the size limit is 100 MB a file, a hundred files to a drop.

That is not incidental here. The spreadsheets people convert are internal: prices, margins, customer lists, staff records, unreleased catalogues. Not sending them anywhere is the simplest form of not having to trust anybody with them.