CSV is one of the most popular formats for publishing data on the web. It is concise, easy to understand by both humans and computers, and aligns nicely to the tabular nature of most data.
But CSV is also a poor format for data. There is no mechanism within CSV to indicate the type of data in a particular column, or whether values in a particular column must be unique. It is therefore hard to validate and prone to errors such as missing values or differing data types within a column.
The CSV on the Web Working Group has developed standard ways to express useful metadata about CSV files and other kinds of tabular data. This primer takes you through the ways in which these standards work together, covering:
What we mean by "tabular data" and "CSV"
Where files that provide metadata about CSV live
How to create a schema to validate the content of a CSV file
How to specify how a CSV file should be converted to RDF or JSON
How to provide other documentation and metadata about a CSV file
Where possible, this primer links back to the normative definitions of terms and properties in the standards. Nothing in this primer overrides those normative definitions.
The CSV on the Web Working Group was chartered to produce a recommendation "Access methods for CSV Metadata" as well as recommendations for "Metadata vocabulary for CSV data" and "Mapping mechanism to transforming CSV into various formats (e.g. RDF, JSON, or XML)". This non-normative document is a primer that describes how these standards work together for new readers. The normative standards are:
Tabular data is any data that can be arranged in a table, like the one here:
column 1
column 2
column 3
row 1
cell in column 1 and row 1
cell in column 2 and row 1
cell in column 3 and row 1
row 2
cell in column 1 and row 2
cell in column 2 and row 2
cell in column 3 and row 2
row 3
cell in column 1 and row 3
cell in column 2 and row 3
cell in column 3 and row 3
There are lots of syntaxes for expressing tabular data on the web. You can put it in HTML tables, pass it around as Excel spreadsheets, or store it in an SQL database.
One easy way to pass around tabular data is as CSV: as comma-separated values. A CSV file writes each row on a separate line and each cell is separated from the next with a comma. The values of cells can be written with double quotes around them; this is necessary when a cell value contains a line break or a comma. So the tabular data above can be expressed in CSV as:
cell in column 1 and row 1,cell in column 2 and row 1,cell in column 3 and row 1
cell in column 1 and row 2,cell in column 2 and row 2,cell in column 3 and row 2
cell in column 1 and row 3,cell in column 2 and row 3,cell in column 3 and row 3
or, with double quotes around cell values:
"cell in column 1 and row 1","cell in column 2 and row 1","cell in column 3 and row 1"
"cell in column 1 and row 2","cell in column 2 and row 2","cell in column 3 and row 2"
"cell in column 1 and row 3","cell in column 2 and row 3","cell in column 3 and row 3"
CSV files usually have an additional row at the top called a header row, which gives human-readable names or titles for each of the columns. Here is a sample CSV file that contains a header row:
Column titles are a type of annotation on a column, not part of the data itself. For example, they aren't included when you're counting the rows of data in a table:
How can you provide extra information about CSV data?
You can provide extra information, known as metadata, about CSV files using a JSON metadata file. If you're just providing metadata about one file, the easiest thing to do is to name the metadata file by adding -metadata.json to the end of the name of the CSV file. For example, if your CSV file is called countries.csv then call the metadata file countries.csv-metadata.json.
The simplest metadata file you can create contains a single table description and looks like:
Metadata files must always include the @context property with a value "http://www.w3.org/ns/csvw": this enables implementations to tell that these are CSV metadata files. The url property points to the CSV file that the metadata file describes.
These metadata documents should be served from a web server with a media type of application/csvm+json if possible.
The description of a table within a metadata file can include:
documentation about the CSV file, such as a description, authorship or licensing information, which can provide context for people using the CSV file
a definition of the structure of the CSV file, which can be used to validate the data that it holds
instructions that enable processors to transform the CSV file into other formats, such as JSON or RDF
By default, if implementations can't find a metadata file by appending -metadata.json to the filename of the CSV file, they'll just look for a file called csv-metadata.json in the same directory as the CSV file.
Metadata files can also describe several CSV files at once, using a slightly different syntax:
Here, the tables property holds an array of table descriptions, each with the URL of the CSV file that it's describing. The metadata file as a whole describes a group of tables. This is usually used when the tables relate to each other in some way: perhaps they're data in the same format from different periods of time, or perhaps they reference each other.
How can you provide extra information about the columns in a CSV file?
You can give information about the columns in a CSV file through a table schema. The simplest thing you can do is say what those columns are called. For example, if you have some CSV like this:
Providing metadata about CSVs can be useful simply in providing extra information to anyone who wants to work with them.
How can you provide documentation about a CSV file?
Here's an example that includes some extra descriptive documentation about a number of CSV files:
{
"@context": "http://www.w3.org/ns/csvw",
"dc:title": "Unemployment in Europe (monthly)"
"dc:description": "Harmonized unemployment data for European countries."
"dc:creator": "Eurostat",
"tables": [{
"url": "countries.csv",
"dc:title": "Countries"
}, {
"url": "country-groups.csv",
"dc:title": "Country groups"
}, {
"url": "unemployment.csv",
"dc:title": "Unemployment (monthly)",
"dc:description": "The total number of people unemployed"
}]
}
This example uses Dublin Core as a vocabulary for providing metadata. You can tell that's the vocabulary that's being used because the terms like dc:title and dc:description begin with the prefix dc, which stands for Dublin Core.
There are several different metadata vocabularies in common use around the web. Some people use Dublin Core. Some people use schema.org. Some people use DCAT. All of these vocabularies can be used independently or together. A publisher could alternatively use:
{
"@context": "http://www.w3.org/ns/csvw",
"schema:name": "Unemployment in Europe (monthly)",
"schema:description": "Harmonized unemployment data for European countries."
"schema:creator": { "schema:name": "Eurostat" },
"tables": [{
"url": "countries.csv",
"schema:name": "Countries"
}, {
"url": "country-groups.csv",
"schema:name": "Country groups"
}, {
"url": "unemployment.csv",
"schema:name": "Unemployment (monthly)",
"schema:description": "The total number of people unemployed"
}]
}
It's not clear at the moment which metadata vocabulary will give publishers the most benefits. Search engines are likely to recognise schema.org. RDF-based systems are more likely to recognise Dublin Core.
More generally, you can use prefixed properties like these on any of the objects in a metadata document. The prefixes that are recognised are those used in the RDFa 1.1 Initial Context. Other properties must be named with full URLs.
How can you provide documentation about the columns in a CSV file?
You can use metadata properties like the ones used for tables for individual columns as well. For example:
{
"@context": "http://www.w3.org/ns/csvw",
"url": "countries.csv"
"tableSchema": {
"columns": [{
"titles": "country",
"dc:description": "The ISO two-letter code for a country, in lowercase."
},{
"titles": "country group",
"dc:description": "A lowercase two-letter code for a group of countries."
},{
"titles": "name (en)",
"dc:description": "The official name of the country in English."
},{
"titles": "name (fr)",
"dc:description": "The official name of the country in French."
},{
"titles": "name (de)",
"dc:description": "The official name of the country in German."
},{
"titles": "latitude",
"dc:description": "The latitude of an indicative point in the country."
},{
"titles": "longitude",
"dc:description": "The longitude of an indicative point in the country."
}]
}
}
We've already seen an example where metadata supplied about a table is structured. Look at the schema:creator here:
{
"@context": "http://www.w3.org/ns/csvw",
"schema:name": "Unemployment in Europe (monthly)",
"schema:description": "Harmonized unemployment data for European countries."
"schema:creator": { "schema:name": "Eurostat" },
"tables": [{
"url": "countries.csv"
}, {
"url": "country-groups.csv"
}, {
"url": "unemployment.csv"
}]
}
The metadata supplied for a table or group of tables can have nested objects within it. You can provide arrays of values. It will be interpreted as if it is (a minimal version of) [[json-ld]]. Particular patterns that are useful are:
Use nested objects to create structure; for example you could supply the website and telephone number of the creator of a table as well as their name:
Use an @id property to provide values that are URLs for things about which more information is available at that URL. For example, rather than providing all the information about Eurostat embedded within the metadata, you could just reference out to it:
There's no standardised facility in the CSV on the Web specifications for annotating individual cells, but there is a hook that will enable best practice about how to do that to emerge: the notes property on a table description can contain objects that represent annotations.
The W3C Web Annotation Working Group is working on a vocabulary for annotations themselves. This vocabulary includes the concept of a target for an annotation and its body (the content of the annotation).
If you are annotating a single cell, the target needs to point to that cell. The easiest way to do that is to use fragment identifiers for CSV files as defined in [[RFC7111]]. These fragment identifiers reference cells based on their position within the original CSV file, with the first row in the CSV file (usually the header row) counted as 1. For example, with the CSV file:
the cell containing Belgique is at #cell=3,4. It's also possible to refer to ranges of cells with this syntax and to use * to refer to the last row in the file. For example, to target a comment on all the locations in the CSV file you could use the fragment identifier #cell=2,6-*,7.
To create comments, then, the notes property can hold an array of objects that use the Web Annotation structure. For example:
Validation is all about checking whether a file contains what you expect it to contain. For CSV files, this can be about:
the number of columns there are and what they're called (see )
what kind of values columns contain
whether any of those values are unique
whether they match values in other CSV files
How can you say what kinds of values are expected in a column?
There's lots more that you can say about the expected content of columns in a CSV file. The most obvious thing is to indicate the data type. For example, with the CSV file:
You don't have to include "datatype": "string" for columns that are strings — columns are assumed to hold strings if no datatype is explicitly specified.
There are a number of different datatypes supported by CSV on the Web implementations, based on the set defined in [[xmlschema11-2]]. The complete set is shown in the following diagram:
Diagram showing the built-in datatypes, based on [[xmlschema11-2]]; names in parentheses denote aliases to the [[xmlschema11-2]] terms (see the diagram in SVG or PNG formats)
The base property must be an existing datatype. The other properties on the new datatype define extra restrictions on values of the new datatype. You can give the new datatype a name and description to provide extra documentation for people using the data:
"datatype": {
"dc:title": "Star Rating",
"dc:description": "A star rating between 1 and 5."
"base": "integer",
"minimum": "1",
"maximum": "5"
}
How do you restrict what kind of strings a column contains?
In the example we've been using, the first column always contains a country code consisting of two lowercase letters. This is a new datatype based on string. You can specify the format for string values in a column using a regular expression, like this:
{
"titles": "country",
"datatype": {
"dc:title": "Country Code",
"dc:description": "Country codes as specified in ISO 3166.",
"base": "string",
"format": "[a-z]{2}"
}
}
It's also possible to restrict the length of a string-based datatype using the length or minLength and/or maxLength properties. For example the following says that the column holding the English names of countries must have values between 3 and 128 characters long:
How do you restrict the size of numbers a column contains?
The size of numbers in a column can be restricted using the minimum and maximum properties and/or the minExclusive and maxExclusive properties. In our example, one column contains latitudes, which can range between -90 and +90:
How do you ensure that decimal numbers have a particular precision or leading zeros?
In the example we're using, the latitudes are provided to between six and eight decimal places and there are no leading or trailing zeros. You can use the format property to provide a pattern that matches these numbers. In the pattern, 0 represents a required digit and # represents an optional digit. For the latitude, the definition looks like:
The format property can also be used to indicate that values in a column should have leading zeros. For example, if a column were supposed to hold a three digit number you could use the pattern 000.
How do you validate numbers that aren't in standard numeric formats?
Sometimes numbers within a CSV file won't be in a standard numeric format. For example, they might include commas as grouping characters (eg 12,345,678) or as decimal points (eg 12,3). In these cases, you can use the format property with an object value.
To match numbers with grouping separators as in 12,345,678 you should specify "," as the groupChar for the format. The pattern property then holds the pattern that indicates how many digits should be in each group. This example validates numbers with groups of three digits separated by commas:
To match numbers with decimal separators other than ., as in 12,3, you should specify "," as the decimalChar for the format. This example validates numbers with commas as decimal points:
You can mix and match decimal and grouping characters and patterns. For example, in France it's standard to use commas for decimal points and spaces for grouping characters, so CSV files produced in France might contain numbers like 1 234 567,89. These could be validated with a datatype like:
How do you restrict what kind of dates and times a column contains?
Dates and times are treated similarly to numbers. You can use the minimum, maximum, minExclusive and/or maxExclusive properties to restrict their values.
For example, to indicate that the column should contain dates later than 1st January 2000, you can use the datatype:
The format of dates or times used for minimum or maximum values is always the ISO 8601 format: yyyy-MM-dd for dates, HH:mm:ss.S for times and yyyy-MM-ddTHH:mm:ss.S for date/times.
How do you validate dates that aren't in standard date or time formats?
Dates and times in CSV files often come in formats other than the standard ISO 8601 format. You can use the format property to indicate the expected format of the date or time.
For example, to recognise dates in the usual UK format such as 31/10/2015 for 31st October 2015, you could use:
Implementations are only required to understand a particular set of common formats for dates and times. These formats are, for dates:
yyyy-MM-dd e.g. 2015-03-22
yyyyMMdd e.g. 20150322
dd-MM-yyyy e.g. 22-03-2015
d-M-yyyy e.g. 22-3-2015
MM-dd-yyyy e.g. 03-22-2015
M-d-yyyy e.g. 3-22-2015
dd/MM/yyyy e.g. 22/03/2015
d/M/yyyy e.g. 22/3/2015
MM/dd/yyyy e.g. 03/22/2015
M/d/yyyy e.g. 3/22/2015
dd.MM.yyyy e.g. 22.03.2015
d.M.yyyy e.g. 22.3.2015
MM.dd.yyyy e.g. 03.22.2015
M.d.yyyy e.g. 3.22.2015
For times:
HH:mm:ss.S with one or more trailing S characters indicating the maximum number of fractional seconds e.g. HH:mm:ss.SSS for 15:02:37.143
HH:mm:ss e.g. 15:02:37
HHmmss e.g. 150237
HH:mm e.g. 15:02
HHmm e.g. 1502
And for date/times:
yyyy-MM-ddTHH:mm:ss.S with one or more trailing S characters indicating the maximum number of fractional seconds e.g. yyyy-MM-ddTHH:mm:ss.SSS for 2015-03-15T15:02:37.143
yyyy-MM-ddTHH:mm:ss e.g. 2015-03-15T15:02:37
yyyy-MM-ddTHH:mm e.g. 2015-03-15T15:02
any of the date formats above, followed by a single space, followed by any of the time formats above, e.g. M/d/yyyy HH:mm for 3/22/2015 15:02 or dd.MM.yyyy HH:mm:ss for 22.03.2015 15:02:37
None of these formats include names or abbreviations for months or days. The implementation you use might support other date and time formats as well, including specialised formats for the other date and time datatypes such as gMonthYear. Check your implementation's documentation to see what it supports.
How do you validate boolean values that aren't true or false?
By default, validators will recognise boolean values that are 1 or 0 or true or false. If a CSV file contains boolean values like T and F or Yes and No then you can create a derived boolean datatype that uses that syntax using the format property, with the two possible values separated by a |, for example:
In it, the second column, country group, contains either the value eu or the value non-eu. Despite there being two values, this isn't a boolean column. Instead, it's a column that has only two valid values.
There are two ways to specify that a column contains one of a list of values: using a regular expression to list the values and using a reference to a separate CSV file that contains the values.
Using a regular expression to give a list of valid values
Using a regular expression to list values works best if those values are strings, if there are only a few of them, and if they are self-explanatory such that you don't want to provide any additional information about them.
In this example, the country group column could be specified as:
As described in , the format property contains a regular expression. List the options separated by | and ensure that you escape any of the characters in the options that have special meaning in regular expressions.
Using a separate CSV file to give a list of valid values
A more powerful method of listing the valid values in a particular column is to list those values in a separate CSV file. The CSV file can be very simple, containing just a single column that lists the valid values. In this example, we can create country-groups.csv containing:
group
eu
non-eu
We can then provide definitions for both the countries.csv and country-groups.csv files, and state that the country group column in countries.csv references the group column in country_groups.csv. This reference from one file to another is called a foreign key.
To use a foreign key, both files must be referenced in the same metadata document, and both columns must be given names. Column names are only used inside the metadata document and you can only use (ASCII) letters, numbers, . and _ within them. So the basic metadata document, before adding the foreign key, should look like:
The foreignKeys property can hold several foreign keys. Each contains a columnReference to a column or list of columns in one CSV file, and a reference which defines a column or list of columns in another CSV file.
The advantage of this method of listing the values allowed in a column is that the CSV file that contains the list of possible values can also provide additional information about those values. For example, we can provide expansions of what eu and non-eu mean in different languages:
group,name (en),name (fr),name (de)
eu,"European Union","Union européenne","Europäische Union"
non-eu,"Non EU countries","Pays hors Union européenne",Nicht-EU-Länder
How do you enable a column to have a mix of value types?
Sometimes a column that contains numbers will contain special values, such as X or NK, when a value is unknown or redacted. If these columns are simply classified as numeric then the non-numeric values will be classed as errors.
To avoid values being classified as errors when they are being used to indicate missing values, list those values as null values using the null property. This can take either a single string or an array of strings. For example, the latitude column might usually be numeric but hold an X if there is no indicative point for the country:
The null property can also be useful when a column contains values that are of the right type but used to indicate a missing value. It's not uncommon, for example, for publishers to use the value 99 in a column that contains integers to indicate that a value is missing.
What if the cells in a column contain lists of values?
Cells may contain lists of values with spaces, semi-colons or other characters acting as separators. For example, instead of using separate latitude and longitude columns, the CSV that we're looking at could contain a single latlong column consisting of the latitude and longitude separated by a space:
In this scenario, the separator property can be used to indicate that the values in a column are lists themselves, and what separator is used between the items in the list. For example:
When separator is specified, the datatype property applies to each of the values in the list. There's no way to indicate that the values in the list have different datatypes, or set limits on the length of the list.
How do you ensure every row has a value for a column?
By default, a validator won't give any errors if a value is missing in a column. If you want to ensure that a value is provided for every row in the column, use the required property for that column, with the value true.
In our example, we might say that all the columns are required except the French and German names (applications being expected to default to the English name if the translation is missing):
Setting required to true means that you can't have any null values in a column. If, in this example, latitude and longitude had null set to X then those columns couldn't contain an X. It doesn't usually make sense to specify both null and required.
How do you indicate all the values in a column are unique?
In our example, the country column must contain unique values: each row should define a different country. To specify this, you can use a primary key to refer to the name of the column:
Each CSV file can only have one primary key. A primary key can be made up of a number of columns that have to be unique in combination: the classic example would be ["firstName", "lastName"].
CSV is great for transferring data around the place, but it's often not as useful for processing that data. Other formats can be better at providing structure and meaning for data. So a means for transforming data out of CSV and into other formats is a common requirement.
What can you transform CSV into?
You can of course transform CSV into anything you like using your favourite programming language. However, the CSV on the Web specs provide standardised ways of mapping CSV into two other formats:
JSON, in [[csv2json]]
RDF, in [[csv2rdf]]
These specifications make use of the metadata described in this primer during the transformation to decide what to include in the transformed output and how to include it. Processors that support these standardised transformations can be used by people who can't program.
These specifications describe what output you get if you don't supply any metadata. Given a CSV file like this:
The specifications define how to transform CSV into RDF. In this Primer all the examples use Turtle as the serialisation for that RDF. Implementations may generate other serialisations for RDF such as RDF/XML or JSON-LD.
What values get used in the output of a transformation?
The result of a transformation will include typed values based on the datatype and language of each column. So if we state that the longitude and latitude are numbers and the names are strings in the given language, as in this metadata:
What value gets used in the output if it's missing in the CSV?
If there's a missing value in the CSV, then usually the property will be omitted in the result as well. So if the latitude and longitude are missing for the first row of the CSV file we've been using, the equivalent JSON won't include those properties either:
If you want a value to appear even when the value is missing in the CSV, you can provide that value as the default for the column. This value must be supplied as a string but will be treated exactly as if it had appeared within the CSV file. For example, if you supply a non-numeric string for a numeric column, as in:
How are the properties in the output of a transformation named?
By default, the properties in the JSON and RDF come from the titles of the columns (the headers in the CSV file). In RDF, since the properties are URIs, the names are URL-encoded and turned into fragment identifiers in a URL based on the location of the CSV file being transformed. Hence in the previous examples there have been property names like "name (en)" in JSON and #name%20%28en%29 in RDF.
You can override this default by supplying a name for the column. That name will be used instead of the title of the column when creating the property. So if you have:
{
"titles": "name (en)",
"name": "english_name"
}
then the property will be called english_name in the JSON output and #english_name in the RDF output.
You can also use the propertyUrl property to supply a prefixed name or a URL for the property. For example, to use schema:latitude as the name for the latitude property in both the JSON and the RDF output, you could use:
The propertyUrl property can be used to map several columns in the CSV file onto properties with the same name. In our example, each country has several names which are all really the same property; the schema could look like:
If there isn't a relevant property in one of the vocabularies that is built-in to CSV on the Web (those listed as part of the RDFa 1.1 initial context), the propertyUrl can hold a URL template. Usually this template won't include any substitutable parts because it's generally the case that the property should be the same for the whole column. For example, you might have:
Sometimes a column contains a value that can be programmatically mapped into a URL. In this case, the valueUrl property contains a template for the URL that it should be mapped into.
For example, say that there were pages for each country on the web at e.g. http://example.org/country/at. In that case, the URL for the country could be generated with the URL template http://example.org/country/{country}. Within this template, {country} inserts the value from the column named country into the URL. So the metadata could contain:
If you want to preserve the original value from the column and use it to create a URL, you may want to introduce a virtual column. For example, with the latitude and longitude of each country available, you might want to provide a link to a map centered on the country within Google Maps. The URLs for these look like https://www.google.com/maps/@50.501045,4.476674,7z, and a template like https://www.google.com/maps/@{lat},{long},7z.
To add a property that points to this URL, add a virtual column at the end of the column definitions within the schema. A virtual column definition looks just like a normal column definition but with the virtual property set to true:
How do you include an identifier for the thing described by each row?
By default, the things described by each row don't have identifiers associated with them in either JSON or RDF outputs. You can add an identifier for the row by setting the aboutUrl property. Usually that's done at the top level of the schema.
For example, say each row in countries.csv was about a country whose identifier looked like http://example.org/country/{code} where code was the value within the first column of the CSV file (the country column). The aboutUrl could be set to the generate this URL for each row using a URL template:
How do you indicate the type of the thing described by each row?
Whether generating JSON or RDF it can be useful to indicate that each row contains data about a particular type of thing, such as a Person or a Country. There isn't usually a column within tabular data that indicates the type of the row (because it's generally the same for every row, so including it would be superfluous), so you have to add it as a virtual column.
The virtual column needs to come after the descriptions of columns actually within the data. It should have its virtual property set to true and its propertyUrl set to rdf:type to indicate that the virtual column will indicate the type of entity the row is about. The valueUrl property can then be set to the prefixed name or URL of the type of the entity. For example, when each row represents a Country, you might use:
How do you include extra metadata in the result of the transformation?
Transformations into JSON or RDF can be carried out in one of two modes. In minimal mode, which is what we've looked at so far, the output only contains data from the rows within the CSV file. In full mode, the output also contains metadata about the CSV file, including metadata from the metadata file.
For example, say that our metadata file looked like:
{
"@context": "http://www.w3.org/ns/csvw",
"url": "countries.csv",
"schema:name": "Countries",
"schema:description": "European countries for which comparative statistics are collected by Eurostat.",
"schema:creator": { "schema:name": "Eurostat" },
"tableSchema": {
...
}
}
The output of a full JSON transformation would look like:
Metadata provided about tables is interpreted based on the rules for [[json-ld]] which means that you can provide as much structure within that metadata as you like, including providing structured values, languages and datatypes, so that the data in the output includes what you need it to.
How can you remove output from a transformation result?
By default, the output from a JSON or RDF transformation will include all the data from all the columns of all the tables in the metadata document. It may be that you're not interested in some of that within the output of your transformation. In that case, you can use the suppressOutput property in the metadata to exclude the data that you're not interested in.
For example, perhaps I'm not interested in the non-English names of countries in my output. In that case, I could suppress them as follows:
Similarly, when generating the output for a set of tables, you can suppress the output from a whole table by adding the suppressOutput property to the description of that table:
Generating JSON in this shape requires the judicious use of virtual columns, aboutUrl and valueUrl: if you create a column whose valueUrl corresponds to the aboutUrl of another column, you will create nested properties.
In this example, we can use two aboutUrls: one in the form http://example.org/country/{code} for the Country and one in the form http://example.org/country/{code}#geo for the geo-coordinates of the country. The names are properties of the former while the longitude and latitude are properties of the latter. A virtual column can add the association between the two objects, with a propertyUrl of schema:geo, like so:
Note also in this example the use of suppressOutput to remove properties that we're not interested in, and the use of virtual columns to add type information to both types of generated object.
The result of this transformation is close to what we were aiming for, though with the addition of @id properties:
How do you indicate that values should be mapped to a list rather than repeating properties in RDF?
If you are used to using RDF you'll know that there's a big difference between having a property that has multiple values (ie multiple triples with the same subject and property) and a property that has a rdf:List as a value. Sometimes one is appropriate, sometimes the other. The ordered property enables you to indicate which to use for values that are sequences in the original data.
Let's use as an example the version of our data in which the latitude and longitude are in the same property:
This shows the #latlong property having two values: 4.76965545e1 and 1.334598005e1. These values could easily get mixed up such that the first was taken to be the longitude and the second the latitude rather than the other way around.
To avoid this mix-up occurring, set the ordered property to true:
As illustrated above, it is possible to transform CSV into something that looks like JSON-LD by transforming it into JSON. You can add @id properties for identifiers using aboutUrl and add @type properties for types using virtual columns.
However, if you're really after JSON-LD as an output, the best route is to transform into RDF and emit that RDF as JSON-LD. This will give you more control over the context that's used to determine the properties and structure of the output.
There's no single specified way of displaying CSV tables in HTML. If you are writing code to do so, it's good practice to:
use the locale of the user of the browser to determine which titles to display for columns, and how to format numbers and dates within the table
respect the directionality of the table and the text within the table that's indicated through the tableDirection and textDirection properties
use the aboutUrl and valueUrl properties to link out to other pages from cells within the table, and the propertyUrl to link out to information about the property a column represents; these properties could also be used to embed RDFa into the HTML table, as described in [[html-rdfa]]
display the metadata that's available about the table so that people get a full idea of the context of the data
provide a way to access notes and annotations that have been provided about individual cells, rows or columns
highlight cells that contain errors so that they're easy to spot and correct
provide links to other related resources indicated through the metadata supplied for the table
keep columns that are used to provide a title for each row (using rowTitles) visible; for screen readers, use these to label the rows so that people can navigate through the table easily
link out to the original CSV file so that people can download it
How can you transform CSV into the DataCube vocabulary?
The Data Cube vocabulary [[vocab-data-cube]] is a vocabulary for statistical data based on SDMX (Statistical Data and Metadata eXchange). Statistical data is often expressed in tables, sometimes with one row per Observation and sometimes with each cell containing a different Observation.
Generating data in the Data Cube vocabulary requires the use of techniques that have been described above, such as adding identifiers to entities, adding structure and types through virtual columns, and using metadata to supply additional static context.
There is a fully worked out example of transforming to DataCube, using meterological data as its basis, available on the Github repository for the Working Group.
While there are only specifications for transforming CSV into JSON and RDF, there is an extension mechanism within CSV metadata to indicate other transformations that could be applied to CSV files. The transformations property on a table description holds an array of descriptions of transformations that processors could carry out. There's no guarantees that a given processor will recognise them, but over time it might be that there begins to be recognised practices for how such transformations might work.
The transformations must have the following properties:
gives a URL for the format that the script is in, for example https://mustache.github.io/ for Mustache or http://www.iana.org/assignments/media-types/application/javascript for Javascript
You can also supply a titles property to provide a human-readable description of the output of the transformation, and a source property to indicate that the input to the transformation isn't the original CSV or tabular data, but json or rdf.
For example, if I wanted to convert the data that we've been using into XML, I could create a Mustache template like this at xml-template.mustache:
There are a number of features in the CSV on the Web metadata documents that support scenarios encountered in CSV files that use different languages. We already discussed using varying number formats in and date formats in . Here we'll look at how to create metadata files, schemas and CSV files that take account of and work across multiple languages.
How do you indicate the language used by the metadata file?
The metadata file will often contain natural language text, such as titles and descriptions of columns and tables. Unless you specify otherwise, implementations will assume all this text is in an undefined language (und). If you want to specify what natural language is in use within the metadata file, you have to change the way the @context is specified. Instead of the normal value:
"@context": "http://www.w3.org/ns/csvw"
The @context should take an array, where the first value is the usual URL as a string and the second is an object with a @language property set to the language being used within the metadata file. This example, which has English-language titles and descriptions, illustrates:
{
"@context": [ "http://www.w3.org/ns/csvw", { "@language": "en "} ],
"dc:title": "Countries"
"url": "countries.csv"
"tableSchema": {
"columns": [{
"titles": "country",
"dc:description": "The ISO two-letter code for a country, in lowercase."
},{
"titles": "country group",
"dc:description": "A lowercase two-letter code for a group of countries."
},{
"titles": "name (en)",
"dc:description": "The official name of the country in English."
},{
"titles": "name (fr)",
"dc:description": "The official name of the country in French."
},{
"titles": "name (de)",
"dc:description": "The official name of the country in German."
},{
"titles": "latitude",
"dc:description": "The latitude of an indicative point in the country."
},{
"titles": "longitude",
"dc:description": "The longitude of an indicative point in the country."
}]
}
}
How do you provide metadata such as descriptions in different languages?
Metadata such as descriptions can be objects rather than strings. Using objects is useful when you want to provide the language for a value. In this case, the object should have two properties: a @value property holding the natural-language string and a @lang value indicating what language that string is in. For example:
{
"titles": "name (en)",
"dc:description": {
"@value": "The official name of the country in English.",
"@lang": "en"
}
}
You can use an array to provide the same metadata in many different languages, for example:
"dc:title": [{
"@lang": "en",
"@value": "Unemployment in Europe (monthly)"
},{
"@lang": "de",
"@value": "Arbeitslosigkeit in Europa (monatlich)"
},{
"@lang": "fr",
"@value": "Le Chômage en Europe (mensuel)"
}]
If you don't indicate the language used for metadata, processors will assume it's the default language used in the metadata as a whole.
How do you provide titles for columns in different languages?
You can use an object as the value for the titles property for a column to provide titles in different languages. Within the object, each property is a language and the value is the title in that language:
How do you specify the language of the values in a column?
Within an individual CSV file, it may be that different columns contain values that are in different languages. In the example we're using, there are three columns that each contain the name of a country, in English, French and German:
Use the lang property on the column description to indicate the language of text in that column:
{
"@context": [ "http://www.w3.org/ns/csvw", { "@language": "en "} ],
"dc:title": "Countries"
"url": "countries.csv"
"tableSchema": {
"columns": [{
"titles": "country",
"dc:description": "The ISO two-letter code for a country, in lowercase."
},{
"titles": "country group",
"dc:description": "A lowercase two-letter code for a group of countries."
},{
"titles": "name (en)",
"dc:description": "The official name of the country in English.",
"lang": "en"
},{
"titles": "name (fr)",
"dc:description": "The official name of the country in French.",
"lang": "fr"
},{
"titles": "name (de)",
"dc:description": "The official name of the country in German.",
"lang": "de"
},{
"titles": "latitude",
"dc:description": "The latitude of an indicative point in the country."
},{
"titles": "longitude",
"dc:description": "The longitude of an indicative point in the country."
}]
}
}
There's no relationship between the language used in a metadata file and that used in the CSV file that it describes.
How do you indicate that tables should be displayed right-to-left?
Implementations that display tables according to the specs should mostly be able to guess whether a table should be displayed left-to-right or right-to-left based on the content of the table. Implementations will look at the content of the cells to work out which way to display their content and will look at the content of the table as a whole to work out whether to display the first column on the right or left of the page.
If you want to override the display of a particular column then you can use the textDirection property on a column description to explicitly be rtl or ltr:
If you want to override the display of the table overall then you can use the tableDirection property on the description of the table, or for all tables in the group.
The value of the tableDirection property is inherited to all columns in the table, so any text within this table will similarly be displayed right-to-left. This can be overridden by setting textDirection to ltr or auto (in which case the direction of the text within each cell will be determined by its contents).
There is no native support for expressing the units of measure for a particular column. You can, however, use documentation to tell people who are using the data what unit of measure is used for that particular column. This can be informal within the description of the column:
Supporting units of measure by transforming to structured values
If you are generating JSON or RDF from CSV, you may want to generate structured values that include the units of each value from the CSV file. This is a little complicated, but useful if different rows contain values that use different units. In this case, the output that you're aiming for in RDF would look something like:
You may want to use different properties than schema:value and schema:unitCode; if so, just use different propertyUrls.
You need to decide on a pattern for the URLs for the values themselves, and set the aboutUrl for the relevant column create that URL. In this example, the URLs that look like #row-1-distance can be generated with the pattern #row-{_row}-distance. The propertyUrl for the column needs to be schema:value as the value in the column provides the value for that property. So the column description looks like:
You then need to use virtual columns (descriptions of additional columns that don't exist in the source CSV) to generate the relationship between the thing whose distance is being measured and the structured value, and the additional property providing the unit for the structured value.
To generate the relationship being the thing that has the distance and the structured value, the virtual column's valueUrl needs to hold the same URL template as you used before:
To create the units property, you need another virtual column where the aboutUrl of that virtual column generates the URL for the structured value, the propertyUrl is schema:unitCode and the valueUrl is the URL representing the unit (in this case http://qudt.org/vocab/unit#Kilometre):
Usually, however, processors should recognise or be able to resolve the URL for the unit to understand that it's a unit of length, if this is important for onward processing.
Supporting units of measure with named datatypes in RDF
If you are generating RDF from CSV, you may want to define a datatype for a column and then provide additional information about that datatype as properties. For example, the column description could look like:
There are many different ways of representing geospatial data within a CSV file, and no single best practice for doing so.
At the simplest level, it's possible to reference geospatial coordinates as points with separate columns for latitude, longitude and if necessary altitude (or using a different spatial reference system). This enables separate validation for the separate coordinates. The example used throughout this primer uses this setup:
Metadata can be used to provide specialist types for the values of these coordinates, to indicate that they are latitude and longitude (by mapping to the well-known schema:latitude and schema:longitude properties which specify the use of WGS84), to group the coordinates together, and to provide a link that uses the coordinates to provide a map:
You can put latitude and longitude into a single column, with a character separator between the numbers as shown in . However, this makes it harder to accurately validate the individual coordinates. They also cannot be separated out into separate property values when converting to JSON or RDF. So this is a more restrictive method and best avoided.
CSV files may also need to contain geometries beyond individual points. There are no built-in formats for geometries recognised by implementations of CSV on the Web. Geometries may be expressed using GeoJSON, GML, KML or OGC Well-Known Text (WKT) representations. In each case, schemas may indicate that columns containing geometries adhere to a particular type:
json if the format is JSON-based such as GeoJSON
xml if the format is XML-based such as GML or KML
string if the format is text-based such as OGC WKT
You can use the format property to further constrain the content of columns containing these values. For example, you could use:
to indicate that a column contains a very basic OGC WKT Polygon. (More sophisticated regular expressions could be used if properties held different types of objects such as points or lines or more complex polygons expressed in OGC WKT.)
There is no standard way of expressing which coordinate reference system (CRS) is being used within CSV metadata, but GeoJSON, GML and OGC WKT values can include information about which CRS they use. The W3C Spatial Data on the Web Working Group intend to make recommendations in this area within [[sdw-bp]].
How can you specify a single schema for multiple CSV files?
CSV on the Web is designed to enable you to reuse the same schema when publishing multiple CSV files, even if those files are created by different organisations and therefore reside in different places. Rather than embedding a schema within the description of a table, the tableSchema property can point out to a schema held somewhere else.
For example, if you were a statistical agency and wanted municipalities to publish their unemployment figures using the same schema, you could specify the columns that you wanted included within the schema you specified at http://example.org/schema/unemployment.json:
Experience shows that publishers of data in CSV files often use their own headings for the columns. So long as these don't change the meaning of the column, as a statistical agency you probably don't care. The titles property allows you to provide multiple alternative titles that people may use in an array:
In cases where the data is being gathered from multiple countries, it may also be useful to specify multiple possible titles in different languages, as described in .
It will help consistency and processing of the data if the municipalities use a consistent set of codes to indicate which municipality the data relates to. As the statistical agency, you can supply the relevant codes in a CSV file, e.g. http://example.org/ref/municipalities.csv:
code,name
0101,Absecon City
0102,Atlantic City
0103,Brigantine City
...
This file will of course have its own simple schema, http://example.org/schema/municipalities.json, that provides the datatype for the municipality codes and indicates that they are unique through a primary key:
A foreign key in the schema supplied by the statistical authority ensures that the codes used in the unemployment data match up with the standard set supplied by the statistical authority:
When a municipality describes their unemployment CSV, they will need to point to your schema for their data and to the centrally provided municipalities reference data and schema:
A more complex example in which there is linking between pairs of files where the schemas are provided by a central authority is provided in the section Foreign Key Reference Between Schemas within [[tabular-metadata]].
It is useful to have titles for rows both for screen readers and in other displays where it may be difficult to view the complete context of a table and therefore to understand the content of a row.
The rowTitles property within a schema provides an array of columns that provide sufficient context to label the row. These may sometimes be the same as the columns used for the primaryKey in the table, but are more likely to be columns that contain human readable text.
In this case, a screen reader or other display of the table could choose to read or display only the row title that matched the user's preferred language.
In other cases, the rowTitles property may be set to an array of columns that together provided sufficient context to understand the column (eg ["firstName", "lastName"]).
A lot of what's called "CSV" that's published on the web isn't actually CSV. It might use something other than commas (such as tabs or semi-colons) as separators between values, or might have multiple header lines.
The specification for CSV as a format is [[RFC4180]]. However, this is an informational specification and and not a formal standard. Therefore, applications may deviate from it.
The metadata that's described here can be used with files that contain tabular data but that aren't CSV. You can provide guidance to processors that are trying to parse those files through the dialect property on a table description. For example, say we were dealing with a tab-separated file that contains multiple header lines at http://example.org/data/unemployment.tsv:
There are a number of other properties that you can set within the dialect to cater for the large range of weird things that people do in CSV files. They are:
If the file has some rows at the start that don't contain useful information, set this to that number of rows. (Sometimes people put metadata at the start of a CSV file, before the header lines.)
If you don't want to ignore whitespace around values, set this to false. If you want to only ignore whitespace at the beginning of values, set it to start and if you want to only ignore whitespace at the end of values, to end. By default whitespace at the start and the end of a value will be stripped away.
Tables in HTML are a bit more complicated than tables in CSV files. However, it is possible to use the metadata described here and in [[tabular-metadata]] to described HTML tables by embedding the metadata within a script element in the header of the HTML page. A description and example of how to do this is provided in the [[csvw-html]] Note.
See also:
[[csvw-html]]
What if you want to put metadata files somewhere else?
By default, if you point a processor at a CSV file on the web, they will look for a Link header within the response with rel="describedby" and specifying a content type of application/csvm+json, application/ld+json or application/json. If you're publishing CSV files and it's possible to set the Link header, this is the best way of telling processors where to look for metadata files.
If processors don't see an appropriate Link header, they will append -metadata.json to the end of the URL of the CSV file to try to find metadata for it. If they can't find a metadata file there, they will look in the directory containing the CSV file for a file called csv-metadata.json and use that file.
Looking for files in this way isn't appropriate on all servers. As a publisher, it might be that you have URLs for CSV files that are automatically generated using queries, such as http://example.org/data?format=csv&x=15&y=53 or that you want metadata to live in a separate subdirectory or central location.
As a publisher, if you can't or don't want to use the Link header, you can control where processors look for metadata for your CSV files by listing the locations to look at within the /.well-known/csvm file on your server. (This is a well-known location as defined in [[RFC5785]].) This file should contain a list of URL patterns which will be expanded by substituting url for the URL of the CSV file, and then resolving against the location of the CSV file. Lines might look like:
/metadata?for={url}
?format=metadata
{+url}m
With a CSV file at http://example.org/data.csv this would lead the processor to search for metadata at:
