4

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.

4 Answers 4

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.

3
  • 1
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Mar 16 at 7:42
  • 1
    Looks cool! To make your answer valid per our site rules, would you mind adding a few details? Also, can it check any CSV file's syntax without knowing beforehand the column names/formats?
    – Nicolas Raoul
    Mar 19 at 8:50
  • @NicolasRaoul I have added some detail, thank you
    – aborruso
    Mar 19 at 9:13
3

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.

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.)

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.