The CSV Format Explained (and How to Fix Common Problems)

CSV is the universal language of spreadsheets and data exports — simple enough to open in a text editor, supported by every tool that touches tabular data. But that simplicity hides some surprisingly common traps that turn a clean export into a mangled mess. Here's how CSV really works and how to avoid the usual problems.

What CSV is

CSV stands for comma-separated values. Each line is a row, and within a row, commas separate the columns. The first line is usually a header naming each column.

name,email,age
Ada Lovelace,ada@example.com,36
Alan Turing,alan@example.com,41

That's the whole idea — which is exactly why the edge cases bite.

The common problems

Commas inside a value

What if a value itself contains a comma, like Lovelace, Ada? Without a rule, that one field would look like two columns. The fix: wrap fields containing commas in double quotes.

"Lovelace, Ada",ada@example.com,36

Quotes inside a value

A double quote inside a quoted field is escaped by doubling it: "She said ""hi""" represents She said "hi".

Line breaks inside a value

A field can even contain a newline if it's quoted — which is why you can't always assume “one line = one row” when parsing CSV by hand.

The delimiter isn't always a comma

In regions where the comma is the decimal separator, spreadsheets often export with semicolons (;) or tabs instead. If a file opens as one big column, a mismatched delimiter is usually why.

Encoding and the BOM

CSV carries no encoding information, so accented characters can garble if the reader guesses wrong — save as UTF-8. Some programs also add an invisible byte-order mark (BOM) at the start that can break the first column header. (See our guide to character encoding for why.)

Working with CSV data

Because CSV is just lines of text, plain text tools handle a lot of tidying: put rows in order with the sort lines tool, drop repeats with remove duplicate lines, and see exactly what changed between two exports with a text diff. For structured transformation, converting the data to JSON first is often the cleaner path.