SOP for validating xSV files

[ialarmedalien]

I'm putting together a generic xSV validation script that uses various qsv commands, and was wondering whether there were best practices around dealing with files that have missing or extra columns in some rows. I know about fixlengths, but I would prefer to just remove the erroneous lines for human attention instead of potentially messing up the data for a row and sending that dodgy data on for further processing.

Since the file I'm dealing with is TSV, the easiest approach is to use a command line tool (e.g. sed, awk) to pick out lines with the incorrect number of tabs and send them to an errors.tsv file and the other lines to valid.tsv.

1. Have I missed an obvious way to do this with qsv? I could run fixlengths and do something involving diffing the file against the original, but that seems convoluted / inelegant.
2. How are other qsv users handling the problem of raggedy xSVs?

[Samielakkad]

Short answer: I don't think there is currently a single qsv command that quarantines structurally ragged records without changing them.

qsv validate input.tsv will detect the inconsistent field count in RFC 4180 mode, but it stops at the first parser error. The .valid / .invalid split belongs to JSON Schema validation after the CSV has been parsed. In the current implementation, a ragged record fails at read_byte_record() before split_invalid_records() can run. fixlengths is the command that deliberately enables a flexible reader, but, as you noted, it transforms the data.

For a generic xSV preflight, I would therefore split with a CSV-aware reader before passing valid.tsv to the rest of the qsv pipeline:

import csv
import json

with (
    open("input.tsv", newline="", encoding="utf-8-sig") as src,
    open("valid.tsv", "w", newline="", encoding="utf-8") as valid,
    open("errors.tsv", "w", newline="", encoding="utf-8") as errors,
):
    reader = csv.reader(src, delimiter="\t")
    good = csv.writer(valid, delimiter="\t")
    bad = csv.writer(errors, delimiter="\t")

    header = next(reader)
    expected = len(header)
    good.writerow(header)
    bad.writerow(["record", "expected_fields", "actual_fields", "row_json"])

    for record_number, row in enumerate(reader, 2):
        if len(row) == expected:
            good.writerow(row)
        else:
            bad.writerow(
                [record_number, expected, len(row), json.dumps(row, ensure_ascii=False)]
            )

That preserves quoted tabs and quoted multiline fields correctly. The record_number is a logical CSV record number, not necessarily a physical line number when multiline fields exist.

If the input contract guarantees no quoting, embedded tabs, or embedded newlines, then the simpler line-oriented version is fine:

awk -F '\t' '
NR == 1 { n = NF; print > "valid.tsv"; next }
NF == n { print > "valid.tsv"; next }
        { print > "errors.tsv" }
' input.tsv

I would not use that awk version as a generic xSV validator because counting delimiters cannot distinguish separators from quoted data.

So my SOP would be: structural quarantine first with a CSV-aware reader, then run qsv validate / schema validation and the rest of the qsv pipeline only on valid.tsv. A future validate option that keeps reading structurally invalid records and emits them separately would remove the need for this preflight.

[jqnatividad]

Thanks @Samielakkad for jumping in.

Adding the feature to validate so that qsv can do similar to valid/invalid for ragged records for both RFC 4180 and JSON Schema modes.