Validate a CSV file and find errors

1. 1Choose the CSV file to check.
2. 2Leave “First row is a header” ticked if the first row holds column names. The header sets how many fields every row should have.
3. 3Click Validate CSV.
4. 4Read the summary and the problems grouped by type, each with its likely cause and how to fix it. Click a type to see only those rows.
5. 5Optionally, separate the valid rows from the rows to fix: import the first file now, correct the second, then merge them.

A file with one extra field and a blank line.

id,product,price
1,"Desk lamp, black",24.90
2,Chair,49.00,in stock

3,Shelf,35.00

Row 3 - error: Row has 4 fields, expected 3.
Row 4 - warning: Empty row.

01Before importing into a CRM, shop or database

One malformed row can abort an import or, worse, shift values into the wrong columns without any error. Checking first takes seconds and tells you exactly which rows to fix.

02When an import fails with a vague error

Messages like “invalid file format” or “line 18442 could not be parsed” rarely say what is wrong. The validator names the problem: an unclosed quote, an extra delimiter, an empty column name.

03Checking files exported from Excel

Excel saves CSV with your regional list separator (often a semicolon in Europe) and, unless you choose “CSV UTF-8”, in a legacy encoding such as Windows-1252. The validator reports the delimiter it found and flags text that is not valid UTF-8.

• Field count: every row must have as many fields as the header, or as the first row when there is no header. Each mismatch is an error.
• Quotes: a quoted field that is never closed swallows the rest of the file. Unclosed or misplaced quotes are errors.
• Column names: empty names and duplicates (ignoring case and surrounding spaces) are warnings, because many importers reject them. Leading or trailing spaces are noted.
• Blank rows: empty rows in the middle of the file are warnings. Blank lines at the very end are ignored.
• Encoding: every row containing text that is not valid UTF-8, typically from a Windows-1252 export, is reported, with the unreadable cells highlighted.
• Byte order mark: a UTF-8 BOM at the start of the file is noted. It is valid, and Excel needs it to show accented letters correctly, but a few older importers read it as part of the first column name.

• Each problem row is displayed under the column names, so you see which value landed in which column.
• Cells beyond the expected number of fields are marked as extra, and missing ones as missing.
• When a row has too many fields and one of them starts with a space, such as “ France” after “Paris”, it is flagged as the likely spot where an unquoted delimiter cut a value in two.
• When almost every row has the wrong width, the report says so: the header or the delimiter is the likely cause, not the rows themselves.
• The list of problems can be downloaded as a CSV report to share with whoever produced the file.

CSV is described by RFC 4180, and its core rules are short. Records are separated by line breaks. Every record has the same number of fields. A field that contains the delimiter, a double quote or a line break must be enclosed in double quotes, and a double quote inside such a field is written twice ("").

The RFC uses commas, but semicolons are standard in countries that write decimals with a comma, and tabs are used in TSV files. The validator detects comma, semicolon, tab and pipe delimiters automatically.