Tools › Developer › CSV to JSON
CSV to JSON converter
Turn a spreadsheet export into JSON, or JSON back into a spreadsheet, without losing the leading zero on a postcode or breaking on a comma inside a quoted address.
- Quoted commas, embedded newlines and doubled quotes all parse correctly
- Leading zeros stay text, so phone numbers and postcodes survive
- The delimiter is detected and shown, not silently assumed
- Rows that do not match the header are reported, never quietly repaired
- Broken JSON is pointed at a line, not an offset into the file
- Nothing is uploaded, so an export with real customer data in it is safe to convert
Why splitting on commas does not work
The one-line version everybody writes first is line.split(','), and it breaks on
the first real file it meets. A CSV field may be quoted, and a quoted field may contain commas,
line breaks, and quotes of its own escaped by doubling.
So "Smith, John",Leeds is two fields, not three. A record is not the same thing
as a line. An address field with a line break in it spans two. And "" inside a
quoted field is one literal quote, not the end of the field. That is RFC 4180, and it is what
this parses.
The leading zero problem
Turn on number conversion and a cell becomes a number only when the whole cell is a number and reading it back gives the same text. That second clause is the one that matters.
007 parses as 7 and would come back as "7", so it stays text. So do
phone numbers, postcodes, product codes, version strings and anything else with a leading zero,
which is exactly the data spreadsheets are famous for eating. 1.50 stays text for
the same reason: it is a price, and 1.5 is a different string.
Which separator
Not every CSV uses commas. Exports from Excel in much of Europe use semicolons, because the comma is the decimal separator there. Anything pasted from a spreadsheet is usually tabs.
The separator is counted outside quotes, which is the whole difficulty: a file of addresses has more commas inside its quoted fields than semicolons separating them, and counting naively picks the comma and produces a single column. The winner is the character whose count is most consistent from row to row, because a real separator appears the same number of times in every line. Whatever is detected is shown in the control, so you can see what happened and override it.
Rows that do not match
If a row has fewer fields than the header, you are told which line and by how much, and the row is left alone. A short row might be a trailing separator or a genuinely missing value, and guessing which is how a converter silently shifts every column after the gap.
Going the other way, objects with different keys are perfectly legal JSON and make a table with holes in it. Every key found anywhere becomes a column, so a record missing one gets an empty cell rather than a shifted row.
Nested JSON, and the mark for Excel
JSON has structure and a table does not, so nested objects become dotted columns.
{"a":{"b":1}} becomes a column named a.b. Arrays become a single JSON
cell rather than a variable number of columns, because otherwise the column count changes from
row to row and the table stops being one.
The mark for Excel is a byte order mark. Without it Excel reads a UTF-8 file as the local codepage and every accented letter arrives mangled, which is the commonest complaint about any CSV export. It is off by default because it is noise to everything else.