Reading CSV looks like a one-line job: split on newlines, split on commas, done. It works on the first file you try and fails on the third, because real CSV has commas inside values, line breaks inside quotes, semicolons instead of commas, and no way at all to say whether `007` is a number. This guide goes through what a reader has to get right, in the order the problems show up.
There Is a Description, Not a Standard
RFC 4180, from October 2005, is the document everyone points to. It is Informational, which means it records how CSV was already being written rather than defining a format programs must follow. Its rules cover the basics: one record per line, an optional header line, double quotes around fields that contain commas, quotes or line breaks, and doubled quotes inside them. It also says spaces are part of a field and should not be ignored.
It does not cover the delimiter beyond the comma, the character encoding, what a missing value looks like, or how to describe a column's type. Every one of those is left to the program that wrote the file, and a reader has to infer them.
Why Splitting on Commas Breaks
A quoted field can hold the delimiter, a doubled quote and even a line break. The record "Bo, Jr.","He said ""hi""","line one↵line two" is three fields on two physical lines, where ↵ marks a real line break. Splitting the text on newlines first cuts that record in half, and splitting each line on commas then cuts the name in two.
A correct reader walks the text one character at a time and keeps one piece of state: whether it is inside quotes. Inside quotes, a comma or a line break is just data and two quotes in a row are one literal quote. Outside quotes, a comma ends the field and a line break ends the record. That is the whole algorithm, and it is the reason not to write CSV parsing as a regular expression.
Files from the wild also break the rules in small ways. A quote may appear in the middle of an unquoted value, as in 5" screen, or text may follow a closing quote. A strict parser rejects the file. A practical one keeps reading, treats the stray quote as a character, and tells you it did so.
Guessing the Delimiter
Excel writes CSV with the list separator from the computer's regional settings. In most of Europe, where the comma is the decimal separator, that is a semicolon, so a "CSV" from a colleague in Germany or France may contain no field-separating commas at all. Tab and pipe are common in database exports.
A reader can tell them apart by trying each candidate on the first rows, quote-aware, and keeping the one that splits every row into the same number of fields. A delimiter that leaves each row as one field is ruled out. Counting raw characters is not enough: a semicolon file full of decimal commas, like 1,5;2,5, has more commas than semicolons, but only the semicolon gives a consistent column count.
Which Values Are Safe to Convert
Every CSV value is text. These are the rules the CSV to JSON converter on this site applies when type detection is on.
| CSV cell | JSON value | Reason |
|---|---|---|
| 42 | 42 | Plain integer |
| -0.5 | -0.5 | Plain decimal |
| 007 | "007" | Leading zero, likely a code |
| 1,5 | "1,5" | Decimal comma is not a JSON number |
| 12345678901234567890 | "12345678901234567890" | Over 15 digits, a double would round it |
| TRUE | true | Booleans in any letter case |
| null | null | Only the exact word |
| ["a","b"] | ["a","b"] | Valid JSON inside a cell |
| (empty) | "" | Empty stays an empty string |
Numbers That Should Stay Text
JSON numbers have no leading zeros, so converting 007 gives 7, and the ZIP code, phone number or SKU it came from is gone. A careful converter leaves those as strings.
Length is the other trap. JavaScript, and many JSON parsers, store numbers as IEEE 754 doubles. RFC 8259, the JSON standard, only expects integers between −(2^53 − 1) and 2^53 − 1 to be read the same everywhere, and 2^53 − 1 is the 16-digit number 9,007,199,254,740,991. An 18-digit order ID converted to a number comes back with different last digits, and nothing reports the change. Keeping any value with more than 15 significant digits as a string avoids it, at the price of the consumer having to parse it.
The last decision is what an empty cell means. It could be an empty string, null or a missing key, and CSV cannot say which. Keeping it as "" loses nothing that was in the file, and the consumer can apply its own rule.
Convert CSV with the delimiter detected
Quote-aware parsing, careful type detection, and notes on anything the file left ambiguous.
Headers Make Keys, and Keys Must Be Unique
With a header row, each record becomes an object keyed by column name. Two columns called name would write to the same key, and the second would replace the first. A blank header would give an empty key. Both happen in exported spreadsheets more often than you would expect, so a reader should rename them, for example to name_2 and column_3, and say that it did.
The first header has a trap of its own. Excel and some editors start a UTF-8 file with a byte order mark, an invisible U+FEFF character. A reader that does not remove it produces a first key that looks like id but is actually \uFEFFid, so every lookup by that name fails without an error.
Headers with dots, such as user.name and user.city, usually come from a tool that flattened nested JSON. Rebuilding them into {"user": {"name": ..., "city": ...}} restores the original shape, as long as no other column is named user on its own. When one is, the dotted column has to stay flat, or one of the two values would be lost.
Ragged rows
A row with fewer fields than the header is common, since some writers drop trailing empty fields. Filling the missing ones with empty strings is the usual fix. A row with more fields is more often a sign of an unquoted comma inside a value, so look at that line before trusting the output.
Frequently Asked Questions
Related Tools
Keep Reading
From JSON to CSV: Nested Objects, Arrays and the Excel Problem
Dot paths for nested objects, three ways to handle an array, and why Excel garbles a correct CSV: the BOM, the semicolon, 15 digits and lost zeros.
JSON Errors Explained: Trailing Commas, NaN and Other Rejections
Why valid-looking JSON fails to parse, how to read a parser's error position, and the number precision bug that silently corrupts large IDs.
The Right-Hand Side: What You Can Write in a Regex Replacement
Everything after the pattern gets one sentence in most tutorials, and that is where the mistakes live. $1 versus \1, groups that move, and the ceiling.