Convert a CSV file to JSON

What this does

Turns a spreadsheet export into JSON without it leaving your computer. Most online converters upload the file to a server, which means a customer list, a payroll export or a set of medical records has just been sent to somebody else’s machine. This one reads the file in the tab you are already looking at.

The method

The interesting part is the quoting, and it is the reason this is a parser rather than a split(','). All four of the following are ordinary in a real export, and all four break a naive split:

Name,Role
"Lovelace, Ada",Mathematician
"She said ""hello""",Quoted
"12 High Street
London",Multi-line

The parser walks the text one character at a time and keeps one piece of state: whether it is currently inside quotes. A separator seen inside quotes is part of the value. A line break inside quotes is part of the value. Two quotes in a row inside a quoted field are one literal quote. Everything else follows from that.

The separator itself is detected by counting candidates in the first complete record — outside quotes, for the same reason — because a semicolon-separated European export split on commas produces twice as many columns as it has, every one of them wrong, with no error to tell you.

Before you read on

A CSV export is 500 lines of text. Can it hold fewer than 500 records?

• ANo — one line is one record. That is what CSV means.

  It is what most code assumes, and it is why so much of that code breaks on a real export.

• BYes — a line break inside a quoted field belongs to the value.

  Yes. An address across two lines is one field on one record spanning two lines of the file.

• COnly if some of the lines are blank.

  That too, but it is the smaller problem, and it does not need quotes to happen.

Yes. A quoted field may contain a line break, and that break is part of the value rather than the end of a record — an address written across two lines is the everyday case. It may also contain the separator, and two quotes in a row inside it stand for one literal quote. All three are ordinary in a real export, and all three are fatal to a split on commas, which is the entire reason this is a parser rather than one line of string handling.

1. inside = false

   Whether the walk is currently between quotes. That single flag is the whole state machine.

2. on a quote: inside = !inside

   Unless the next character is also a quote, in which case the pair is one literal quote in the value and the flag does not move.

3. on a separator: inside ? keep it : end the field

   A comma inside quotes is part of the value. Lovelace, Ada is one field, not two.

4. on a line break: inside ? keep it : end the record

   Which is how a 500-line file holds fewer than 500 records.

A worked example

Four rows of a savings schedule:

Year,Paid in,Interest,Balance
1,3000,152.34,3152.34
2,6000,468.91,6468.91
3,9000,955.02,9955.02
4,12000,1620.77,13620.77

converts to four rows and four columns, the first of which is:

{ "Year": "1", "Paid in": "3000", "Interest": "152.34", "Balance": "3152.34" }

Those exact figures are asserted in calc.test.ts, so this page and the code cannot drift apart about what the tool does.

It also leaves the rows exactly as it found them. Use the CSV column selector first when the JSON should contain only a named subset or a different column order. To sort lines or drop duplicates first, do that before converting rather than after — JSON is the harder shape to tidy. For the reverse direction, JSON to CSV has to decide which keys form the complete header and what nested objects mean before it can write the first row.