Convert XLSX to NDJSON

A JSON array is a single value. To read the last object in it you have to have parsed everything before it, which means holding the whole document in memory or reaching for a streaming parser that understands partial structures. For a configuration file that is irrelevant. For four million rows it is the difference between a working import and a process the operating system kills.

Newline-delimited JSON removes the problem by removing the container. Each line is a complete, independent JSON value, so a consumer reads a line, does something with it, discards it and moves on with a constant memory footprint. That single property is why bulk loaders, log shippers and warehouse ingest jobs all converged on the same shape. It is also why a failed load is recoverable. A stream that fails on record eight hundred thousand can be resumed from that line, where a document that fails to parse gives you nothing at all and no indication of which record was responsible.

The guarantee only holds if nothing in the data can produce a newline of its own, and this conversion enforces that. A cell containing a line break — a multi-line address, a comment somebody typed with Alt+Enter — is written with the break escaped inside the JSON string, so the record stays on one line. A tab inside a cell is escaped the same way.

That is the concrete advantage over a delimited file. A tab-separated export of the same sheet has to wrap an awkward cell in quotes and hope the reader implements the convention; here there is nothing to implement. Splitting the file into chunks of ten thousand lines is safe, counting lines gives you the record count, and no row can be corrupted by the row above it. The comparison is concrete: the same cell containing a tab is written here as an escape inside a quoted JSON string, and in a tab-separated export as a field wrapped in double quotes that shell tools do not understand. One of those is handled by every JSON parser ever written and the other is a support ticket.

A delimited file has one type: text. Whatever reads it has to guess, and guessing is where identifiers become integers and version numbers become decimals. JSON carries the distinction explicitly, so a quantity is written as 12, a flag as true, a missing value as null, and a product code that was text in the workbook stays quoted with its leading zeros intact.

For a load into a typed destination that is worth real time. The schema you declare and the values you supply agree without a coercion layer in between, and an ingest job that rejects a row tells you which field disagreed rather than reporting a parse error on a line number. The types come from the workbook rather than from inference over text, which is the important part. A cell that Excel held as a number is written as a number here because it was one, not because a parser looked at the characters and decided they resembled one.

Every line repeats every key. Fifty thousand rows of six columns means fifty thousand copies of the six field names, which on a real sheet came out at about 4.4 MB against 1.8 MB for the same data as tab-separated text — roughly two and a half times, from an .xlsx of about 4.3 MB.

That trade is usually correct for this destination, because the file is going to be read once by a machine rather than stored forever. If the same records are going to be queried repeatedly, the shape to reach for is columnar rather than line-oriented, and the same sheet lands at a small fraction of the size. NDJSON is a transport format, not a storage format. In transit the size mostly does not matter either, because the file compresses extremely well: the repeated keys are the most compressible thing in it, and gzip on the way to an object store removes almost all of the overhead this section describes.

The tools follow from the shape. `wc -l` gives you the row count exactly, because there is one record per line and a trailing newline. `head -1` shows you the field names as they actually were written rather than as you remember them. `split -l 10000` produces chunks that are each valid on their own.

For anything more than that, jq reads a stream of values rather than one document, so `jq -c "select(.qty > 100)"` filters a file larger than memory and emits the same format it consumed. That composability is the reason to convert into this shape rather than into an array you would have to take apart again. It is also the cheapest way to inspect a spreadsheet somebody has sent you. One command tells you the field names, another tells you the row count, and a third shows you every distinct value in a column — none of which requires opening the workbook or trusting what its first screen appears to say.

Not every API that says newline-delimited JSON means only that. Elasticsearch and OpenSearch bulk requests interleave an instruction line before each document, so the body is twice as many lines as there are records and this output is the raw material rather than the payload. Generating the action lines is a few lines of script over the file.

Loaders that take the format as-is do exist and are the common case: a warehouse load job pointed at newline-delimited JSON, a queue producer reading a line per message, an application’s own import routine. Check the destination’s documentation for whether it wants records or a request body, because the two look similar enough to waste an afternoon. The distinguishing question is whether the format description mentions an action or metadata line. If it does, you are being asked for a protocol and this file is the payload half of it; if it only describes the records, this output goes in as it is.

Dates in a spreadsheet are day counts wearing a display format, and the conversion writes the value rather than the appearance: 1 January 2024 becomes 45292, and a timestamp becomes that number with a fraction for the time. The count starts at the end of December 1899.

This is the failure mode worth watching on this pair specifically, because JSON numbers are legitimate and a loader with a numeric column will take 45292 happily and tell you nothing. Convert those fields deliberately — in a jq step over the file, or in the target after loading — and make it part of the pipeline rather than something you notice in a dashboard six weeks later.

An empty cell is written as null rather than omitted, so every object carries the same keys and a consumer expecting a fixed shape gets one. Where later rows introduce a field the earlier ones lacked, the key set is the union across the sheet, so nothing is dropped for appearing late.

Only the first sheet of the workbook is converted, because a stream of records has no way to express "and now a different table". If the sheet you need is not first, reorder the workbook and convert again; if you need several, convert several times and load them as separate streams, which is what the destination will want in any case.

Everything happens in your browser. The workbook is parsed and the lines are written locally, with no upload and no queue — which for an export of customer records, orders or anything under a data protection policy is the difference between a tool you can use at work and one you cannot.

The ceiling is 100 MB per workbook and 100 files in one drop, the same for everybody and not a tier to be paid past. Well before that figure, memory is what you will feel, because the sheet is read into an array before it is written out: tens of megabytes is unremarkable, and a workbook close to the cap is where a browser tab starts to labour. Beyond that the right instrument is a streaming reader in a script, and it is more useful to say so than to fail three-quarters of the way through a very large file.

If the consumer is a program that will read the whole file and hand it to something else — a fixture, a seed script, a request body — a JSON array is the shape it expects, and NDJSON only adds an assembly step. Convert to JSON in that case.

If the records are going to be queried repeatedly rather than loaded once, convert to Parquet: same rows, a fraction of the bytes, and a query that touches two fields reads two columns instead of every line. NDJSON is the right answer while data is moving, and a poor one once it has arrived.