What Is CSV, and Why Is It Still So Widespread?
CSV (Comma-Separated Values) is one of the oldest and most widely used file formats for storing tabular data as plain text. Each line represents one record, and each value on that line represents a column separated by a comma. The format is so simple that it can be opened and read in any text editor; but that apparent simplicity is not as trouble-free as it looks.
Spreadsheet applications such as Excel and Google Sheets, product/order exports from e-commerce platforms, accounting software, moving data between CRM systems and database dumps all still rely heavily on CSV. The reason is simple: compared with JSON or XML, CSV takes up far less space, can be read directly by a human eye and is supported by almost every piece of software.
Modern web applications mostly move data around as JSON, but a format that users can edit by hand and open and filter in Excel is still needed; on that front there is no practical alternative to CSV. That is why converting JSON data coming from an API into CSV for reporting, or importing a CSV exported from an accounting program into a web application as JSON, is an ordinary part of everyday software development.
RFC 4180: CSV's Official (But Little-Known) Rules
For many years CSV had no official standard; every application interpreted the format a little differently. Published in 2005, RFC 4180 documented the CSV practices that were already in de facto use and established a common reference point. It is not a mandatory standard, but the vast majority of tools that produce and read CSV today follow these rules.
- Fields are separated by a comma (
,), and lines end with CRLF (carriage return + line feed). - If a field contains a comma, a double quote or a line break, the entire field must be enclosed in double quotes (
"). - A double quote appearing inside a quoted field is escaped by writing two double quotes in a row (
""). - The first line usually, but not necessarily, contains the column headers.
For example, in the line "Doe, John",30,Istanbul the first field is quoted because it contains a comma; a rule-abiding parser must read this as three fields (Doe, John, 30, Istanbul), not four. When a quote has to appear inside a field, the same logic applies: a spelling such as "6"" TV" means the field value is actually 6" TV.
Why Does a Simple split(comma) Fall Short?
Many developers try to parse CSV by splitting the line directly on commas (split(',')). This method appears to work with simple, unquoted data, but on a line such as "Doe, John",30,Istanbul it cannot tell a structural comma from one inside a quoted value; it wrongly splits Doe, John, which should be a single field, into two separate fields, and the column count for that row shifts.
The same problem applies to embedded newlines. A quoted field may contain a real line break (Enter); a parser that reads the file line by line perceives this single logical record as two separate lines, and the data is corrupted. A correct parser has to walk through the input character by character, tracking whether it is currently inside quotes (its state).
The delimiter is not universal either. In the locales of many European languages, decimal numbers are written with a comma (1,5), so in those regions Excel exports CSV using a semicolon (;) instead of a comma. If you open a file and notice that the first line looks like a single column, the delimiter is most likely a semicolon rather than a comma.
From CSV to JSON: The Header Row Becomes the Object Keys
The logic for converting CSV to JSON is clear: each value on the first line becomes a key of the objects in the resulting JSON array. Every subsequent line turns into an object whose values are matched against those keys. So a CSV with the header name,age,city becomes an array of objects, each carrying the keys name, age and city.
There is one important point to watch here: CSV carries no type information of its own, so in a CSV-to-JSON conversion every value arrives as text (a string); even a numeric column appears in the JSON output as a quoted string. In the other direction, when going from JSON to CSV, the keys of the first object form the header row, and each value is quoted only when it needs to be (when it contains a comma, a quote or a line break).
Common Mistakes
| Mistake | Why It Causes Trouble |
|---|---|
| Encoding mismatch | On some systems Excel's default CSV export uses a local encoding instead of UTF-8; non-ASCII characters (such as é, ü, ş, ç) then appear mangled. |
| Not noticing the BOM (byte order mark) | Some CSV files saved as UTF-8 add an invisible BOM byte at the start; a parser that does not account for it can leak a hidden character into the beginning of the first column name. |
| Inconsistent column counts | Rows that hold fewer or more values than the header lead to missing or shifted fields in the resulting JSON objects. |
| Trailing empty lines | Blank lines left at the end of the file can be added to the JSON array as an empty or meaningless object without you noticing. |
Rather than checking all of these rules by hand, using a tool that handles quoting, escaped quotes and embedded newlines in accordance with RFC 4180 both saves time and prevents data loss.