Why CSV breaks so often
CSV looks like the simplest data format in the world: values separated by commas, one record per line. In practice it’s one of the most error-prone, because that simplicity hides a handful of rules that tools apply inconsistently. A single stray quote or one row with an extra comma can make an entire file fail to import, and the error a database hands back is often unhelpful. Validating the structure first — and seeing the exact line of each problem — is the fastest way to turn a failed import into a quick fix.
The CSV Validator parses your file the way a strict importer would and reports every issue with a line number. This guide explains the rules behind those checks so the messages make sense.
The CSV rules (RFC 4180)
There’s no single official CSV standard, but RFC 4180 is the closest thing, and most robust parsers follow it. The core rules are:
- Fields are separated by a delimiter — a comma by default.
- Records are separated by line breaks — typically
\r\nor\n. - The first row may be a header naming the columns.
- A field that contains the delimiter, a quote, or a line break must be wrapped in double quotes.
- A literal double quote inside a quoted field is written as two double quotes (
"").
That fourth and fifth rule are where most problems start. Consider a company name like Acme, Inc.. Written bare, the comma splits it into two fields and the row suddenly has one column too many. Written correctly as "Acme, Inc.", the quotes tell the parser the comma is part of the value.
Delimiters: not always commas
Despite the name, CSV files don’t always use commas. In many European locales the comma is the decimal separator (1,5 means one-and-a-half), so spreadsheets export with semicolons instead. Tab-separated files (TSV) are common in scientific and database exports, and pipe (|) delimiters show up in legacy systems. They’re all the same format with a different separator. The CSV Validator lets you pick comma, semicolon, tab, or pipe so it counts columns correctly for your file. Choosing the wrong delimiter will make a perfectly good file look like it has one giant column.
Quoting, in detail
Quoting is the mechanism that lets a field contain otherwise-special characters. Inside a pair of double quotes, commas, line breaks, and the delimiter are all treated as literal text. To include an actual double quote, you double it:
id,quote
1,"She said ""hello"" and left"
2,"Multi-line
value here"Row 1’s field contains a real pair of quotes around hello; row 2’s field spans two physical lines but is still a single value. A correct parser — and the validator — reads both as one field. The classic failure is an unclosed quote: open a quoted field and forget to close it, and the parser swallows the rest of the file looking for the closing quote. The validator flags this specifically because it’s both common and catastrophic.
The errors the validator reports
When you run a file through the CSV Validator, problems are sorted into errors and warnings.
Errors are structural faults that would break most importers:
- Unclosed quoted field — a quote was opened and never closed.
- Wrong column count — a row has more or fewer fields than the header, almost always from an unescaped delimiter or quote.
- Empty header name — a column in the header row has no name, which breaks tools that key data by column name.
Warnings are worth knowing but not necessarily fatal:
- Duplicate header name — two columns share a name, which can cause one to silently overwrite the other downstream.
- Empty row — a fully blank line, often a harmless trailing newline but sometimes a sign of a copy-paste mistake.
A file is reported valid only when there are zero errors. Warnings don’t block validity but are flagged so you can decide whether they matter for your use.
Reading a column-count error
The most frequent error is “row N has X columns; expected Y.” To debug it, line the offending row up against the header and count the delimiters. Nine times out of ten you’ll find either a comma inside a value that wasn’t quoted, or a quote that opened a field and shifted everything after it. Fix the quoting and the column count falls back into line. Because the validator gives you the exact line number, you don’t have to hunt through thousands of rows.
Validate before you transform
A good workflow is to validate first, then convert. Once the CSV Validator reports a clean file, you can confidently transform it — for example into JSON for an API — without the conversion choking on a malformed row. Our CSV to JSON guide walks through that next step, and it relies on the same RFC 4180 parsing rules described here, so a file that validates cleanly will convert cleanly too.
Practical tips for clean CSV
A few habits prevent most CSV pain:
- Always quote fields that might contain the delimiter, quotes, or line breaks — even if a given value currently doesn’t, future data might.
- Keep the header row consistent: unique, non-empty names, no trailing delimiter.
- Stick to one delimiter throughout the file and know which one your tools expect.
- Prefer UTF-8 encoding to avoid mangled accented characters.
- When exporting from a spreadsheet, open the result in a text editor once to confirm the quoting looks right.
Run the file through the CSV Validator before every import and you’ll catch the ragged rows and stray quotes while they’re still trivial to fix — long before they reach a database that gives you nothing but a line number and a shrug.