CSV to JSON

The part of CSV to JSON that goes wrong

Splitting on commas is the easy half, and almost every converter gets it right. The half that quietly ruins data is deciding what the values are. A CSV cell is text and nothing else — the format carries no type information at all, which RFC 4180 is quite clear about. JSON, on the other hand, distinguishes "34" from 34. Something has to bridge that gap, and how it bridges it is the whole ball game.

Most tools bridge it by running every cell through Number() and keeping the result if it looks numeric. That is where account numbers die. Number('8901240544102066246') is 8901240544102066000, because JavaScript numbers are doubles and stop being exact past 2⁵³. The same pass turns the postcode 007 into 7 and the price 1.50 into 1.5. None of it warns you. Every CSV library worth using offers this as "dynamic typing", and most enable it by default.

This page converts a value only when the conversion is reversible — when writing the number back out would give you the exact characters that were in the file. A long identifier, a zero-padded code, a trailing-zero decimal and anything in scientific notation all stay strings, because turning them into numbers would change them. You can switch typing off entirely if you would rather every field arrived as text.

The rest is ordinary careful parsing: quoted fields containing commas and line breaks, "" as an escaped quote, CRLF and bare-CR line endings, and the byte-order mark Excel writes on "CSV UTF-8" export — which, left alone, glues itself to your first column name so id stops matching id.

Converting a file

1. Paste or upload the CSV – Left pane, or Upload for a file from disk. Output appears as you type, so you can fix a stray quote and watch the error clear.
2. Confirm the delimiter – Detect handles commas, semicolons, tabs and pipes by looking at which one divides the rows most consistently. If a file uses a delimiter inside unquoted text the guess can be wrong, so pin it explicitly when the output looks shredded.
3. Say whether the first row is a header – With it on you get an array of objects keyed by column name. With it off you get an array of arrays, which is what you want for a file that starts straight into data.
4. Decide about typed values – On, and 34 becomes a number and true becomes a boolean. Off, and every field is a string. Either way, nothing that would lose a digit gets converted.
5. Copy or download – Output is indented two spaces. Nothing is uploaded — the file is read in this tab, which is the point when it is a customer export.

If your JSON comes back with one enormous key containing the whole line, the delimiter is wrong — a semicolon file read as comma-separated does exactly that. If instead the row count is short, look for an unbalanced quote; one stray " swallows every following line until the next one.

What careful typing looks like

Four columns, four different decisions. Notice that only rsrp comes out as a number — every other value would have changed if it had been converted.

iccid,postcode,price,rsrp
8901240544102066246,007,29.50,-92
8901240544102066247,014,15.00,-104

[
  {
    "iccid": "8901240544102066246",
    "postcode": "007",
    "price": "29.50",
    "rsrp": -92
  },
  {
    "iccid": "8901240544102066247",
    "postcode": "014",
    "price": "15.00",
    "rsrp": -104
  }
]

When you need this

Loading a spreadsheet export into an API

Someone sends a CSV from Excel or Google Sheets and the endpoint wants JSON. This is the most common reason people land here, and it is also where the damage usually happens — the export already lost leading zeros when the spreadsheet opened it, and a careless converter then rounds whatever survived.

Seeding test fixtures

A CSV is the fastest way to write forty rows of test data by hand. Converting it to JSON gives you a fixture file your tests can import, and running it through the formatter afterwards keeps the diff readable.

Getting data out of CSV and into anything else

JSON is the hub on this site. Once you are there you can reach YAML, XML or TOON, or read it as a table. Converting here is usually the first of two steps.

Checking what a delimiter is actually doing

Switching between comma and semicolon and watching the output change is the quickest way to work out how a file from a European system is really structured.

What this converter does carefully

• Reversible typing only. A value becomes a number when writing it back gives the same characters. 8901240544102066246, 007, 1.50, -0 and 1e5 all stay strings, because none of them survive the round trip.
• RFC 4180 quoting. Commas and line breaks inside quoted fields, and "" as a literal quote.
• Four delimiters, detected or pinned. Comma, the European semicolon, tab and pipe.
• The Excel BOM is stripped. Otherwise it becomes part of your first column name and nothing matches it.
• Truncated files are reported. A source that ends inside a quoted field is an error rather than a silently shortened result.
• Ragged rows are kept. A row with fewer cells than the header gets empty strings rather than being dropped, so the row count in equals the row count out.
• Nothing leaves your browser.

Questions people actually ask

Why is my ID a string instead of a number?

Because making it a number would change it. Past 2⁵³ a JavaScript number cannot hold every integer, so an ICCID or a bigint primary key comes back rounded — and two IDs one digit apart can land on the same value. Keeping it as a string is the only way to hand you back what your file actually said. If your consumer needs a real number and the value is small enough to be safe, it already is one.

My postcodes lost their leading zeros.

Not here — 007 stays "007". If they were already gone in the CSV, a spreadsheet ate them before you exported: Excel treats a zero-padded code as a number on open. The fix is upstream, importing the column as Text rather than General.

Can I get an array of arrays instead of objects?

Yes — turn off "First row is a header". You will get one array per row, in file order, which is what you want when the file has no header or when the header is duplicated.

What happens to duplicate or blank column names?

Blank headers get a positional name so no column is silently dropped, and a repeated name is disambiguated rather than overwritten. Two columns both called id would otherwise lose one of them entirely, which is the kind of loss you would not notice until much later.

Is TSV supported?

Yes, pick Tab as the delimiter, or let Detect find it. Tab-separated files are usually the safest thing a spreadsheet can export, precisely because tabs almost never appear inside a value.

How do I go the other way?

JSON to CSV does the reverse. Worth knowing that a spreadsheet will re-round long numbers when it opens the result no matter what the file contains — Excel keeps about 15 significant figures. Importing the column as Text rather than double-clicking the file is the only reliable defence.

Related tools

Worth reading

• RFC 4180 — the CSV specification – Short enough to read in one sitting, and the source of the quoting rules
• RFC 8259 §6 — JSON numbers – Why implementations disagree about big integers, and what the spec suggests instead