How to Validate a CSV File and Fix Common Errors

A valid CSV file has one record per line, the same number of fields in every record, and double quotes around any field that contains a comma, a double quote or a line break. To check a file, paste it into the CSV/TSV Formatter & Validator: it lists the errors it finds, each with a line number, and you fix the line it names.

A row with the wrong number of columns is usually a comma inside a value that was not quoted. The rest of this guide covers how the format works, the errors a validator reports and how to fix each one, and how to turn a CSV into JSON, YAML or SQL.

How is a CSV file put together?

Each record is on its own line, the fields in a record are separated by commas, and the first line can be a header that names the columns. RFC 4180, the document that describes the format, says each line should contain the same number of fields throughout the file, that the last record may or may not end with a line break, and that spaces are part of a field and should not be ignored. This is the validator's own example: four records, five columns, and no problems found.

name,role,location,joined,active
Ada Lovelace,Engineer,London,1843-01-01,true
Grace Hopper,Admiral,New York,1944-06-01,true
"Katherine Johnson",Mathematician,"White Sulphur Springs, WV",1953-06-01,false
"Margaret Hamilton",Engineer,Boston,1960-08-01,true

RFC 4180 is an informational document that records what most programs do, not a strict standard, so real files vary. That is why a validator is useful: it tells you where a file departs from the common rules.

When does a CSV field need double quotes?

RFC 4180 says fields containing line breaks, double quotes and commas should be enclosed in double quotes, and that a double quote inside a quoted field is written as two double quotes.

A line break works the same way. A quoted field can span lines, and the validator keeps the break inside the field instead of starting a new record:

name,note
Ada,"line one
line two"
Grace,ok

That file has two records, and Ada's note is two lines long.

Quotes only count at the start of a field

The validator treats a double quote as the start of a quoted field only when it is the first character of the field. In Ada,5" display the quote is plain text, so the field is 5" display and there is no error. RFC 4180 says double quotes may not appear inside an unquoted field, so other programs may reject that file. For the same reason, a space before the opening quote, as in Katherine, "White Sulphur Springs, WV", means the field is not quoted at all, and its comma splits it into a third column.

Which delimiter does my file use?

The comma is the delimiter RFC 4180 describes, but files use others too, and a file read with the wrong delimiter is not reported as an error: it becomes one wide column. Read with a comma, name;city followed by Ada;London is a table with a single column named name;city. If your table is one column, pick the delimiter yourself.

Spreadsheets take the delimiter from a regional setting. Microsoft says the default list separator Excel uses for a CSV file is a comma, that you can change it in the Windows Region settings, and that changing the decimal separator to a comma forces Excel to use a semicolon as the list separator. So a file saved with one setting can open as a single column where another is in use.

What errors does a CSV validator report, and how do I fix them?

The validator reports two kinds of problem. Errors mean the file does not read as a table, and each comes with the line it starts on. This is what you will see for the most common ones:

Take a file where one person has no city and another has an extra value:

name,email,city
Ada,ada@example.com,London
Grace,grace@example.com
Linus,linus@example.com,Helsinki,Finland

The validator reports Line 3 has 2 columns but the header has 3. and Line 4 has 4 columns but the header has 3. Add an empty field to Grace's line (grace@example.com,) and decide what Finland belongs to on Linus's.

One stray quote can give two errors

A quote that is followed by text is reported on its own. Each of the two inner quotes in "She said "hi" to him" is followed by something other than a delimiter or a line end, so you get an error for the h and another for the space after hi. Double the inner quotes, "She said ""hi"" to him", and both go away.

If a quote is never closed, everything after it, line breaks included, is read as part of that one field. A file with an opening quote on line 2 can therefore shrink to a single record, so fix the first error before you trust the row count.

Warnings

Warnings do not stop the file from being read. They point at things that can still cause trouble in other tools. A warning about the whole file is shown against line 1.

What are line endings and the byte-order mark?

RFC 4180 separates records with CRLF, a carriage return followed by a line feed, but notes that some implementations use other values. The validator reads LF, CRLF and lone CR, shows the ending it found in the stats bar, and warns when a file mixes them.

A byte-order mark is the character U+FEFF at the very start of a file, which is the three bytes EF BB BF in UTF-8. The Unicode FAQ says it is not required for UTF-8, only a signature that can help identify the encoding, and that some recipients do not expect it. In a CSV file the mark sits in front of the first header name, so the validator removes it, with a warning, before it reads the header.

How do I convert a CSV to JSON, YAML or SQL?

Paste the CSV and open the tab you want. These conversions use the same small file, with a leading-zero ID, an empty cell, a quote inside a field and a comma inside a field:

id,name,note
007,Ada,"Said ""hello"""
8,,plain
+15551234,"O'Neil, Pat",x

JSON

An array of objects, one per row, keyed by the header names. Every value is a string, because a CSV file holds only text: 007 stays "007" and does not become the number 7.

[
  {
    "id": "007",
    "name": "Ada",
    "note": "Said \"hello\""
  },
  …
]

TypeScript

An interface for those objects. Because every JSON value is a string, every field is typed string.

export interface Row {
  id: string;
  name: string;
  note: string;
}

YAML

A list of mappings. Values are kept as quoted strings.

- id: "007"
  name: "Ada"
  note: "Said \"hello\""
- id: "8"
  name: ""
  note: "plain"
- id: "+15551234"
  name: "O'Neil, Pat"
  note: "x"

SQL

One INSERT per row, with backtick-quoted table and column names (the table is called records unless you rename it). An empty cell becomes NULL, a plain number such as 8 is left unquoted, and a value that would change as a number, like 007 or +15551234, keeps its quotes. A single quote inside a value is doubled.

INSERT INTO `records` (`id`, `name`, `note`) VALUES ('007', 'Ada', 'Said "hello"');
INSERT INTO `records` (`id`, `name`, `note`) VALUES (8, NULL, 'plain');
INSERT INTO `records` (`id`, `name`, `note`) VALUES ('+15551234', 'O''Neil, Pat', 'x');

The two remaining tabs are CSV, which writes the table back with the same delimiter and line ending and quotes only the fields that need it, and Aligned, a padded plain-text table. An empty or duplicate header name is exported as column_N or with a numeric suffix, so the keys in every conversion are unique.

What is specific to the CSV/TSV Formatter & Validator?

• You can paste text, upload a file of up to 10 MB, or load the example.
• The delimiter is detected on its own: comma, semicolon, tab or pipe, whichever splits the first records into the widest, most consistent table, with ties going to comma, then semicolon, tab and pipe. You can also pick one.
• With First row is the header turned off, the columns are named column_1, column_2 and so on.
• Errors with line numbers: a character after a closing quote, a quote that is never closed, and rows with a different number of columns from the header. At most 25 of those row errors are listed, followed by a count of the rest, and the page shows the first six issues and a count of the others.
• The Table tab shows the first 200 rows. The other tabs convert every row, each with Copy and Download.
• Everything is parsed in your browser, and nothing you paste or upload is sent anywhere.

Try the CSV/TSV Formatter & Validator

Open the CSV/TSV Formatter & Validator, press Load example, then break it: remove a field from one row, or take the quotes off White Sulphur Springs, WV, and watch the error and its line number appear.

How to validate a CSV file