Convert CSV to NDJSON

A CSV names its columns once, at the top, and every row after that is positional — the third field is the city because the third heading said so. NDJSON does the opposite: each line is a complete object carrying its own keys, so the seventy-thousandth record is as intelligible on its own as the first.

That is the trade the conversion makes, and it is not free. Repeating the key names on every line makes the file larger, sometimes considerably so on a table of short values with long headings. What you buy is that no line depends on any other line, which is what makes every other property on this page possible.

The reason this conversion is usually asked for is not elegance. It is that you have eleven monthly exports, the system added a field in April, and there is no way to concatenate a file with fourteen columns and a file with fifteen. Joining the CSVs leaves a header row sitting in the middle of the data, and even after removing it the fields no longer line up.

NDJSON has no such problem. Records with different key sets sit in the same file quite legitimately, and every reasonable consumer — a query engine, a load job, a script — treats a missing key as absent rather than as a broken row. Convert each export separately, concatenate the results with `cat`, and the schema difference becomes a fact about individual records instead of a structural failure.

Because a record ends where the line ends, the file can be cut anywhere a newline occurs and both halves stay valid. `split -l 100000` produces loadable chunks. `head -n 1000` produces a sample you can actually run against a schema. A failed load can be resumed from a line number instead of started again.

Appending works for the same reason: new records go on the end with `>>` and nothing above them has to be rewritten. Doing the equivalent to a CSV means checking whether the header is present, whether the column order matches and whether the last line ended with a newline, and getting any of those wrong corrupts the file silently rather than loudly.

JSON distinguishes the number 7 from the string "7" and a CSV does not, so something has to decide, and what decides is inference over the text. Values that parse as numbers become JSON numbers — `1e5` arrives in the output as 100000 — while `true` and `false` become booleans and everything else stays a string.

The guess is right for quantities, prices and flags and wrong for identifiers. `007` becomes `7`, and once it is a number in the JSON there is nothing left to recover it from. If the export contains codes rather than measurements, the cheapest defence is to make them unambiguous in the source: a prefix, or a column already exported as text, both survive. The general test is whether adding two values in the column would mean anything; if not, it is an identifier and it wanted to stay a string.

A blank cell is written as `null` rather than as an empty string. That is a real decision and worth knowing, because the two are not the same to anything that consumes the file: a JSON schema with `"type": "string"` rejects null, and a database load will put a null where you may have expected the empty string you saw in the source.

What the conversion cannot do is tell you which one the export meant. A CSV has exactly one way to write "nothing here" and it is used for both "this field is blank" and "this field does not apply". If the distinction matters downstream, it has to be encoded in the source explicitly — a sentinel value, or a second column — before any conversion sees it.

The file is designed for `jq -c`, which reads it a value at a time without holding the whole thing in memory. `jq -r ".city"` over a converted export prints one city per line; `jq -s "length"` counts records; a `select` filter produces a smaller NDJSON file that is itself still valid input to everything else.

The same applies to anything that reads lines. `grep` on the raw file works and is honest, because a record cannot span two lines and cannot be broken across a match. `wc -l` gives the record count exactly, which is a small thing until you compare it to counting rows in a CSV where a quoted address contains a newline.

Expect growth. Every line repeats the key names, every string is quoted, and the punctuation of an object is added around the values. On a table of short codes with descriptive column headings the file can double; on a table of long free-text fields the difference is slight, because the values dominate.

Compression closes most of the gap, since the repeated keys are exactly what a compressor is best at, and a gzipped NDJSON file is usually close to a gzipped CSV of the same data. If the size at rest is what actually matters rather than the streaming shape, that is a signal to look at Parquet instead, where the column names are stored once in a footer rather than on every record.

NDJSON is a container shape, not a contract. A destination that ingests JSON lines will still have opinions about field names, about date formats, and about whether a null is permitted in a given position, and none of those are decided by this conversion.

The two that catch people out are dates and identifiers. A date in a CSV is text and stays text, so whatever string the exporting system chose is what arrives — and if that string is `03/04/2024` the ambiguity travels with it. Identifiers, as above, may have become numbers. Both are cheap to fix in a one-line `jq` pass over the converted file, and much more expensive to fix after the load.

Reading the CSV and writing the JSON lines are both plain JavaScript, loaded by this page on demand. No request carries the file, so a nightly customer export or a finance extract can be converted without it becoming somebody else’s copy.

The free tier stops at 100 MB a file, and under that the ceiling is memory. The whole table is built before anything is written, which puts tens of megabytes comfortably in range and the last stretch to 100 MB at the point where a browser tab starts to strain. For a larger export, a streaming CSV reader in a script is the right tool — and given that the destination is a stream, writing that script is often the correct end state anyway.

Choose a plain JSON array instead when the whole thing is going to be loaded at once by something that expects an array — an API payload, a test fixture, a configuration file. NDJSON is worse than useless there, because most JSON parsers will reject it outright on the second line.

Keep the CSV if the destination is a spreadsheet or an import screen that names it, since neither gains anything from records on lines. And choose Parquet when the same data will be queried repeatedly rather than ingested once: it stores the column names once, keeps the types explicitly instead of by inference, and is a fraction of the size on disk.