Are there any good sites/services to validate consistency of CSV file ?
The same as W3C validator but for CSV ?
Thanks!
asked Jul 18, 2011 at 20:27
Scherbius.comScherbius.com
3,3664 gold badges22 silver badges44 bronze badges
2
The Open Data Institute is developing a CSV validation service that will allow users to check the structure of their data as well as validate it against a simple schema.
The service is still very much in alpha but can be found here:
http://csvlint.io/
The code for the application and the underlying library are both open source:
https://github.com/theodi/csvlint
https://github.com/theodi/csvlint.rb
The README in the library provides a summary of the errors and warnings that can be generated. The following types of error can be reported:
:wrong_content_type— content type is not text/csv:ragged_rows— row has a different number of columns (than the first row in the file):blank_rows— completely empty row, e.g. blank line or a line where all column values are empty:invalid_encoding— encoding error when parsing row, e.g. because of invalid characters:not_found— HTTP 404 error when retrieving the data:quoting— problem with quoting, e.g. missing or stray quote, unclosed quoted field:whitespace— a quoted column has leading or trailing whitespace
The following types of warning can be reported:
:no_encoding— the Content-Type header returned in the HTTP request does not have a charset parameter:encoding— the character set is not UTF-8:no_content_type— file is being served without a Content-Type header:excel— no Content-Type header and the file extension is .xls:check_options— CSV file appears to contain only a single column:inconsistent_values— inconsistent values in the same column. Reported if <90% of values seem to have same data type (either numeric or alphanumeric including punctuation)
answered Feb 11, 2014 at 17:55
ldoddsldodds
2492 silver badges4 bronze badges
1
The National Archives developed a CSV Schema Language and CSV Validator, software written in Java. It’s open source.
answered Aug 7, 2016 at 12:05
MilosMilos
1923 silver badges11 bronze badges
To validate a CSV file I use the RAINBOW CSV extension in Visual Studio Code and also I open the CSV file in Excel.
answered Feb 15, 2018 at 16:18
mruanovamruanova
5,7016 gold badges34 silver badges53 bronze badges
There is a great way to validate your CSV file.I am referring to this article, where the whole process is explained in tiniest details.
The validation process has two steps: the first one is to post the file to the API. Once your file is accepted,the API returns a polling endpoint that contains the results of the validation process.10 MB limit per file.
answered Feb 5, 2020 at 23:45
monkrusmonkrus
1,33724 silver badges23 bronze badges
CSV Lint at csvlint.com (not .io 🙂 is a service we’re building to solve this problem. It checks CSV files against user-defined validation rules / schemas cell by cell.
We spent a lot of time tweaking the UI to allow users to create complex validation rules / schemas easily that meet their business needs without a single line of code.
Our offline validation feature allows users to see the results in-realtime even when validating multiple large size (with millions+ rows) files, and most importantly it 100% protects user data privacy.
answered Jun 17, 2018 at 6:57
JoeJoe
2791 gold badge4 silver badges15 bronze badges
1
Are there any good sites/services to validate consistency of CSV file ?
The same as W3C validator but for CSV ?
Thanks!
asked Jul 18, 2011 at 20:27
Scherbius.comScherbius.com
3,3664 gold badges22 silver badges44 bronze badges
2
The Open Data Institute is developing a CSV validation service that will allow users to check the structure of their data as well as validate it against a simple schema.
The service is still very much in alpha but can be found here:
http://csvlint.io/
The code for the application and the underlying library are both open source:
https://github.com/theodi/csvlint
https://github.com/theodi/csvlint.rb
The README in the library provides a summary of the errors and warnings that can be generated. The following types of error can be reported:
:wrong_content_type— content type is not text/csv:ragged_rows— row has a different number of columns (than the first row in the file):blank_rows— completely empty row, e.g. blank line or a line where all column values are empty:invalid_encoding— encoding error when parsing row, e.g. because of invalid characters:not_found— HTTP 404 error when retrieving the data:quoting— problem with quoting, e.g. missing or stray quote, unclosed quoted field:whitespace— a quoted column has leading or trailing whitespace
The following types of warning can be reported:
:no_encoding— the Content-Type header returned in the HTTP request does not have a charset parameter:encoding— the character set is not UTF-8:no_content_type— file is being served without a Content-Type header:excel— no Content-Type header and the file extension is .xls:check_options— CSV file appears to contain only a single column:inconsistent_values— inconsistent values in the same column. Reported if <90% of values seem to have same data type (either numeric or alphanumeric including punctuation)
answered Feb 11, 2014 at 17:55
ldoddsldodds
2492 silver badges4 bronze badges
1
The National Archives developed a CSV Schema Language and CSV Validator, software written in Java. It’s open source.
answered Aug 7, 2016 at 12:05
MilosMilos
1923 silver badges11 bronze badges
To validate a CSV file I use the RAINBOW CSV extension in Visual Studio Code and also I open the CSV file in Excel.
answered Feb 15, 2018 at 16:18
mruanovamruanova
5,7016 gold badges34 silver badges53 bronze badges
There is a great way to validate your CSV file.I am referring to this article, where the whole process is explained in tiniest details.
The validation process has two steps: the first one is to post the file to the API. Once your file is accepted,the API returns a polling endpoint that contains the results of the validation process.10 MB limit per file.
answered Feb 5, 2020 at 23:45
monkrusmonkrus
1,33724 silver badges23 bronze badges
CSV Lint at csvlint.com (not .io 🙂 is a service we’re building to solve this problem. It checks CSV files against user-defined validation rules / schemas cell by cell.
We spent a lot of time tweaking the UI to allow users to create complex validation rules / schemas easily that meet their business needs without a single line of code.
Our offline validation feature allows users to see the results in-realtime even when validating multiple large size (with millions+ rows) files, and most importantly it 100% protects user data privacy.
answered Jun 17, 2018 at 6:57
JoeJoe
2791 gold badge4 silver badges15 bronze badges
1
CSV validator
A CLI tool to validate a CSV file against a set of rules defined with JSON Schema. It’s heavily inspired from csval, with some additions to match my needs.
Usage
Usage: csv-validator [options] <csvFile> <rulesFile> Validate a CSV file against a set of rules defined with JSON Schema. Options: -V, --version output the version number -a, --abort-early move to the next line as soon as an error is encountered -d, --dynamic-typing convert data into the appropriate type according to their format -e, --encoding <encoding> specify the encoding of the files (default: "utf8") -q, --quiet hide the list of errors encountered -s, --skip-empty-lines ignore empty lines in the CSV file -h, --help display help for command
Rules files
The rules files must follow the JSON Schema specification.
Basic example
Consider the following rules file.
{
"type": "object",
"properties": {
"brand": {
"type": "string"
},
"model": {
"type": "string"
},
"color": {
"type": "string"
},
"mileage": {
"type": "integer",
"minimum": 0
},
"sold": {
"type": "boolean"
}
}
}
Valid file
The following CSV file will pass validation.
brand,model,color,mileage,sold
Toyota,Prius,blue,45108,true
Opel,Zafira,red,2784,false
Nissan,Micra,green,98410,false
File "cars.csv" passes validation checks.
Inconsistent number of fields
The following CSV file will fail validation because the number of fields is inconsistent across rows.
brand,model,color,mileage,sold
Toyota,Prius,blue,45108,true
Opel,red,2784,false
Nissan,Micra,green,98410,false
Error at row 2: Too few fields: expected 4 fields but parsed 3.
File "cars.csv" fails validation checks.
Schema violation
The following CSV file will fail validation because some data violates the rules.
brand,model,color,mileage,sold
Toyota,Prius,blue,-14,true
Opel,Zafira,red,2784,false
Nissan,Micra,green,98410,false
Error at row 1: Field "mileage" must be greater than or equal to 0. File "cars.csv" fails validation checks.
Advanced examples
Nullable types
Consider the following rules file.
{
"type": "object",
"properties": {
"name": {
"type": "string"
},
"race": {
"type": "string"
},
"chip_number": {
"oneOf": [
{ "type": "null" },
{ "type": "integer" }
]
}
}
}
The following CSV file should pass validation, because data seems to match the rules.
name,race,chip_number
Bounty,Golden Retriever,
Ritsuka,Akita,1784647826
But in practice it will fail.
Error at row 1: Field "chip_number" does not match any of the allowed types. File "dogs.csv" fails validation checks.
This is because the CSV format does not have a standard method to represent null fields. Therefore empty fields are converted to empty string, not null values. To allow the use of the «null» type in the rule files, it is possible to use the -d or --dynamic-typing option, which will convert the data into the type that seems best suited to their representation.
The file then passes validation.
File "dogs.csv" passes validation checks.
Multiple errors
When validating large datasets, the number of error messages may quickly become unmanageable. It is then possible to use several options to limit the number of errors displayed.
Consider the following rules file and CSV file.
{
"type": "object",
"properties": {
"first_name": {
"type": "string"
},
"last_name": {
"type": "string"
},
"age": {
"type": "integer",
"minimum": 0
}
}
}
first_name,last_name,age
Stefania,Josh,23
Damiano,,unknown
Paulie,Niese,78
,Vasyutkin,
Marlena,,68
Caressa,Hanington,
Glenine,,72
Ilyssa,Kelling,48
Syd,,unk
Babara,Killcross,59
Without any options, the validation will fail with the following errors.
Error at row 2: Field "last_name" is not allowed to be empty. Error at row 2: Field "age" must be a number. Error at row 4: Field "first_name" is not allowed to be empty. Error at row 4: Field "age" must be a number. Error at row 5: Field "last_name" is not allowed to be empty. Error at row 6: Field "age" must be a number. Error at row 7: Field "last_name" is not allowed to be empty. Error at row 9: Field "last_name" is not allowed to be empty. Error at row 9: Field "age" must be a number. File "people.csv" fails validation checks.
As you can see, some lines contain several errors. Let’s assume now that you just want to know which lines contain errors. You can use the -a or --abort-early option, and the output will then look like this.
Error at row 2: Field "last_name" is not allowed to be empty. Error at row 4: Field "first_name" is not allowed to be empty. Error at row 5: Field "last_name" is not allowed to be empty. Error at row 6: Field "age" must be a number. Error at row 7: Field "last_name" is not allowed to be empty. Error at row 9: Field "last_name" is not allowed to be empty. File "people.csv" fails validation checks.
And if you just want to know whether a file is valid or not, you can use the -q or --quiet option. The output will then look like this.
File "people.csv" fails validation checks.
Empty lines
Now imagine that your CSV file is perfectly valid, but contains an additional empty line at the end. By default, validation will fail with a message like this.
Error at row 11: Too few fields: expected 3 fields but parsed 1.
File "people.csv" fails validation checks.
The -s or --skip-empty-lines option allows you to ignore empty lines within the file. The file is then again considered valid.
File "people.csv" passes validation checks.
7 ответы
Создан 19 янв.
Институт открытых данных разрабатывает службу проверки CSV, которая позволит пользователям проверять структуру своих данных, а также сверять их с простой схемой.
Сервис все еще находится в альфа-версии, но его можно найти здесь:
http://csvlint.io/
Код приложения и базовой библиотеки имеют открытый исходный код:
https://github.com/theodi/csvlint
https://github.com/theodi/csvlint.rb
README в библиотеке содержит сводку ошибок и предупреждений, которые могут быть сгенерированы. Могут быть зарегистрированы следующие типы ошибок:
:wrong_content_type— тип содержимого не text/csv:ragged_rows— строка имеет другое количество столбцов (чем первая строка в файле):blank_rows— полностью пустая строка, например, пустая строка или строка, в которой все значения столбца пусты:invalid_encoding— ошибка кодирования при анализе строки, например, из-за недопустимых символов:not_found— Ошибка HTTP 404 при получении данных:quoting— проблема с цитированием, например, отсутствующая или случайная кавычка, незакрытое поле в кавычках:whitespace— столбец в кавычках имеет начальные или конечные пробелы
Можно сообщать о следующих типах предупреждений:
:no_encoding— заголовок Content-Type, возвращаемый в HTTP-запросе, не имеет параметра charset:encoding— набор символов не UTF-8:no_content_type— файл обслуживается без заголовка Content-Type:excel— нет заголовка Content-Type и расширение файла .xls:check_options— Файл CSV содержит только один столбец:inconsistent_values— несовместимые значения в одном и том же столбце. Сообщается, если <90% значений имеют одинаковый тип данных (цифровой или буквенно-цифровой, включая знаки препинания)
Создан 11 фев.
Национальный архив разработал Язык схемы CSV и CSV-валидатор, программное обеспечение, написанное на Java. Это с открытым исходным кодом.
ответ дан 07 авг.
Для проверки CSV-файла я использую расширение RAINBOW CSV в Visual Studio Code, а также открываю CSV-файл в Excel.
Создан 15 фев.
Существует отличный способ проверить ваш файл CSV. Я имею в виду это гайд, где весь процесс описан в мельчайших деталях.
Процесс проверки состоит из двух шагов: первый — отправить файл в API. Как только ваш файл будет принят, API вернет конечную точку опроса, содержащую результаты процесса проверки. Ограничение 10 МБ на файл.
Создан 05 фев.
CSV-линт at csvlint.com (не .io 🙂 — это сервис, который мы создаем для решения этой проблемы. Он проверяет файлы CSV на соответствие определенным пользователем правилам/схемам проверки ячейка за ячейкой.
Мы потратили много времени на настройку пользовательского интерфейса, чтобы пользователи могли легко создавать сложные правила/схемы проверки, отвечающие их бизнес-потребностям, без единой строки кода.
Наша функция автономной проверки позволяет пользователям видеть результаты в режиме реального времени даже при проверке нескольких файлов большого размера (с миллионами и более строками), и, что наиболее важно, она на 100 % защищает конфиденциальность пользовательских данных.
Создан 17 июн.
Создан 14 янв.
Не тот ответ, который вы ищете? Просмотрите другие вопросы с метками
validation
service
csv
web
or задайте свой вопрос.
Introduction
In the section on the tool DROID we look at how to use it to characterize digital content. There is it noted that DROID also has the functionality to generate checksums, although it does not offer integrity checking. We can, however, use the checksums generated by DROID with another tool from The National Archives (UK) (TNA) to carry out integrity checking. That tool is CSV Validator and this page will provide an overview of the tool and take you step by step through how to use it.
Please note: if you are not familiar with the tool DROID, it is recommended that you read through that content before working through this page.
What is CSV Validator?
CSV Validator was developed by TNA to allow automated validation of metadata supplied by depositors of digital content. It compares the metadata with a schema, a type of file that details the requirements for the structure and content of a document. In addition to using CSV Validator for checking metadata, the Digital Preservation team at TNA have created a publicly available schema that can be used with the tool to enable integrity checking. The tool uses the schema alongside a .csv file of data exported from DROID after characterization to carry out the check. CSV Validator generates a checksum for each file and, according to the rules in the schema, compares it with the information in the DROID generated .csv file, and highlights any errors found. For example, missing files or mismatched checksums.
Why Use CSV Validator?
If you are already planning to use DROID for characterization, and generating basic metadata, using the outputs with CSV Validator for integrity checking can make sense. It means that you do not need to store checksums separately, reducing the number of additional files you need to keep alongside the digital content. CSV Validator also provides useful details in its results log when an error is detected during integrity checking, which can make identifying issues easier.
The main drawback of CSV Validator is that it is a bit more awkward than other tools to use for integrity checking after moving a file. It requires the user to provide extra information about where the files were and where they have been moved to. We will, however, cover how to do this on this page. The need to use a schema can also be off-putting for some, but it is actually quite straightforward. Finally, the tool is only available for those with Windows or UNIX/LINUX computers.
Downloading CSV Validator
CSV Validator and the schema you will need for integrity checking are both available for the TNA’s Digital Preservation Team’s repository on the website GitHub.
CSV Validator can be downloaded via the “Releases” section here: https://github.com/digital-preservation/csv-validator (see right).
The Schema document (DROID_integrity_check.csvs) can be downloaded here: https://github.com/digital-preservation/droid-csv-schema.
To run CSV Validator you also will also need to have Java installed on your computer. If you need to install Java you can find it here: https://www.java.com/en/
Opening CSV Validator
CSV Validator is not installed on to your computer in the same way most apps we use are. Rather we must open it from the files we downloaded each time.
To open CSV Validator, we must first extract the files from the .zip file we downloaded. These can be saved wherever is convenient on your computer. Next, navigate to the “bin” folder, which is in the main folder. To open the tool on a Windows computer we then double-click on the “validate-gui.bat” (Batch) file. A command line window will appear (do not close this) and then the interface for CSV Validator.
Editing the Schema
The final step we need to complete before running an integrity check with CSV Validator is editing the schema document. The document is quite intimidating at first, but there is actually only one small part of the document that needs to be edited.
The easiest way to edit the file is to open it in a simple text editor such as “Notepad”. The first twenty-eight lines are notes on the schema and how to use it, so those can be ignored. The line you need to edit is the following:
URI: fileExists integrityCheck(«»,»files»,»includeFolder»)
All that needs changed is to replace the word “files” with the name of the top level folder of the digital content covered by the relevant DROID report. You will need to edit the schema for each new DROID report you run the process with. It is also important not to change the file extension of the schema file, it should be “.csvs”.
Once the tool is downloaded and the schema prepared, we are ready to run an integrity check.
Running an Integrity Check
In the video below we will work through the steps of running an integrity check using CSV Validator and our DROID output, starting with opening the tool from its “batch” file. In this example we assume we have already prepared our schema file and that the digital content has not been moved since we created the DROID output file.
Saving the results of the integrity check can act a record of the successful completion of the process. The saved file is not assigned a file type automatically, so it is important to type a file extension when saving. In the example we chose to save the results as a Text (.txt) file.
In this example the check was successful, resulting in a PASS for all files. But what happens if an error is detected?
Finding an Error during an Integrity Check
In the video above we ran an integrity check where the results said everything was OK (i.e. no files had changed or were missing). This time round the files are from a less perfect future where some errors may have occurred…
The image above shows the results file from the integrity check process. The first two errors relate to a file called “MyPDFCat.pdf”, showing that there has been a fail for the “fileExists” check and also that is was not found when trying to generate checksums. Therefore, we can assume this file has been deleted or moved out of this folder.
The third error shows that the checksum does not match for the file “MyPlainTextCat.txt”, in this case we can assume that a change has been made to the file. We will need to investigate both of these errors further and may need to replace the files from another copy.
What if the Digital Content Had Been Moved?
The two demos above assumed that the digital content had not been moved since the DROID analysis had been completed. But, one of the most important times to complete an integrity check is after moving content (for example, from an ingest processing area of the network to an archive drive). As CSV Validator uses the file path information in the CSV file to locate the files for the integrity check, we must provide it with details of where the files have been moved to. This is done using a “path substitution”. The video demostrates the process again also including a path substitution.
Although the process for entering a file path substitution is relatively straightforward, getting the file path correct can sometimes take a bit of trial and error. If CSV Validator produces results that state the files do not exist, this is normally an indicator that there is an error in file path entered (rather than all of the files have been lost!) So be prepared that you might need to try the process a few times before it works perfectly….
I wrote an open source Python tool to simplify validation of such files available from http://pypi.python.org/pypi/cutplace/.
The basic idea is that you describe the data format in a structured interface specification using OpenOffice.org, Excel or plain CSV. This is done in a few minutes and legible enough to serve as documentation too. We use it to validate files with about 200.000 rows on a daily base.
You can validate a CSV file using the command line:
cutplace specification.csv data.csv
In case invalid data rows are found, the exit code is 1. If you need more control, you can write a little Python script that imports the cutplace module and adds a listener for validation events.
As example, here’s a specification that would validate the sample data you provided, filling the gaps of your short description by making a few assumptions. (I’m writing the specification in CSV to inline it in this post. In practice I prefer OpenOffice.org’s Calc and ODS because I can use more formating and make it easier to read and maintain.)
,"Interface: Show statistics"
,
,"Data format"
"D","Format","CSV"
"D","Item delimiter",";"
"D","Header","1"
"D","Encoding","ASCII"
,
,"Fields"
,"Name","Example","Empty","Length","Type","Rule"
"F","date","15-Mar-10",,,"RegEx","dd-[A-Z][a-z][a-z]-dd"
"F","id","231",,,"Integer","0:"
"F","shown","345",,,"Integer","0:"
,
,"Checks"
,"Description","Type","Rule"
"C","id per date must be unique","IsUnique","date, id"
Lines starting with «D» describe the basic data format. In this case it is a CSV file using «;» as delimiter with 1 header line in ASCII encoding.
Lines starting with «F» describe the various fields. For example,
,"Name","Example","Empty","Length","Type","Rule"
"F","id","231",,,"Integer","0:"
defines a mandatory field «id» of type Integer with a value of 0 or greater. To allow the field to be empty, specify an «X» in the «Empty» column:
,"Name","Example","Empty","Length","Type","Rule"
"F","id","231","X",,"Integer","0:"
Finally there is an optional section to contain more advances checks spawning the whole file, not only single rows. For example, if each date in your file must provide date for an id only once, you can state this using:
,"Description","Type","Rule"
"C","id per date must be unique","IsUnique","date, id"
Any row that starts with an empty column can contain any text you like and will not be processed during validation. This is useful for headings, comments and so on.
CSV Validator is a CSV validation and reporting tool which implements CSV Schema Language.
Released as Open Source under the Mozilla Public Licence version 2.0.
This page is for CSV Validator 1.1 (and related minor releases), the equivalent page for the previous 1.0 release is now to be found at csv-validator-1.0.html.
Overview
The CSV Validator will take a CSV Schema file and a CSV file, verify that the CSV Schema itself is syntactically correct and then assert that each rule in the CSV Schema holds true for the CSV file. The Validator is predominantly written in Scala 2.11 and run’s on any platform with a Java Virtual Machine (JVM). The Validator toolset provides:
- A stand-alone command line tool.
- A desktop application (we provide a simple Swing GUI).
- A library that can be embedded into your own Scala project.
- A library that can be embedded into your own Java project, as we also provide native Java 7 interfaces.
Background
The National Archives receive Metadata along with Collections of Digitised or Born-Digital Collections. Whilst The National Archives typically process Metadata in XML and RDF, it was recognised that it was too difficult and/or expensive for many suppliers to produce the desired metadata in XML and/or RDF, as such it was decided that Metadata would be received in CSV format.
Our experience shows that when suppliers are asked to produce metadata in XML or RDF there are several possible barriers:
- Many content/document repository systems only export metadata in CSV, or generate XML or RDF in a non-desirable format which would then have to be transformed (at further cost).
- Lack of technical knowledge in either XML or RDF.
- Lack of experience of tools for producing and validating XML or RDF.
- Cost. Installing new software tools comes at a severe cost for those OGDs that have outsourced their IT support.
- Best/Worst case, most suppliers already have Microsoft Excel (or an equivalent) installed which they know how to use to produce a CSV file.
The National Archives set exacting requirements on the Metadata that they expect and the format of that Metadata. Such constraints enable them to automatically process it, as the semantics of the metadata are already defined. Whilst previous bespoke tools have been developed in the past for validating data in various CSV files, it was felt that a generic open tool which could be shared with suppliers would offer several benefits:
- A common CSV Schema language, would enable The National Archives to absolutely define required Metadata formats.
- Developed CSV Schemas could be shared with suppliers and other archival sector organisations.
- Suppliers could validate Metadata before sending it to The National Archives. Hopefully reducing mistakes and therefore costs to both parties.
- The National Archives could use the same tool to ensure Metadata compliance automatically.
- As a secondary concern, it was recognised that this tool would also have value for anyone working with CSV as a data/metadata transfer medium.
New features in Version 1.1 (and later)
CSV Validator 1.1 introduces the new features of CSV Schema Language 1.1.
Other changes from the original 1.0 release include full column name validation: a schema error will now be produced if the names of columns listed in the schema file
do not match the actual names of the columns in the CSV file to be validated (when a header row is present). The output pane of the GUI version of the validator is now
cleared when a new validation run is begun, rather than leaving output of a previous run visible until new output is produced.
Release candidates for CSV Validator 1.2, introducing the new features in the draft CSV Schema Language 1.2
are also being made available. So far (other than the version number shown), there are no interface changes, so features and screenshots should be equally applicable.
Installing the CSV Validator
The core of the CSV Validator is written in Scala 2.11, Scala runs in the Java Virtual Machine, so requires Java 7 or newer to be installed on your computer in order to run.
The latest release build is automatically published from the source code repository on GitHub,
to Maven Central currently (26 January 2016), Version 1.1.5.
To install, download the appropriate application.zip file for either csv-validator-ui (for the GUI Application) or csv-validator-cmd (for the Command Line Application).
Simply extract the entire contents of the zip to your desired installation location. The validation components are identical, but csv-validator-ui contains additional
components to create the GUI interface. The other difference between the packages is in the .bat and shell scripts supplied. For csv-validator-ui they launch the GUI,
for csv-validator-cmd they accept command line parameters to allow the CSV file and CSV schema to be selected (and other options to be used).
The latest release candidate for CSV Validator 1.2 can also be found on Maven Central
currently (26 January 2017), Version 1.2-RC2.
CSV Validator GUI Application
Starting the GUI
In order to run the CSV Validator via the GUI, navigate to the directory where you installed csv-validator, and then to the bin subdirectory and run either:
- validate-gui.bat on Windows operating systems, or;
- validate-gui shell script on Unix/Linux.
You should then see the following:
Selecting metadata and schema for validation
To validate data you must supply a full filepath for the csv file to be validated, and for the schema it is to be validated against.
You can either type, or copy-and-paste, the filepath into the appropriate box, these are labelled «CSV file» and «CSV Schema file» respectively.
Alternatively, click the buttons labelled «…» (highlighted in the image below):
This will open up a standard «File open» dialogue, allowing you to navigate to the file in the file system, and select it:
Clicking «Open» will populate the related text box:
Using Settings
While in the most straightforward cases, you can validate a CSV file immediately after supplying the filepaths,
in many instances you will need to supply Settings options.
Using the Settings is equivalent to providing options when running via the Command Line
(note that there is one additional option at the command line that is not available within Settings, to produce a detailed report on the parse of the schema itself).
To open up Settings, click on the downward facing double arrow below the file dialogue buttons (highlighted below):
Having opened up Settings, you will see that there are six sets of options:
CSV Encoding, which has a dropdown menu giving the choice of:UTF-8and;windows-1252;CSV Schema Encoding, which has a dropdown menu giving the choice of:UTF-8and;windows-1252;Fail on first error?;Enforce case-sensitive filepath checks?;Validate CSV for valid UTF-8 characters, and;Path Substitutions
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_2ADM362B003tech_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.
Validating a CSV file and understanding program output
Having selected a CSV file and schema, and chosen which settings (if any) to apply, validation is performed by clicking the large «Validate» button.
As of Version 1.0-RC2, there is a progress bar (but due to a threading issue) that and output to the report pane (below the «Validate» button) is currently only
updated when all rows have been validated (Issue #34 has been raised to address this).
If checksum verification is being performed, the validation can take a considerable amount of time, as each file described in the CSV file has to be read in full
in order to calculate the checksum. The mouse pointer will change to the egg-timer (or equivalent) while validation is taking place.
If the data is completely clean, and validates perfectly against the schema, the only output returned to the output pane will be PASS.
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:
![[1.1] failure: version 1.0 missing or incorrect
<br />
batch_code,department,series,piece,item,ordinal,file_uuid,file_path,file_checksum,resource_uri,scan_operator,scan_id,scan_location,scan_native_format,scan_timestamp,image_resolution,image_width,image_height,image_tonal_resolution,image_format,image_colour_space,process_location,jp2_creation_timestamp,uuid_timestamp,embed_timestamp,image_split,image_split_other_uuid,image_split_operator,image_split_timestamp,image_crop,image_crop_operator,image_crop_timestamp,image_deskew,image_deskew_operator,image_deskew_timestamp,QA-code,comments,transcribed_volume_number,transcribed_birth_date_day,transcribed_birth_date_month,transcribed_birth_date_year,transcribed_official_number
<br />
^](https://digital-preservation.github.io/csv-validator/images/1.1/version-declaration-error.png)
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.
Closing the Validator
All standard means of closing the window should work, or click the close button at the bottom right of the 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.
Starting the CSV Validator at the command line
In order to run the CSV Validator via the command line, set your working directory to the bin subdirectory of the installation directory and execute either:
validate.baton Windows operating systems, or;validateshell script on Unix/Linux.
Executing without any options or command line parameters should produce the following usage text:
Error: Missing argument Error: Missing argument Try --help for more information.
Subsequently, executing with the --help argument, should produce the following help text:
CSV Validator - Command Line 1.0
Usage: validate [options] <csv-path> <csv-schema-path>
--help
Prints this usage text
-t | --trace-parser
Prints a trace of the parser parse
-f <value> | --fail-fast <value>
Stops on the first validation error rather than reporting all errors
-p:<key>=<value> | --path:<key>=<value>
Allows you to substitute a file path (or part of) in the CSV for a different file path
-c <value> | --case-sensitive-paths <value>
Enforces case-sensitive file path checking. Useful when validating on case-insensitive filesystems like Windows NTFS
-x <value> | --csv-encoding <value>
Defines the charset encoding used in the CSV file
-y <value> | --csv-schema-encoding <value>
Defines the charset encoding used in the CSV Schema file
--disable-utf8-validation
Disable UTF-8 validation for CSV files.
--show-progress
Show progress
<csv-path>
The path to the CSV file to validate
<csv-schema-path>
The path to the CSV Schema file to use for validation
Using command line parameters
As a minimum you must supply two command line parameters in order to validate a CSV file. The first parameter is the path to the CSV file you wish to validate,
the second the path to the schema. If a the path contains spaces, wrap it appropriately, e.g. in double quotes («) on Windows.
Using command line options
Nine options (plus —help, which merely reproduces the usage text given above) are available to modify certain behaviours of the CSV Validator,
or to provide debugging assistance.
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.
Command line output
All output is written to stdout by default, and so can be written to file by the usual terminal redirection means. Reporting is otherwise identical to that produced by the GUI client,
which is described in the section Validating a CSV file and understanding program output above.
Example CSV Schemas
In order to understand how to use the Validator in practice, see the example CSV Schema file,
digitised_surrogate_tech_acq_metadata_v1_TESTBATCH000.csvs,
in the GitHub repository digital-preservation/csv-schema.
In the example-data subfolder you will find a CSV file,
digitised_surrogate_tech_acq_metadata_v1_TESTBATCH000.csv,
which complies with the schema. This CSV file refers to XML files in the folder structure below TEST_1
If you download the csv-schema repository and run the csv-validator (by either of the methods described above), you will find that initially it reports validation errors
as it cannot find the XML files for the fileExists or checksum column validation tests. If you create a path substitution, replacing file:///TEST_1/ with
file:///<your system path>/TEST_1/ (if you simply placed the repository on your C: drive on a Windows system, <your system path> will look something like,
C:csv-schemaexample-schemasexample-data, but as it’s a URL, you should reverse the slashes, C:/csv-schema/example-schemas/example-data — in addition if there are any spaces in the path,
you must escape them by replacing them with %20) you should then find that all tests pass. On a Unix-based system you will not need to reverse the slashes,
but spaces and other special characters will still need to be escaped. There is an additional complication that the % character is treated as a
special character in Windows scripts, and itself needs to be escaped, so you should actually use %%20 to obtain the desired behaviour (there can be a further complication
if you are calling out from a controlling script to launch the validator ie call validate.bat where the outer script will do a first replacement of the escaped
character, and so within the call statement you would actually need to use %%%%20 to obtain the desired behaviour (this is passed to the call statement itself as %%20).
System and Performance Considerations
Performance of the CSV Validator is generally limited by the rate at which data can be read from storage. This is particularly true when the CSV Schema used includes
checksum verification for files referred to in the metadata file, when the time taken to complete validation is almost entirely driven to read the files and compute
the checksums.
The CSV file being validated is read line-by-line so the size of the file does not generally affect the memory required for processing,
even when handling CSV files of the order of 0.5 gigabytes in total size, and a million rows.
However, if the schema uses the unique Column Validation Expression to ensure that a given column
(or combination of columns) has a unique value for each row of the CSV file, then a hash map is built by the validator to check the uniqueness.
With this constraint, and a CSV file of the order of 0.5 gigabytes, and a million rows,
it may be necessary to increase the Max Heap Size available to the Java Virtual Machine so that validation can complete.
If the heap is not sufficient, validation will stall, and after a period of time Java will return an error message similar to:
«java.lang.OutOfMemoryError: Java heap space.»
Changing the heap size involves setting the -Xmx option, either via system settings (this will probably require administrative rights on the machine),
or by amending the the .bat or shell script used to launch the validator. Search within the relevant file for JAVA_OPTS,
immediately after that, insert a space and something like -Xmx3G or -Xmx3072m — both these example set the heap size to 3 gigabytes.
The largest value that has been found necessary at The National Archives is 6 gigabytes. Max heap size should always be less than the available system memory.
System restrictions on 32 bit versions of Windows can severely restrict the actual maximum that will be applied.