CSV Validator 1.1

Fail on first error

With this setting selected, validation will terminate when the first Validation Error is encountered in the CSV file. If there are no validation errors the whole CSV file will be validated and return PASS. You should be aware that the CSV Schema can declare that some validation tests will produce only validation warnings, rather than validation errors. A validation warning will not stop validation, even with "Fail on first error" set. This setting is equivalent to using the -f true or --failfast true options when running via the Command Line.

Enforce case-sensitive filepath checks

With this setting selected, validation treats filepaths as being case sensitive, even if the file system itself is not (e.g. on Windows NTFS). Without doing this data which appears to validate on a Windows machine may then fail when further processing is carried out on a system where the file system is case sensitive (e.g. Unix/Linux). This setting is equivalent to using the -c true or --case-sensitive-paths true options when running via the Command Line.

UTF-8 Validation

This setting is on by default, meaning that the validator also verifies that the CSV file appears to contain only UTF-8 characters. It is ignored if CSV Encoding has been changed to something other than UTF-8, and can otherwise be disabled by unticking the checkbox. This setting is equivalent to using the --disable-utf8-validation option when running via the Command Line.

Path Substitutions

Path Substitutions are a more slippery concept to grasp. As described in the Background section above, the original use case at The National Archives was for a tool to validate metadata files. These files generally describe a set of digital files being transferred to The National Archives and so contain information, including a filepath and checksum, about each file in a particular transfer. In addition to checking that the metadata files are correctly structured, we wish to check that the file actually exists at the filepath specified, and that when recalculated, the checksum for the files matches the value supplied in the CSV file. To this end, the CSV Schema language contains a set of so-called External Single Expressions, which allow reference to other file system resources outside the CSV file being validated. However, it is likely that after transfer the drive letter or mount point (at least) will have changed from the original. Path Substitutions allow us to overcome this at validation time by effectively performing a simple find-and-replace within the file during validation which maps the supplied filepath to the equivalent on the system where validation is actually taking place. It is also important to note that the CSV Validator expects filepaths to be supplied as file URIs (so on Windows systems, you may need to reverse the slashes).

Many path substitutions can be supplied to allow for the case where the content files are in many different locations.

Creating Path Substitutions

To create a Path Substitution in the GUI, click the "Add Path Substitution..." button (highlighted):

This will open a popup:

in the first box enter the text string which will be found in the supplied CSV file, and in the second the text string which should be substituted. So considering the examples already used in this document, the CSV file is found at Y:\UAT_2\ADM362B003\tech_acq_metadata_v1_ADM362B003.csv and we can see that there is a folder named ADM_362 alongside the CSV file, that folder contains the images which the CSV file describes. When created, the metadata CSV file and images were on the C: drive, instead of the Y: drive, and they are also now in a project-related folder structure. So the filepaths in the metadata CSV file all begin file:///c:/ADM_362/ - when we run the validator, we actually need to look for the content files at file:///y:/UAT_2/ADM362B003/ADM_362/ when we are checking for their existence and verifying the associated checksums. So we enter those filepath fragments into the "From:" and "To:" boxes respectively, and click OK. This gives:

Creating a Path Substitution in this way is equivalent to running via the Command Line with the -p or --path flags and supplying a key:value pair.

Editing or Removing Path Substitutions

To edit a substitution, simply double-click on the field you wish to edit:

Hit <Enter> or <Tab>, or click on the other side of the substitution, to complete the edit.

To completely remove a path substitution, right-click on an existing path substitution definition, this will give a context menu with a single option, "Remove Path Substitution", clicking that will delete the path substitution definition.

Schema Errors

If you are developing schemas, it is possible to make syntax errors in the schema, for example a mismatch in the declared number of columns (using the Total Columns Directive) against the actual number of Column Definitions included in the schema (eg @totalColumns = 41 but number of columns defined = 42 at line: 2, column: 1). The schema itself is always checked before validation of the data begins, and schema errors always terminate validation.

Or, if the Version Declaration has been omitted from the schema, or is incorrect, you will see a schema error saying that the Version Declaration is not present (eg [1.1] failure: version 1.0 missing or incorrect). You will also see this if you accidentally switch over the input filepaths in the validator, and put the filepath for the CSV data file into the field for the schema and vice versa:

Validation Errors

When Validation Errors occur they will be reported in the output pane on the completion of validation. The report will be prefixed Error: and will then show the Validation Rule where the error has occurred, the line and data column from the CSV file where the error has occurred, the actual data present in the file, and for rules such as checksum validation, what the computed value of the data was. The line number refers to data lines only, so if the CSV file contains a header row, you may see an apparent discrepancy in the line numbers displayed when you view the data in a text editor or spreadsheet program, compared to the line number indicated by the csv-validator.

Error:   checksum(file($file_path), "SHA-256") file "file:///TEST_1/1/1/1_1_001.xml" checksum match fails for line: 1, column: file_checksum, value: "fb58b56a17af0f52cf794c108e0c1574a3a2c02b25e22699668bb43801028432". Computed checksum value:"fb58b56a17af0f52cf794c108e0c1574a3a2c02b25e22699668bb43801028431"
FAIL
						

The next image shows a more varied selection of Validation Errors, and demonstrates that the basic format of the error messages is consistent:

