Generic xSV validation script

[ialarmedalien]

Numerous data sources use CSV or TSV as a data format, so it would be good to have a validation script to run over these files prior to processing them further.

Variables to account for:

• different separators (tab, comma, etc.)
• other important characters: escape, comment, quotes, line break
• nulls may be indicated by a variety of text strings (null, NULL, none, n/a, na, etc.) or by a blank
• character encoding
• files may or may not have a header row, which may or may not be commented
• files may have duplicate lines
• file size may vary from a few lines to multi-GB monsters

Let's assume that someone has looked at the xSV file, read the docs, and knows what the file should look like. They have generated a JSON schema for the CSV file to enable validation of the CSV.

Suggested validation steps

Suggested steps for xSV file validation using qsv, which is installed in the docker container.

1. Strip out headers, comments, and any surplus whitespace; replace any dodgy characters; save resulting file as CSV.

qsv input \
  --delimiter $delimiter \
  --auto-skip \
  --trim-headers \
  --trim-fields \
  --encoding-errors replace \
  $source_file > cleaned_file.csv

2. Replace any textual representations of NULL with an empty space

qsv replace -i '^(na|None|none|null|etc.)$' '' cleaned_file.csv > nulled_cleaned_file.csv

3. Sort and dedupe using an appropriate column

qsv sort --select 'col1,col2,col3' nulled_cleaned_file.csv > sorted.csv
qsv dedup --sorted --select 'col1' --dupes-output dupes.csv sorted.csv > clean.csv

(use extsort and extdedup for large files)

4. Generate an index for the file

qsv index clean.csv

5. Validate

# validate the schema itself to ensure it's OK
qsv validate schema schema_for_csv.schema.json

# validate the clean csv file
qsv validate --no-headers clean.csv schema_for_csv.schema.json

# When everything passes:
# - exit code 0
# - no extra files produced

# When some rows fail:
# - exit code non-zero
# - clean.csv.valid.csv — rows that passed
# - clean.csv.invalid.csv — rows that failed
# - clean.csv.validation-errors.csv — one row per error: row_number, field, error

6. Add a header row to the file

The header row can be generated from the field names specified in the JSON schema

• there is no qsv command for this, so it may have to be done using python or python + shell

Other useful bits and bobs

Some of these may be useful to add to other scripts -- e.g. might be helpful to have a script that does some general sniffing, schema derivation, and stats on a generic input file.

guess file settings (delimiter, comments, etc.)

qsv sniff some_file.txt

generate a schema from the input:

qsv schema clean.csv

diff csv files:

qsv diff file1.csv file2.csv

generate stats:

qsv stats some_data.csv