CSV Validator

There is no CSV standard, only conventions

RFC 4180 describes a common form but is not binding, and real files deviate constantly. Delimiters may be commas, semicolons, tabs or pipes; line endings may be either convention; quoting may be minimal, full or inconsistent within one file. A validator can only check a file against a chosen dialect, which is why the first question is always which dialect this file claims to be.

Semicolons come from spreadsheet locales

In locales where the comma is the decimal separator, Excel writes and expects semicolon-delimited files while still calling them CSV. A file exported in Germany and imported in the US collapses into a single column. This is the most common CSV interoperability failure and has nothing to do with the file being malformed.

Quoting is where files actually break

A field containing the delimiter, a line break or a quote character must be quoted, and a literal quote inside a quoted field is escaped by doubling it. Unbalanced quotes are catastrophic rather than local: the parser treats everything from the unclosed quote onward as one field, so a single bad row destroys the remainder of the file. When a CSV imports correctly for 4,000 rows and then collapses, an unbalanced quote at row 4,001 is the first thing to check.

Embedded newlines are legal and widely mishandled

A quoted field may contain line breaks, so the number of lines in a CSV file is not the number of records. Any tool that splits on newlines before parsing quotes will corrupt such files, and this includes a great deal of quickly written code. If a row count disagrees with a line count, this is usually why.

Encoding and the byte order mark

A UTF-8 BOM at the start of a file becomes part of the first column's header name in many parsers, so a column named "id" silently becomes "\ufeffid" and every lookup by name fails while the file looks perfect. Files exported from Excel commonly carry one. Mixed or mislabelled encodings also turn accented characters into mojibake that survives into the database.

Structural checks worth running

Consistent field count per row, a header row with unique non-empty names, no trailing delimiter creating a phantom column, and consistent line endings. Ragged rows — where some have more fields than the header — usually indicate an unquoted delimiter inside a value, which is the same root cause as most quoting failures.