The full text of each error message is shown below, the errors are: a checksum mismatch, note that both the checksum read from the CSV file, and the actual computed checksum value are shown; then a regex error, the regex shown only allows the number before .xml to go up to 010, rather than value of 011 that has been put in the CSV file; the concatenation of the various strings and field values shown does not match the value actually found in the CSV file (in this instance you would have to manually check the CSV file to see which field is in error); the image_width value is outside the specified range; and finally, an incorrect value ahs been supplied for image_format.

Error:   checksum(file($file_path), "SHA-256") file "file:///TEST_1/2/2/2_2_010.xml" checksum match fails for line: 40, column: file_checksum, value: "f7ab46b417eecb57c43e62996646780c1e9e2aa7da4af361dc4616c5aff26f65". Computed checksum value:"d59a033ebd100b08f1c93a0475c9f33f898bb13740fed61f8e86435d01a70e86"
Error:   regex(".*[12]_[12]_((00[1-9])|(010)).xml$") fails for line: 41, column: file_path, value: "file:///TEST_1/2/2/2_2_011.xml"
Error:   is(concat("http://datagov.nationalarchives.gov.uk/66/", $department, "/", $series, "/", $piece, "/", $item, "/", $file_uuid)) fails for line: 41, column: resource_uri, value: "http://datagov.nationalarchives.gov.uk/66/TEST/1/2/2/ab677099-950e-4853-b2d4-6d39dc1b8722"
Error:   range(2000,*) fails for line: 41, column: image_width, value: "234"
Error:   is("x-fmt/392") fails for line: 41, column: image_format, value: "x-fmt/391"
FAIL
						

Saving output as report

If you wish to save the validation output as a report, for example, in order to send it to whoever supplied the data so that they can correct it, simply click the "Save" button towards the bottom right of the CSV Validator window. If you have opened the settings pane, you may need to close it again, or move your mouse to the bottom of the window and drag downwards to expand it, in order to be able to see this button. This will open a standard save dialogue box. Save the output as a simple text file at your desired location.

Trace

The trace option produces terminal output describing how the CSV Schema has been parsed by the CSV Validator. Complex schemas have sometimes taken several minutes to parse, or even hung completely, in earlier versions of the CSV Validator. This output allows developers to understand where these problems have arisen in order to troubleshoot the parser.

This option is only available at the command line, and not via the Settings panel in the GUI version of the application.

Fail fast

To use the fail fast option, supply either of the flags -f or --fail-fast along with a (boolean) value. Using either true or 1 as the supplied value switches the option on. With this option in force, validation will be terminated when the first Validation Error is encountered.

By default (or if you supply false or 0 with this option flag) validation continues to the end of the CSV file being validated, and all validation errors are reported.

However, Schema Errors always cause immediate termination of validation, regardless of the use of this option.

Path substitutions

To use the path substitution option, supply either of the flags -p or --path along with a key=value pair. The reasons for using path substitutions are discussed in more detail in Path Substitutions section under the use of GUI. The key represents the string to be found in the CSV file being validated, the value the string to replace that with.

For the example path substitution given above, (in the Path Substitutions section under the use of GUI) the command line parameter would be entered as:

-p file:///c:/ADM_362/=file:///y:/UAT_2/ADM362B003/ADM_362/

Note that the paths are expressed as a URL, so characters (such as spaces) that are not permitted in URLs will need to be escaped (see Example CSV Schemas below).

Case sensitive paths

To use the case sensitive paths option, supply either of the flags -c or --case-sensitive-paths along with a (boolean) value. Using either true or 1 as the supplied value switches the option on. With this option in force, file paths used with Column Validation Expressions such as File Exists Expressions will be treated as being case sensitive even on operating systems where that would not normally be the case, such as Windows NTFS.

By default (or if you supply false or 0 with this option flag) the behaviour of the host operating system will be used.

This helps to avoid inconsistent validation results when validating the same CSV file and schema on different operating systems.

CSV File Encoding

To specify that the CSV file that you are validating uses a different character set encoding than the default (UTF-8 without byte order marker), supply either of the flags -x or --csv-encoding along with the name of the character set encoding.

CSV Schema File Encoding

To specify that the CSV Schema file that you are parsing uses a different character set encoding than the default (UTF-8 without byte order marker), supply either of the flags -y or --csv-schema-encoding along with the name of the character set encoding.

Disable UTF-8 Validation

By default the validator also verifies that the CSV file appears to contain only UTF-8 characters. UTF-8 validation is automatically disabled if the flags -x or --csv-encoding have been used to specify an encoding other than UTF-8. To disable UTF-8 validation for some other reason, use the flag --disable-utf8-validation (there is deliberately no short form for this flag)

Show Progress

Setting the flag --show-progress causes the validator to produce an output statement for each line of the CSV file, similar to processing 1 of 41. The first number represents the line number within the file (including any header row), while the second number represents the total number of lines in the file (including any header row). This is likely to be extended in future releases to allow finer grained control over the frequency of output, particularly for larger CSV files.

CSV Path

The file system path for the CSV to be validated, expressed in the standard manner for the operating system you are using.

CSV Schema Path

The file system path for the CSV Schema to be used for validation, expressed in the standard manner for the operating system you are using.