# validate

> Validate CSV data [_blazingly-fast_](https://github.com/Stranger6667/jsonschema-rs?tab=readme-ov-file#performance "using jsonschema-rs - the fastest JSON Schema validator for Rust") using [JSON Schema Validation (Draft 2020-12)](https://json-schema.org/draft/2020-12/json-schema-validation.html) (e.g. _up to 780,031 rows/second_[^1] using [NYC's 311 schema](https://github.com/dathere/qsv/blob/master/resources/test/311_Service_Requests_from_2010_to_Present-2022-03-04.csv.schema.json) generated by the [`schema`](../../README.md#schema_deeplink) command) & put invalid records into a separate file along with a detailed validation error report. Supports several custom JSON Schema formats & keywords: * `currency` custom format with [ISO-4217](https://en.wikipedia.org/wiki/ISO_4217) validation * `dynamicEnum` custom keyword that supports enum validation against a CSV on the filesystem or a URL (http/https/ckan & dathere URL schemes supported) * `uniqueCombinedWith` custom keyword to validate uniqueness across multiple columns for composite key validation. If no JSON schema file is provided, validates if a CSV conforms to the [RFC 4180 standard](../../README.md#rfc-4180-csv-standard) and is UTF-8 encoded.

**[Table of Contents](TableOfContents.md)** | **Source: [src/cmd/validate.rs](https://github.com/dathere/qsv/blob/master/src/cmd/validate.rs)** | [📇](TableOfContents.md#legend "uses an index when available.")[🗄️](TableOfContents.md#legend "Extended input support.")[🚀](TableOfContents.md#legend "multithreaded even without an index.")[🌐](TableOfContents.md#legend "has web-aware options.")[📚](TableOfContents.md#legend "has lookup table support, enabling runtime \"lookups\" against local or remote reference CSVs.") [![CKAN](../images/ckan.png)](TableOfContents.md#legend "has CKAN-aware integration options.")

<a name="nav"></a>
[Description](#description) | [Examples](#examples) | [Usage](#usage) | [Arguments](#arguments) | [Validate Options](#validate-options) | [Fancy Regex Options](#fancy-regex-options) | [Options For Both Regex Engines](#options-for-both-regex-engines) | [Email Validation Options](#email-validation-options) | [Common Options](#common-options)

<a name="description"></a>

## Description [↩](#nav)

Validates CSV data using two main modes:  

### JSON Schema Validation Mode:

This mode is invoked if a JSON Schema file (draft 2020-12) is provided.

The CSV data is validated against the JSON Schema. If the CSV data is valid, no output
files are created and the command returns an exit code of 0.

If invalid records are found, they are put into an "invalid" file, with the rest of the
records put into a "valid"" file.

A "validation-errors.tsv" report is also created with the following columns:  

* row_number: the row number of the invalid record
* field: the field name of the invalid field
* error: a validation error message detailing why the field is invalid

It uses the JSON Schema Validation Specification (draft 2020-12) to validate the CSV.
It validates the structure of the file, as well as the data types and domain/range of the fields.
See <https://json-schema.org/draft/2020-12/json-schema-validation.html>

qsv supports a custom format - `currency`. This format will only accept a valid currency, defined as:  

1. ISO Currency Symbol (optional): This is the ISO 4217 three-character code or currency symbol
(e.g. USD, EUR, JPY, $, €, ¥, etc.)
2. Amount: This is the numerical value of the currency. More than 2 decimal places are allowed.
3. Formats: Valid currency formats include:
Standard: $1,000.00 or USD1000.00
Negative amounts: ($100.00) or -$100.00
Different styles: 1.000,00 (used in some countries for euros)

```console
qsv also supports two custom keywords - `dynamicEnum` and `uniqueCombinedWith`.
```


### Dynamicenum

`dynamicEnum` allows for dynamic validation against a reference CSV file.
It can be used to validate against a set of values unknown at the time of schema creation or
when the set of valid values is dynamic or too large to hardcode into the JSON Schema with `enum`.
The reference CSV file can be local or a URL (http/https, dathere & ckan schemes supported).
The "dynamicEnum" value has the form:  

// qsvlite binary variant only supports URIs which can be files on the local filesystem
// or remote files (http and https schemes supported)
dynamicEnum = "URI|colname" where colname is the column name or column index (0-based)

// use data.csv from the current working directory; use the 1st column for validation
dynamicEnum = "data.csv"

// use data.csv in /lookup_dir directory; use the column "Agency" for validation
dynamicEnum = "/lookupdir/data.csv|Agency"

// get data.csv; use the 3rd column for validation (2 as the col index is 0-based)
dynamicEnum = "<https://example.com/data.csv|2">

// on other qsv binary variants, dynamicEnum has expanded caching functionality
dynamicEnum = "[cache_name;cache_age]|URI|colname" where cache_name and cache_age are optional

// use data.csv from current working directory; cache it as data with a default
// cache age of 3600 seconds i.e. the cached data.csv expires after 1 hour
dynamicEnum = "data.csv"

// get data.csv; cache it as custom_name, cache age 600 seconds
dynamicEnum = "custom_name;600|<https://example.com/data.csv">

// get data.csv; cache it as data, cache age 800 seconds
dynamicEnum = ";800|<https://example.com/data.csv">

// get the top matching result for nyc_neighborhoods (signaled by trailing ?),
// cache it as nyc_neighborhood_data.csv (NOTE: cache name is required when using CKAN scheme)
// with a default cache age of 3600 seconds
// be sure to set --ckan-api, otherwise it will default to datHere's CKAN (data.dathere.com)
dynamicEnum = "nyc_neighborhood_data|ckan:://nyc_neighborhoods?"

// get CKAN resource with id 1234567, cache it as resname, 3600 secs cache age
// note that if the resource is a private resource, you'll need to set --ckan-token
dynamicEnum = "resname|ckan:://1234567"

// same as above but with a cache age of 100 seconds; use the borough column for validation
dynamicEnum = "resname;100|ckan:://1234567|borough

// get us_states.csv from datHere lookup tables
dynamicEnum = "dathere://us_states.csv"

If colname is not specified, the first column of the CSV file is read and used for validation.

### Uniquecombinedwith

`uniqueCombinedWith` allows you to validate that combinations of values across specified columns
are unique. It can be used with either column names or column indices (0-based). For example:  

// Validate that combinations of name and email are unique
uniqueCombinedWith = ["name", "email"]

// Validate that combinations of columns at indices 1 and 2 are unique
uniqueCombinedWith = [1, 2]

// Validate that the combinations of named and indexed columns are unique
uniqueCombinedWith = ["name", 2]

When a duplicate combination is found, the validation will fail and the error message will indicate
which columns had duplicate combinations (named columns first, then indexed columns). The invalid
records will be written to the .invalid file, while valid records will be written to the .valid file.

`uniqueCombinedWith` complements the standard `uniqueItems` keyword, which can only validate
uniqueness across a single column.

---

You can create a JSON Schema file from a reference CSV file using the `qsv schema` command.
Once the schema is created, you can fine-tune it to your needs and use it to validate other CSV
files that have the same structure.

Be sure to select a "training" CSV file that is representative of the data you want to validate
when creating a schema. The data types, domain/range and regular expressions inferred from the
reference CSV file should be appropriate for the data you want to validate.

Typically, after creating a schema, you should edit it to fine-tune each field's inferred
validation rules.

For example, if we created a JSON schema file called "reference.schema.json" using the `schema` command.
And want to validate "mydata.csv" which we know has validation errors, the output files from running
`qsv validate mydata.csv reference.schema.json` are:  

* mydata.csv.valid
* mydata.csv.invalid
* mydata.csv.validation-errors.tsv

With an exit code of 1 to indicate a validation error.

If we validate another CSV file, "mydata2.csv", which we know is valid, there are no output files,
and the exit code is 0.

If piped from stdin, the filenames will use `stdin.csv` as the base filename. For example:  
`cat mydata.csv | qsv validate reference.schema.json`

* stdin.csv.valid
* stdin.csv.invalid
* stdin.csv.validation-errors.tsv

JSON SCHEMA SCHEMA VALIDATION SUBMODE:  
---

`validate` also has a `schema` subcommand to validate JSON Schema files themselves. E.g.
`qsv validate schema myjsonschema.json`
// ignore format validation
`qsv validate schema --no-format-validation myjsonschema.json`

### Rfc 4180 Validation Mode:

If run without a JSON Schema file, the CSV is validated for RFC 4180 CSV standard compliance
(see <https://github.com/dathere/qsv#rfc-4180-csv-standard>).

It also confirms if the CSV is UTF-8 encoded.

For both modes, returns exit code 0 when the CSV file is valid, exitcode > 0 otherwise.
If all records are valid, no output files are produced.


<a name="examples"></a>

## Examples [↩](#nav)

> Validate a CSV file. Use this to check if a CSV file is readable by qsv.

```console
qsv validate data.csv
```

> Quarantine rows with the wrong number of columns instead of aborting.
> Well-formed rows go to data.csv.valid; ragged rows go to data.csv.invalid.

```console
qsv validate --split-ragged data.csv
```

> Skip '#'-prefixed comment lines while quarantining ragged rows.
> validate has no parser-config options of its own, but it honors the global
> QSV_COMMENT_CHAR environment variable in BOTH validation modes. Commented lines
> (including a commented header) are skipped - they are NOT written to the .valid file.

```console
QSV_COMMENT_CHAR='#' qsv validate --split-ragged data.tsv
```

> For CSV dialects validate cannot express (custom quote/escape characters,
> preamble/epilogue lines, non-UTF-8 encodings), normalize with `qsv input` FIRST,
> then validate the normalized output. Note that `qsv input` aborts on ragged rows,
> so this ordering only works on files that are not ragged.

```console
qsv input --quote "'" --escape '\' --comment '#' data.tsv -o normalized.csv
```

```console
qsv validate normalized.csv
```

> Validate a TSV file against a JSON Schema

```console
qsv validate data.tsv schema.json
```

> Validate multiple CSV files using various dialects against a JSON Schema

```console
qsv validate data1.csv data2.tab data3.ssv schema.json
```

> Validate all CSV files in a directory against a JSON Schema

```console
qsv validate /path/to/csv_directory schema.json
```

> Validate CSV files listed in a '.infile-list' file against a JSON Schema

```console
qsv validate files.infile-list schema.json
```

For more examples, see the tests included in this file (denoted by '#[test]') or see

<https://github.com/dathere/qsv/blob/master/tests/test_validate.rs>.
See also <https://github.com/dathere/qsv/wiki/Validation-and-Schema#validate>

<a name="usage"></a>

## Usage [↩](#nav)

```console
qsv validate schema [--no-format-validation] [<json-schema>]
qsv validate [options] [<input>...]
qsv validate [options] [<input>] <json-schema>
qsv validate --help
```

<a name="arguments"></a>

## Arguments [↩](#nav)

| &nbsp;&nbsp;&nbsp;Argument&nbsp;&nbsp;&nbsp;&nbsp; | Description |
|----------|-------------|
| &nbsp;`<input>`&nbsp; | Input CSV file(s) to validate. If not provided, will read from stdin. If input is a directory, all files in the directory will be validated. If the input is a file with a '.infile-list' extension, the file will be read as a list of input files. If the input are snappy-compressed files(s), it will be decompressed automatically. Extended Input Support is only available for RFC 4180 validation mode. |
| &nbsp;`<json-schema>`&nbsp; | JSON Schema file to validate against. If not provided, `validate` will run in RFC 4180 validation mode. The file can be a local file or a URL (http and https schemes supported). |

<a name="validate-options"></a>

## Validate Options [↩](#nav)

| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Option&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Type | Description | Default |
|--------|------|-------------|--------|
| &nbsp;`‑‑trim`&nbsp; | flag | Trim leading and trailing whitespace from fields before validating. |  |
| &nbsp;`‑‑no‑format‑validation`&nbsp; | flag | Disable JSON Schema format validation. Ignores all JSON Schema "format" keywords (e.g. date,email, uri, currency, etc.). This is useful when you want to validate the structure of the CSV file w/o worrying about the data types and domain/range of the fields. |  |
| &nbsp;`‑‑fail‑fast`&nbsp; | flag | Stops on first error. |  |
| &nbsp;`‑‑valid`&nbsp; | string | Valid record output file suffix. | `valid` |
| &nbsp;`‑‑invalid`&nbsp; | string | Invalid record output file suffix. | `invalid` |
| &nbsp;`‑‑split‑ragged`&nbsp; | flag | Opt-in mode: instead of aborting on the first row with the wrong number of columns (a "ragged" row), write well-formed rows to the '<input>.<valid>' file and the ragged rows to the '<input>.<invalid>' file, with a report appended to '<input>.validation-errors.tsv'. Returns a non-zero exit code when any ragged row is found. Only column-count mismatches are diverted; malformed quoting and non-UTF-8 data still abort validation. Works in both RFC 4180 and JSON Schema validation modes. The split files are written next to each input file, so this is intended for on-disk file input(s). |  |
| &nbsp;`‑‑json`&nbsp; | flag | When validating without a JSON Schema, return the RFC 4180 check as a JSON file instead of a message. |  |
| &nbsp;`‑‑pretty‑json`&nbsp; | flag | Same as --json, but pretty printed. |  |
| &nbsp;`‑‑valid‑output`&nbsp; | string | Change validation mode behavior so if ALL rows are valid, to pass it to output, return exit code 1, and set stderr to the number of valid rows. Setting this will override the default behavior of creating a valid file only when there are invalid records. To send valid records to stdout, use `-` as the filename. |  |
| &nbsp;`‑j,`<br>`‑‑jobs`&nbsp; | integer | The number of jobs to run in parallel. When not set, the number of jobs is set to the number of CPUs detected. |  |
| &nbsp;`‑b,`<br>`‑‑batch`&nbsp; | integer | The number of rows per batch to load into memory, before running in parallel. Automatically determined for CSV files with more than 50000 rows. Set to 0 to load all rows in one batch. Set to 1 to force batch optimization even for files with less than 50000 rows. | `50000` |

<a name="fancy-regex-options"></a>

## Fancy Regex Options [↩](#nav)

| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Option&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Type | Description | Default |
|--------|------|-------------|--------|
| &nbsp;`‑‑fancy‑regex`&nbsp; | flag | Use the fancy regex engine instead of the default regex engine for validation. The fancy engine supports advanced regex features such as lookaround and backreferences, but is not as performant as the default regex engine which guarantees linear-time matching, prevents DoS attacks, and is more efficient for simple patterns. |  |
| &nbsp;`‑‑backtrack‑limit`&nbsp; | integer | Set the approximate number of backtracking steps allowed. This is only used when --fancy-regex is set. | `1000000` |

<a name="options-for-both-regex-engines"></a>

## Options For Both Regex Engines [↩](#nav)

| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Option&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Type | Description | Default |
|--------|------|-------------|--------|
| &nbsp;`‑‑size‑limit`&nbsp; | integer | Set the approximate size limit, in megabytes, of a compiled regex. | `50` |
| &nbsp;`‑‑dfa‑size‑limit`&nbsp; | integer | Set the approximate capacity, in megabytes, of the cache of transitions used by the engine's lazy Discrete Finite Automata. | `10` |
| &nbsp;`‑‑timeout`&nbsp; | integer | Timeout for downloading json-schemas on URLs and for 'dynamicEnum' lookups on URLs. If 0, no timeout is used. | `30` |
| &nbsp;`‑‑cache‑dir`&nbsp; | string | The directory to use for caching downloaded dynamicEnum resources. If the directory does not exist, qsv will attempt to create it. If the QSV_CACHE_DIR envvar is set, it will be used instead. Not available on qsvlite. | `~/.qsv-cache` |
| &nbsp;`‑‑ckan‑api`&nbsp; | string | The URL of the CKAN API to use for downloading dynamicEnum resources with the "ckan://" scheme. If the QSV_CKAN_API envvar is set, it will be used instead. Not available on qsvlite. | `https://data.dathere.com/api/3/action` |
| &nbsp;`‑‑ckan‑token`&nbsp; | string | The CKAN API token to use. Only required if downloading private resources. If the QSV_CKAN_TOKEN envvar is set, it will be used instead. Not available on qsvlite. |  |

<a name="email-validation-options"></a>

## Email Validation Options [↩](#nav)

| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Option&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Type | Description | Default |
|--------|------|-------------|--------|
| &nbsp;`‑‑email‑required‑tld`&nbsp; | flag | Require the email to have a valid Top-Level Domain (TLD) (e.g. .com, .org, .net, etc.). e.g. "john.doe@example" is VALID if this option is NOT set. |  |
| &nbsp;`‑‑email‑display‑text`&nbsp; | flag | Allow display text in emails. e.g. "John Doe <john.doe@example.com>" is INVALID if this option is NOT set. |  |
| &nbsp;`‑‑email‑min‑subdomains`&nbsp; | integer | Minimum number of subdomains required in the email. e.g. "jdoe@example.com" is INVALID if this option is set to 3, but "jdoe@sub.example.com" is VALID. | `2` |
| &nbsp;`‑‑email‑domain‑literal`&nbsp; | flag | Allow domain literals in emails. e.g. "john.doe@[127.0.0.1]" is VALID if this option is set. |  |

<a name="common-options"></a>

## Common Options [↩](#nav)

| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Option&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Type | Description | Default |
|--------|------|-------------|--------|
| &nbsp;`‑h,`<br>`‑‑help`&nbsp; | flag | Display this message |  |
| &nbsp;`‑n,`<br>`‑‑no‑headers`&nbsp; | flag | When set, the first row will not be interpreted as headers. It will be validated with the rest of the rows. Otherwise, the first row will always appear as the header row in the output. Note that this option is only valid when running in RFC 4180 validation mode as JSON Schema validation requires headers. |  |
| &nbsp;`‑d,`<br>`‑‑delimiter`&nbsp; | string | The field delimiter for reading CSV data. Must be a single character. |  |
| &nbsp;`‑p,`<br>`‑‑progressbar`&nbsp; | flag | Show progress bars. Not valid for stdin. |  |
| &nbsp;`‑q,`<br>`‑‑quiet`&nbsp; | flag | Do not display validation summary message. |  |

---
**Source:** [`src/cmd/validate.rs`](https://github.com/dathere/qsv/blob/master/src/cmd/validate.rs)
| **[Table of Contents](TableOfContents.md)** | **[README](../../README.md)**
