Convert CSV to JSON
Paste delimited text and it comes back as JSON. Quoted fields keep their separators and their line breaks, short rows are padded rather than dropped, and any value a number would damage stays a string.
How it works
There is no CSV standard. RFC 4180 was published in October 2005 as an Informational document, and its section 2 opens by saying it describes the format that seems to be followed by most implementations. It registered the media type text/csv and wrote down what programs were already doing. Nothing was obliged to follow it then and nothing is obliged now, so a file with the extension .csv tells you almost nothing about the bytes inside it. A reader has to work out the dialect from the content, or ask.
The parts RFC 4180 does fix are worth knowing, because real files break all of them. Records end with CRLF, and the same document's registration section warns in the next breath that some implementations use other values. A header line is optional and signalled by a MIME parameter, header=present, which no file arriving as a file can carry. Fields are separated by commas. That last one holds least often of the three. The registration also concedes the position outright under interoperability: there are considerable differences among implementations, so read liberally.
The separator is frequently not a comma
Excel writes the list separator from the Windows regional settings, so the menu item labelled CSV (Comma delimited) produces semicolons on a machine set to German or French. A file may also begin with a line reading sep=; which Excel treats as an instruction about the rest of the document. No RFC mentions that line.
This page honours a sep= line when one is present. Otherwise it counts commas, semicolons, tabs and pipes outside quoted regions across the first 20 lines, and scores each by how consistently the same count repeats from line to line. Reading only the first line picks the wrong character whenever the header is short words and the data below it is full of prose. The winner and the runner-up are both reported, so a bad guess is something you can see rather than something you find later.
Quoting is the only escape there is
A field that contains the separator, a line break or a double quote is wrapped in double quotes, and a double quote inside such a field is written twice. That is the whole mechanism. There is no backslash escape, no alternative quote character, and no way to mark a field as raw.
The consequence is the trap. A single record can span several lines of the file, so the line count and the record count are different numbers, and splitting the text on the newline is the most common way CSV reading breaks. The parser here walks the text one character at a time and tracks whether it is inside a quoted field. Inside one, a quote ends the field only when a separator, a line ending or the end of the text follows it, so a stray internal quote stays a character instead of shifting every field after it.
A CSV holds no types at all
Every field in the file is text. The format carries no schema, no column declarations and no way to mark one column numeric, so a reader returning anything other than strings has invented the type itself. Python's csv module invents nothing and hands back strings. pandas read_csv guesses a dtype per column, so a column of zero-padded postcodes arrives as int64 and 01234 becomes 1234; the documented way to stop it is passing dtype=str. Excel guesses at the moment the file opens, before anything is saved.
The guessing goes past numbers. A 2016 Genome Biology paper by Ziemann, Eren and El-Osta scanned supplementary Excel files from genomics journals and found gene-name corruption in roughly a fifth of them: the symbol SEPT1 arriving in the sheet as 1-Sep. In 2020 the HGNC renamed 27 human genes rather than keep fighting the spreadsheet, so SEPT1 is now SEPTIN1 and MARCH1 is MARCHF1. Renaming the genes was the cheaper repair.
What this page declines to convert
The test for a number is a round trip. A field becomes a number only when writing that number back as text reproduces the original characters. 0042 fails, because it returns as 42 and stops matching the system it came from. A leading plus fails, 0x1F fails, and 1.50 fails because it returns as 1.5, a different version number even though it is the same quantity. 9007199254740993 fails because a 64-bit float has no room for it and returns the even number below.
The words true and false convert. yes, no, Y and N do not, and neither do 1 and 0, since a column of ones is a count until somebody says otherwise. An empty field becomes null under the third inference setting and stays an empty string under the second, because the file gives no way to tell a missing value from a blank one. Every field the round-trip rule turned down is counted and reported with the reason it failed.
Questions
Why is my ID column still text?
Because converting it would change it. The rule here is that a field becomes a number only when the number written back as text is identical to the field, so 0042 stays a string rather than becoming 42, and a value above 9,007,199,254,740,991 stays a string rather than being rounded to the nearest value a 64-bit float can hold. The row above the output counts each refusal and names which rule caught it. Set type inference to none to keep the whole file as strings.
My export uses semicolons. Do I have to say so?
No. The separator is detected by counting commas, semicolons, tabs and pipes outside quoted fields over the first 20 lines and taking the one whose per-line count repeats most consistently. A leading sep= line, which Excel writes and reads, overrides the count. The result and the runner-up are both reported, so a wrong guess is visible, and the delimiter control forces a specific character when the file is too short or too odd to sniff.
What happens to a row with the wrong number of fields?
It is kept. A row shorter than the header is padded with null so every object has the same members, and a row longer than the header keeps its extra values under generated names of the form column_7. Each ragged row is listed with its line number, the count it had and the count the header declared. Dropping the row would lose data without telling anyone, which is the behaviour worth avoiding here.
Can a field contain a line break?
Yes, if the field is quoted, and the parser handles it. That is why the row count and the file's line count can differ. Line breaks inside a quoted field are normalised to LF in the output string. A blank line between records is discarded rather than read as a one-field row, since a stray empty line is nearly always formatting.
Two columns in my file share a name. What happens?
The second one is renamed with a _2 suffix, the third with _3, skipping any suffix the file already uses as a real name. A JSON object cannot hold two members with the same name, so without renaming the later column would overwrite the earlier one and the data would be gone with no sign of it. The original name and its replacements are reported above the output.