CSVLint and Other Validators for CSV Files Compared

A validator answers one question: is this file well formed? The answer sounds trivial and turns out to be what stops half of the bad loads in a pipeline, because an invalid file caught at the door costs a line in a log, and the same file caught three steps later costs an afternoon. Several tools do the job, and they differ in what they check, how many errors they report and how they behave in a script. I ran four of them on the same two small broken files and compared the output. This is part of the toolbox surveyed in CSV tools worth knowing, and it complements the checks in validating a CSV file, which describes what to validate before choosing a tool.

Two broken files

The first file has a short row and a long row, the common kind of damage from an unescaped comma or a truncated export:

order_id,customer,total
1001,Acme Ltd,120.50
1002,Beta Corp,89.90
1003,Cy Foods
1004,Delta,12.00,extra

The second has a different fault, an opening quote that is never closed. The parser sees a quote, keeps reading until it finds the next quote, and finds none:

order_id,customer,total
1001,Acme Ltd,120.50
1002,"Beta Corp,89.90
1003,Cy Foods
1004,Delta,12.00,extra

A person reads the second file as "line 3 has a stray quote". A parser reads it as "record 2 starts at line 3 and runs to the end of the file". The way each tool reports that difference tells you something about it.

qsv validate

qsv validate checks that every record has the same number of fields as the header, and, given a JSON Schema, that values satisfy it. On the first file it reported the first violation and stopped:

$ qsv validate ragged.csv
Validation error: CSV error: record 3 (line: 4, byte: 66): found record with 2 fields, but the previous record has 3 fields.
Use `qsv fixlengths` to fix record length issues.
exit=1

The message carries the record number, the physical line and the byte offset, which is enough to find the row with sed -n 4p, and a pointer to the command that repairs it. On the second file it reported record 2 (line: 3, byte: 45): found record with 2 fields, which is the swallowed-quote record: it starts on line 3 and contains everything after the quote, so it has two fields. The tool is quick and its message names the symptom and not the cause, so for a quote error you have to look at the line it points to. Because it stops at the first error, a file with a hundred bad rows needs a hundred runs, or a different tool.

csvkit csvclean

csvkit's csvclean reports problems and can write out the good rows. In csvkit 2 you must enable checks explicitly, and -a turns them all on. Without a flag it exits with an error that no checks were enabled, which is a safe behavior and a surprise the first time:

$ csvclean -a ragged.csv
line_number,msg,order_id,customer,total
3,"Expected 3 columns, found 2 columns",1003,Cy Foods
4,"Expected 3 columns, found 4 columns",1004,Delta,12.00,extra
order_id,customer,total
1001,Acme Ltd,120.50
1002,Beta Corp,89.90

It found both problems in one run and reported them as CSV, with the offending rows beside each message, which is convenient for sending back to a provider. The remaining output after the error table is the rows that passed. On the unclosed-quote file it gave one error, for the swallowed record, and showed the whole multi-line field. Exit status was non-zero in both cases, so it works in a script. The tool is Python and slower than qsv on large files, which matters if you validate a few gigabytes every day (getting started with csvkit).

CSVLint

CSVLint is the Open Data Institute's validator, a Ruby gem that checks structure and can validate against a CSV on the Web schema (CSVW and describing CSV with metadata explains the format). I ran it from a throwaway Ruby container on a three-line file with a short row:

$ csvlint r.csv
r.csv is INVALID
1. ragged_rows. Row: 3. 2
exit=1

The error type, ragged_rows, and the row number, with the field count found, are in the report. CSVLint distinguishes categories of problem: structural errors such as ragged_rows and unclosed_quote (it uses names like blank_rows, line_breaks and whitespace for others), and warnings about things like a missing content type or an inconsistent line ending. That separation is useful for publishing data, where "technically valid but sloppy" is worth flagging. The cost is a Ruby dependency, which for many teams is a reason to prefer one of the others.

Frictionless

Frictionless validates a file against a table schema that you write or let it infer: field names, types, constraints such as required and unique and enumerations. Run with no schema, it infers one and checks the file against it. On the unclosed-quote file it reported one problem in a table:

$ frictionless validate bad.csv
dataset: bad  type: table  status: INVALID
Row  Field  Type          Message
3    3      missing-cell  Row at position "3" has a missing cell in field "total" at position "3"

(The table above is flattened from the tool's boxed output.) The error type is missing-cell, with the row and the field, and the message names the column. What the structure-only checkers cannot do is here: with a schema that says total is a number and required, it would also flag a value of eighty, a duplicate order_id or a status outside an allowed list. It is the right tool when the contract is a typed schema (writing a schema for a CSV). It also cost the most to set up, as a Python package with a long list of dependencies.

How they compare

For a first check in a script, qsv validate is the smallest dependency and the fastest, and a non-zero exit is the whole interface. When you need to tell a provider everything that is wrong with their file, csvclean -a or Frictionless gives you the full list. When the file is published data with a metadata file, CSVLint speaks CSVW. When the contract has types, use Frictionless or a schema-aware check in your loader.

What none of them checks

Structure is the floor. All four are blind to what your data means: a negative quantity, a date in the year 1900, a country code that does not exist, an email with no at sign, a total that does not equal the sum of its lines. Those are business rules, and they belong in your own checks, run in SQL or code after the structure passes (writing data quality checks for a CSV). They are blind to encoding as well, apart from invalid UTF-8; a file in the wrong code page passes as long as the byte sequences are legal.

The other blind spot is the one in the second sample file. A tool that reports "record 2 has 2 fields" is correct and not helpful when the real fault is one stray quote, and the report for a quote error is always about the record that swallowed the rest of the file. When a validator complains about a record that looks fine, look at the line before it for an odd number of quotes.

When validation belongs in your own code

Use a standalone validator for a first look, for a CI check on a file in a repository and for a provider's sample. Inside a loader, validate with the loader's own parser, because a file that passes a different tool's parser can still fail yours, and the checks you need (types, ranges, keys, foreign keys) are specific to your tables. The sensible arrangement is both: a structural check at the door that fails fast with a clear message, then the loader's typed parse, then the business checks, with each stage logging what it rejected (logging rejected rows from a CSV import for review). The tools' own documentation is the place to look for options: the qsv repository, csvkit's csvclean page, the Frictionless framework and the CSVW primer.