CSV to JSON Converter

There is a spec for CSV. It is RFC 4180, nothing implements it exactly, and it stays quiet about most of what you will actually meet in a real file. It does pin down the quoting though, and quoting is the part that bites.

name,note
Ada,"Wrote the first algorithm, for a machine
that was never built"
Grace,"Coined ""debugging"" after a moth"

Three rules, and that is the whole format:
  a field with a comma, a quote or a newline gets quotes
  a quote inside a quoted field is written twice
  everything else is written as it is

A quoted field can contain the delimiter, and it can contain line breaks, and that is why CSV cannot be parsed a line at a time. Any tool that splits on newlines first will break on the first exported record with a paragraph in it, and when it breaks you get rows, not an error.

The delimiter is not always a comma

name;price;note
Widget;1,50;"A comma is the decimal point here"

Half of Europe writes 1,50 for one and a half, so the
delimiter has to be something else. Excel picks it from
the machine's locale. So a file that opens
correctly on one computer is one column on another.

Tabs come from spreadsheets and pastes, pipes come from log processing, and semicolons come from anywhere a comma is already the decimal separator. The delimiter is worked out here by looking for the character that appears the same number of times on every line, which is a much better signal than which one appears most: a file of English sentences separated by semicolons contains more commas than semicolons.

Type inference is a decision about your data

Value       Read as a string      Read with inference

007         "007"                 "007"    leading zero kept
7           "7"                   7
1e5         "1e5"                 100000
true        "true"                true
            ""                    null (when asked for)
+44 7700    "+44 7700"            "+44 7700"
2026-08-14  "2026-08-14"          "2026-08-14"

A leading zero stays a string here, always. It nearly always means an identifier that has to keep its width. A postcode, a phone extension, a product code, a bank sort code. Turning 007 into7 loses the meaning along with the digit.

None of this is hypothetical. Human gene names had to be officially renamed because spreadsheets kept turning SEPT2 into a date, and about a fifth of published genetics papers were found to contain the damage. A tool that guesses aggressively is a tool that will eventually be wrong about something that mattered.

Every number is kept as the characters that were typed instead of parsed into a double, so an eighteen-digit identifier survives. Switch inference off entirely and every field comes out as a string. That is what you want when the CSV is the source of truth and something downstream will do the typing.

Rows with the wrong number of fields

A row with fewer fields than the header usually means a trailing delimiter went missing. A row with more usually means an unescaped delimiter inside a value, and the row after it is often wrong too.

Both get reported above instead of padded without a word, and the table marks the short cells and the extra ones. The JSON output fills missing fields with nothing and gives extra ones numbered names, so the data survives even when the shape does not.

The byte-order mark Excel adds

Save a CSV as UTF-8 from Excel and the file starts with three invisible bytes. Read it naively and the first column is calledname and not name, so it matches nothing, looks identical in every error message, and has cost a great many people an afternoon.

It is stripped here and mentioned when it was there. It exists because Excel will otherwise open a UTF-8 file as the machine's legacy encoding and turn every accent into two characters, so it is a fix for one problem that causes another.

Going the other way

JSON is a tree and CSV is a grid, so something has to give. Nested objects become dotted column names and arrays become indexed ones:address.city, tags.0, tags.1. That keeps every value and makes the path visible. It is the only version anybody can reassemble afterwards.

The columns are the union of every record's keys in the order they first appear, so a record missing a field gets an empty cell instead of shifting everything after it along by one. A field is quoted only when it needs to be, so quotes go on when the value contains the delimiter, a quote, a line break, or whitespace at either end that would otherwise be lost.

Line endings, and why a CSV is not a text file

RFC 4180 specifies CRLF. Everything on a Unix machine writes LF. Both are read here, including a lone CR from a pre-2001 Mac, and LF is written unless you ask otherwise.

The reason it matters more than usual: a line ending inside a quoted field is data, and a line ending outside one is structure. Any process that normalises line endings across a whole CSV file, and a git checkout with autocrlf on will do exactly that, changes the data and not just the structure.