CSV to JSON and back, with a parser that reads quotes properly

Because a comma inside quotes is data, not a separator. Take the line 1,"Chan, Wei Ming",Ipoh — split it on commas and you get four pieces instead of three, and every field after the name lands one column to the left. Nothing errors; you just get a table where the city column holds surnames. Paste that line above with the header row switched off and you will get three fields, the second of which is Chan, Wei Ming. The case that breaks line-splitting entirely is a line break inside a quoted cell — an address written over two lines, which any spreadsheet will produce — because the record is then spread over two physical lines of the file. This parser walks the text one character at a time and tracks whether it is currently inside quotes, which is exactly what RFC 4180 describes.

Yes, and it is very common. Excel writes whichever character your locale calls the list separator, and in most of continental Europe the comma is the decimal separator — 1,5 is a number there — so the separator becomes a semicolon. Tab-separated files come out of database exports, and pipe-separated ones out of older banking and telco systems. A comma-only parser turns all of these into a single column of text, which looks like the tool worked. The delimiter is detected here and the detection is shown in words, so you can see what it decided and overrule it.

Because a CSV file has no types. A cell holds characters; whether 1200 is a number, a product code or a year is a decision made by whatever reads the file, and different readers decide differently — which is why the same export can look right in one tool and wrong in another. This page makes strings the default so that what you get out matches what was in the file, and puts the guessing behind a switch you have to turn on.

One rule covers the numbers: an unquoted field becomes a number only if writing that number back out gives exactly the characters you started with. So 1200 becomes the number 1200, while 007 stays "007" (writing 7 back gives different characters), 1.10 stays "1.10", 1e5 stays "1e5" and +60123456789 stays text. On top of that, true and false in any case become booleans, the word null and an empty unquoted cell become null, and a quoted empty cell stays an empty string. Anything inside quotes is left alone entirely, so a quoted 007 is text no matter what. What the rule cannot save you from is a value that is genuinely a valid number and was never meant to be one — a 12-digit account number with no leading zero converts cleanly and silently. That is why the setting is off by default.

They are numbered instead of merged. A header of name,name,email gives keys name, name_2 and email, plus a warning naming the column. This is not tidiness: a JavaScript object cannot hold the same key twice, so without the rename the first column would be overwritten by the second and would vanish from every record with nothing in the output to show it ever existed. A header cell left blank is named after its position — column3 for the third — and rows whose field count does not match the header are reported by row number, with missing fields filled in and extra ones named the same way.

Nested objects flatten into dotted column names: {"address":{"city":"Ipoh"}} becomes a column called address.city. Arrays are written into a single cell as JSON, so ["vip","net30"] becomes one cell containing ["vip","net30"], quoted and with its own quotes doubled as the CSV rules require. Indexing them into tags.0 and tags.1 was rejected because the column count would then be set by the longest row in the file — one record with a hundred tags gives a hundred columns that are empty on every other row. Nulls become empty cells, because CSV has no way to write null. Column names are collected from every record rather than only the first, so a field that appears halfway down the file still gets a column.

Excel on Windows does not assume UTF-8 for a .csv unless the file begins with a byte order mark — three invisible bytes that say what the encoding is. Switch on “BOM for Excel” before downloading and it opens correctly. It affects the downloaded file only; the text on screen and the copy button never carry it. Going the other way, a BOM at the start of a file you paste in is stripped automatically, because left in place it becomes part of the first column name and that key then quietly matches nothing.

Any cell whose text starts with =, +, - or @ is treated as a formula by Excel and Google Sheets, so =1+1 gets evaluated and a phone number typed as +60… can turn into something unrecognisable. This page writes your values exactly as you gave them and does not insert an apostrophe or any other prefix to defuse it, because that would alter your data to work around a setting in someone else’s program. It is worth knowing about if the file is going to a spreadsheet; if it is going to a program, it does not arise.

No. Both directions run in JavaScript this page already loaded, there is no upload and no request of any kind, and nothing is written to localStorage — reload and the box is empty. That matters more here than on most pages, because what people paste into a CSV converter is usually a real export with real names, real addresses and real amounts in it.