CSV to JSON: The Five Cases That Break Every Converter

RFC 4180 is a description, not a rule

Everyone cites RFC 4180 as though it were the CSV standard. Its own front matter says otherwise: it is Informational, which in IETF terms means it does not specify an Internet standard of any kind. It was published in 2005 to write down what programs were already doing, and it says so about its own subject — rule 5 notes that some programs, Microsoft Excel among them, do not use double quotes at all. That is the root of every problem in this article. There is no authority to appeal to when two tools disagree about the same file, because neither of them is violating anything.

The RFC does define two things that would help, and neither of them survives contact with a file on disk. It defines a header parameter on the text/csv media type, with the values present and absent, so a recipient can be told whether the first row is field names. And it says common usage is US-ASCII, with other character sets carried by the charset parameter. Both are MIME parameters: they live on an HTTP response or an email part, not inside the bytes. Save the same data as sales.csv and both facts are gone. That is why every CSV reader in the world has a checkbox that says first row is a header, and why the encoding has to be guessed.

Cases 1 and 2 — the delimiter and the newline inside a quoted field

These two are the same bug wearing different clothes, and both come from splitting on the raw character instead of parsing. A converter that does text.split(",") turns Tom,"Paris, France" into three columns and every row below it inherits the extra column. A converter that does text.split("\n") first cuts a two-line address in half and produces a row with one field. The fix is the same for both: walk the string character by character, keep a flag for whether you are inside quotes, and only treat a delimiter or a newline as structural when the flag is off.

Both pass. name,address / Tom,"12 rue A\nParis" / Ann,"3 rue B" returns exactly two objects, the first with an address of two lines. A doubled quote is unescaped on the way in, so "He said ""hi"" loudly" comes back as He said "hi" loudly. One deviation from the RFC is worth knowing: the tool normalises every CR LF to LF before parsing, so a line break that was CR LF inside a quoted field comes out of the converter as a bare LF. Nothing is lost, but if you are byte-comparing the round trip, that is where the difference will be.

The case it does not rescue is the one the RFC is explicit about. Section 2.4 says spaces are considered part of a field and should not be ignored, so in Tom, "Paris, France" the third character of the field is a space and the quote that follows is just a character, not an opening delimiter. The parser agrees with the RFC and produces two broken columns, " Paris and France". Most people writing that line meant it to be quoted. If your exporter puts a space after the delimiter, strip it before converting or the quoting is decorative.

Case 3 — types, and the round-trip test that saves your phone numbers

CSV has no types. Every cell is text, and the moment you produce JSON you have to decide whether 1 is the string "1" or the number 1. The converter's default is to decide nothing: conversion is a toggle and it starts off, so a plain run gives you an array of objects whose every value is a string. That is the right default, because a string is always recoverable and a number is not.

Turn the toggle on and the rule is a single round-trip test: a cell becomes a number only when printing that number back gives exactly the characters that arrived. Run it and 1 becomes 1, but 0044 stays "0044" because Number("0044") prints as 44. 1.0 stays "1.0" because it prints as 1. 1e3 stays "1e3" because it prints as 1000. .5 and +1 stay strings for the same reason. And 9007199254740993 stays a string, because JavaScript's nearest double prints as 9007199254740992 — a converter without this guard silently changes the last digit of a large identifier, and nothing downstream will ever tell you.

Three things the round-trip test does not cover, because they are handled by literal comparison instead. true and false become booleans, but only in lower case: TRUE, True and FALSE all stay strings, which matters because Excel writes booleans in upper case and the French and German user interfaces write VRAI and WAHR. null becomes JSON null, which is a genuine trap — a text field whose value is the four letters null is not the same thing as a missing value, and after conversion you can no longer tell them apart. And nothing at all is done with dates: 2026-08-18 stays the string "2026-08-18", which is the correct answer, because a converter that parses dates has to pick a time zone and will get it wrong.

Case 4 — duplicate headers, blank headers, ragged rows

A CSV is free to repeat a column name; a JSON object is not. Given name,name,name over a,b,c, the naive implementation writes the same key three times and JSON keeps the last one, so you get {"name": "c"} and two columns of data are gone with no error. This converter renames instead: name, name_2, name_3. It also handles the follow-on case, where the invented name collides with a real one — name,name,name_2 gives name, name_2 and name_2_2, because the renamer checks against everything already used rather than only against the original headers.

A header cell that is empty gets a positional name: name,,name, over a,b,c,d gives name, column2, name_2 and column4. Header names are trimmed, so " name , age " produces name and age. And the width of the output is the widest row in the file, not the width of the header — a,b,c over the two rows 1,2 and 3,4,5,6 gives every object four keys, with c empty on the short row and a column4 holding the 6 that the header never accounted for. Nothing is dropped, which is the right call for a conversion tool: a converter that silently truncates a long row is destroying the one row that most needs looking at.

Case 5 — encoding, the one the tool cannot fix

