Offline CSV validator

Question

I have many bash scripts converting various sources of data to CSV.
The scripts often have bugs, so I would like to add a validation step.

Is there a tool to validate CSV file?

Requirements:

• Free,
• Command-line,
• Usable on Linux,
• Works offline,
• The tool should at least make sure that the CSV respects rules that will make it loadable in Excel and Calc.

It is OK if the tool requires for instance ; and not ,, I can adapt. All of my values are inside quotes.

Answer 1

You could use the frictionless cli and in particular the validate command:

https://framework.frictionlessdata.io/docs/guides/validation-guide/

The standard syntax of the command is

frictionless validate input.csv

and you have back simply

# -----
# valid: input.csv
# -----

It's very useful to make analysis in bulk to have json validation output. This is an example.

It's not necessary to define validation rules, it does a set of classic tests for this file format.

But you can define also a set of rules, to check the CSV in a way you define.

Answer 2

It's somewhat unclear how far your requirements stretch, but in general most scripting languages have a CSV parser of their own, and can be used from the command line. If there's an error in your CSV file, a parser should find it.

For example:

echo "one, two" | ruby -r csv -e 'CSV.parse(STDIN.read)'

results in the script producing no output and an exit code of 0

Whereas:

echo 'one, "two' | ruby -r csv -e 'CSV.parse(STDIN.read)'

Produces an exit code of 1 and the output:

/usr/lib/ruby/1.9.1/csv.rb:1925:in `block (2 levels) in shift': Illegal quoting in line 1. (CSV::MalformedCSVError)
    from /usr/lib/ruby/1.9.1/csv.rb:1887:in `each'
    from /usr/lib/ruby/1.9.1/csv.rb:1887:in `block in shift'
    from /usr/lib/ruby/1.9.1/csv.rb:1849:in `loop'
    from /usr/lib/ruby/1.9.1/csv.rb:1849:in `shift'
    from /usr/lib/ruby/1.9.1/csv.rb:1791:in `each'
    from /usr/lib/ruby/1.9.1/csv.rb:1805:in `to_a'
    from /usr/lib/ruby/1.9.1/csv.rb:1805:in `read'
    from /usr/lib/ruby/1.9.1/csv.rb:1379:in `parse'
    from -e:1:in `<main>'

I don't know if its requirements are the same as Excel's.

Answer 3

Take a look at csvkit, a python library for working with CSV data with accompanying command line scripts. It's designed to be smart about character encoding (and can be used to convert or normalize your encodings), and since it uses Python's csv module, it's pretty reliable in terms of reading and writing according to spec.

It's designed with UNIX principles in mind, so it's well suited to pipes and the like. A simple test might be to run your data through csvcut -n [filename] | wc -l and verify that it has the expected number of columns.

The csvstat tool will infer a data type for each column, which could be useful for validation. Parsing its output is less straightforward, although as a sometime contributor to the library, I'd hazard that a pull-request offering a command line switch to get a different kind of output from csvstat that is easier to parse would definitely be considered. (Maybe it would make sense as a different command—this isn't something I've thought that much about.)

Answer 4

You could use the bash itself to write a good validator for CSV file.
You can refer to this previous post. It explains how to use "awk" command to realize a validator for fields and for the file itself