A CSV file is bytes. Nothing inside it says which table turns those bytes into characters, and RFC 4180 puts that information in a MIME parameter that a file on disk does not carry. Two mechanisms partly cover the gap. A byte order mark at the start of the file identifies UTF-8, UTF-16 LE and UTF-16 BE, and the browser's own file reader consumes it: drop a UTF-16 LE file with a BOM into the tool and the text arrives correctly decoded, with the mark already removed. The tool then strips a BOM again itself, which catches the case where a mark arrives through the clipboard rather than through a file.

The gap that stays open is the file with no mark at all, which is most of them. Save a spreadsheet as plain CSV on a Western European Windows machine and you get Windows-1252, one byte per character, no BOM. There is no encoding selector on this converter, so the reader falls back to UTF-8, the byte E9 that meant é is not valid UTF-8, and it is replaced by U+FFFD. The tool then parses cleanly and returns {"name": "Andr�", "city": "K�ln"} with no warning, because as far as the parser is concerned nothing went wrong. U+FFFD carries no record of the byte it replaced, so this is not repairable after the fact: re-export the file as UTF-8, or paste the text instead of dropping the file, since text on the clipboard has already been decoded by the application that owns it.

One more encoding case has a sharp edge: UTF-16 without a byte order mark. There is nothing to sniff, so the file is read as UTF-8, every second byte is a zero, and what you get back is a single object whose key contains NUL characters. It looks like garbage rather than like slightly wrong text, which is the good outcome — you will notice immediately. The dangerous failures are the quiet ones, and Windows-1252 read as UTF-8 is the quietest of them, because the columns line up perfectly and only the accented letters are wrong.

The sixth case nobody lists: the sep= line

Excel accepts a first line of the form sep=; as an instruction about which character separates the fields, and plenty of export routines emit it so that a semicolon file opens correctly for a reader whose list separator is a comma. It is not in RFC 4180 and never was — it is a vendor convention that spread because it works. To a converter that has never heard of it, it is simply the first record of the file.

That is what happens here. Feed the converter sep=; followed by Name;Ville;Montant and two data rows, and the auto-detector correctly picks the semicolon — because the sep= line itself contains one — but then the header step consumes it. You get three objects instead of two, with the keys "sep=", column2 and column3, and the real header names Name, Ville and Montant appear as the values of the first one. It is obvious as soon as you look at the output, and invisible if you pipe it straight into something else. Delete the first line before converting, or convert the delimiter first and let the delimiter converter rewrite the hint for you.

Frequently asked questions

Should I turn type conversion on or leave it off?

Leave it off unless something downstream needs real numbers. A string is a lossless representation of what was in the cell; a number is a lossy one, and the loss is irreversible. The round-trip guard means this particular tool will not damage 0044, 1.0 or a 19-digit identifier, but it will convert a column of postal codes that happen to have no leading zero, and then 75001 and 75008 are numbers while a Dutch code like 1012 AB is still a string — one column, two types, and whatever consumes the JSON has to handle both. If you do need numbers, convert the columns you care about after the fact, where you can name them, rather than letting a heuristic decide column by column.

How does the converter know my file uses semicolons?

It counts candidate delimiters — comma, semicolon, tab and pipe — on the first record only, skipping anything inside quotes, and picks whichever occurs most. Reading only the first record is deliberate: a header like "Nom;Prénom" should not be scored by a comma buried in a quoted address three hundred rows down. The limitation is the mirror image. If your header happens to contain one comma and your data rows use semicolons, the detector picks the comma and every row becomes a single field. Two symptoms give it away instantly: one key in every object, and a key whose name is the whole header line. When in doubt, set the delimiter explicitly instead of relying on detection.

Why did my accented characters turn into question marks or black diamonds?

Because the file was not UTF-8 and nothing told the reader so. The black diamond with a question mark is U+FFFD, the Unicode replacement character, and it is what a decoder emits when a byte sequence is not valid in the encoding it was told to assume. Your file was almost certainly Windows-1252 or ISO 8859-1, where é is the single byte E9; UTF-8 needs two bytes for é, and E9 alone is not a legal start of anything. The damage happens before the CSV parser runs, so no CSV setting will undo it. Open the original in a text editor that lets you choose the encoding, save it as UTF-8, and convert again. If the file came from a spreadsheet, export it with the UTF-8 option rather than plain CSV.

My rows do not all have the same number of fields. Will data be dropped?

No. The output width is the widest row in the file, including rows wider than the header. A short row gets empty strings for the missing columns; a long row gets extra keys named column4, column5 and so on for the fields the header never named. Nothing is truncated, which matters because an over-long row is usually the symptom of an unescaped delimiter somewhere above it, and truncating would hide the evidence. If you see column4 in your JSON and your header only had three names, look for a field containing an unquoted delimiter — that row is where the file went wrong.

Is there a version of CSV that does not have these problems?

Not within CSV itself, because the format has no place to put the metadata that would settle the questions. What exists instead are conventions layered on top: an accompanying schema file that names the columns and their types, a fixed export profile agreed between the two systems, or a format that carries its own types. If you control both ends, JSON Lines — one JSON object per line — solves the quoting, the newlines and the types at once, at the cost of being larger and not opening in a spreadsheet. If you do not control both ends, the practical answer is to be boring: UTF-8 with a BOM, comma or semicolon consistently, every field quoted, no sep= line, and a header whose names are unique and free of the delimiter.