Table groups

A group of tables comprises a set of annotated tables and a set of annotations that relate to that group of tables. The core annotations of a group of tables are:

• id — an identifier for this group of tables, or null if this is undefined.
• notes — any number of additional annotations on the group of tables. This annotation may be empty.
• tables — the list of tables in the group of tables. A group of tables MUST have one or more tables.

Groups of tables MAY in addition have any number of annotations which provide information about the group of tables. Annotations on a group of tables may include:

• titles or descriptions of the group of tables.
• information about the source or provenance of the group of tables.
• links to other groups of tables (e.g. to those that provide similar data from a different time period).

When originating from [[!tabular-metadata]], these annotations arise from common properties defined on table group descriptions within metadata documents.

Tables

An annotated table is a table that is annotated with additional metadata. The core annotations of a table are:

• columns — the list of columns in the table. A table MUST have one or more columns and the order of the columns within the list is significant and MUST be preserved by applications.
• direction — the direction in which the columns in the table should be displayed, as described in .
• foreign keys — a list of foreign keys on the table, as defined in [[!tabular-metadata]], which may be an empty list.
• id — an identifier for this table, or null if this is undefined.
• notes — any number of additional annotations on the table. This annotation may be empty.
• rows — the list of rows in the table. A table MUST have one or more rows and the order of the rows within the list is significant and MUST be preserved by applications.
• schema — a URL referencing a schema applied to this table, or null.
• suppress output — a boolean that indicates whether or not this table should be suppressed in any output generated from converting the group of tables, that this table belongs to, into another format, as described in .
• transformations — a (possibly empty) list of specifications for converting this table into other formats, as defined in [[!tabular-metadata]].
• url — the URL of the source of the data in the table, or null if this is undefined.

The table MAY in addition have any number of other annotations. Annotations on a table may include:

• titles or descriptions of the table,
• information about the source or provenance of the data in the table, or
• links to other tables (e.g. to indicate tables that include related information).

When originating from [[!tabular-metadata]], these annotations arise from common properties defined on table group descriptions within metadata documents.

Columns

A column represents a vertical arrangement of cells within a table. The core annotations of a column are:

• about URL — the about URL URI template used to create a URL identifier for each value of cell in this column relative to the row in which it is contained, as defined in [[!tabular-metadata]].
• cells — the list of cells in the column. A column MUST contain one cell from each row in the table. The order of the cells in the list MUST match the order of the rows in which they appear within the rows for the associated table.
• datatype — the expected datatype for the values of cells in this column, as defined in [[!tabular-metadata]].
• default — the default value for cells whose string value is an empty string.
• lang — the code for the expected language for the values of cells in this column, expressed in the format defined by [[!BCP47]].
• name — the name of the column.
• null — the string or strings which cause the value of cells having string value matching any of these values to be null.
• number — the position of the column amongst the columns for the associated table, starting from 1.
• ordered — a boolean that indicates whether the order of values of a cell should be preserved or not.
• property URL — the expected property URL URI template used to create a URL identifier for the property of each value of cell in this column relative to the row in which it is contained, as defined in [[!tabular-metadata]].
• required — a boolean that indicates that values of cells in this column MUST NOT be empty.
• separator — a string value used to create multiple values of cells in this column by splitting the string value on the separator.
• source number — the position of the column in the file at the url of the table, starting from 1, or null.
• suppress output — a boolean that indicates whether or not this column should be suppressed in any output generated from converting the table, as described in .
• table — the table in which the column appears.
• text direction — the indicator of the text direction values of cells in this column, as described in .
• titles — any number of human-readable titles for the column, each of which MAY have an associated language code as defined by [[!BCP47]].
• value URL — the expected value URL URI template used to create the URL identifier for the value of each cell in this, as defined in [[!tabular-metadata]].
• virtual — a boolean that indicates whether the column is a virtual column. Virtual columns are used to extend the source data with additional empty columns to support more advanced conversions; when this annotation is false, the column is a real column, which exists in the source data for the table.

Columns MAY in addition have any number of other annotations, such as a description. When originating from [[!tabular-metadata]], these annotations arise from common properties defined on table group descriptions within metadata documents.

Rows

A row represents a horizontal arrangement of cells within a table. The core annotations of a row are:

• cells — the list of cells in the row. A row MUST contain one cell from each column in the table. The order of the cells in the list MUST match the order of the columns in which they appear within the table columns for the row's table.
• number — the position of the row amongst the rows for the table, starting from 1.
• primary key — a possibly empty list of cells whose values together provide a unique identifier for this row. This is similar to the name of a column.
• titles — any number of human-readable titles for the row, each of which MAY have an associated language code as defined by [[!BCP47]].
• referenced rows — a possibly empty list of pairs of a foreign key and a row in a table within the same group of tables (which may be another row in the table in which this row appears).
• source number — the position of the row in the original url of the table, starting from 1, or null.
• table — the table in which the row appears.

Rows MAY have any number of additional annotations. The annotations on a row provide additional metadata about the information held in the row, such as:

• the certainty of the information in that row.
• information about the source or provenance of the data in that row.

Neither this specification nor [[!tabular-metadata]] defines a method to specify such annotations. Implementations MAY define a method for adding annotations to rows by interpreting notes on the table.

Cells

A cell represents a cell at the intersection of a row and a column within a table. The core annotations of a cell are:

• about URL — an absolute URL for the entity about which this cell provides information, or null.
• column — the column in which the cell appears; the cell MUST be in the cells for that column.
• errors — a (possibly empty) list of validation errors generated while parsing the value of the cell.
• ordered — a boolean that, if the value of this cell is a list, indicates whether the order of that list should be preserved or not.
• property URL — an absolute URL for the property associated with this cell, or null.
• row — the row in which the cell appears; the cell MUST be in the cells for that row.
• string value — a string that is the original syntactic representation of the value of the cell, e.g. how the cell appears within a CSV file; this may be an empty string.
• table — the table in which the cell appears.
• text direction — which direction the text within the cell should be displayed, as described in .
• value — the semantic value of the cell; this MAY be a list of values, each of which MAY have a datatype other than a string, MAY have a language and MAY be null. For example, annotations might enable a processor to understand the string value of the cell as representing a number or a date. By default, if the string value is an empty string, the semantic value of the cell is null.
• value URL — an absolute URL for this cell's value, or null.

There presence or absence of quotes around a value within a CSV file is a syntactic detail that is not reflected in the tabular data model. In other words, there is no distinction in the model between the second value in a,,z and the second value in a,"",z.

Cells MAY have any number of additional annotations. The annotations on a cell provide metadata about the value held in the cell, particularly when this overrides the information provided for the column and row that the cell falls within. Annotations on a cell might be:

• notes to aid the interpretation of the value.
• information about the source or provenance of the data in that cell.

Neither this specification nor [[!tabular-metadata]] defines a method to specify such annotations. Implementations MAY define a method for adding annotations to cells by interpreting notes on the table.

Datatypes

Columns and cell values within tables may be annotated with a datatype which indicates the type of the values obtained by parsing the string value of the cell.

Datatypes are based on a subset of those defined in [[!xmlschema11-2]]. The annotated tabular data model limits cell values to have datatypes as shown on the diagram:

• the datatypes defined in [[!xmlschema11-2]] as derived from and including xsd:anyAtomicType.
• the datatype rdf:XMLLiteral, a sub-type of xsd:string, which indicates the value is an XML fragment.
• the datatype rdf:HTML, a sub-type of xsd:string, which indicates the value is an HTML fragment.
• the datatype csvw:JSON, a sub-type of xsd:string, which indicates the value is serialized JSON.
• datatypes derived from any of these datatypes.

The core annotations of a datatype are:

• id — the absolute URL that identifies the datatype, or null if undefined; converters SHOULD use this URL when serializing values of this datatype. Processors MAY use this annotation to perform additional validation on column values using this datatype.
• base — the absolute URL that identifies the datatype from which this datatype is derived. This MUST be the URL of a built-in datatype as listed above, or null if the datatype is xsd:anyAtomicType. All values of the datatype MUST be valid values of the base datatype.
• format — a string or object that defines the format of a value of this type, used when parsing a cell string value as described in .
• length — a number that the exact length of a cell value as described in .
• minimum length — a number that the minimum length of a cell value as described in .
• maximum length — a number that the maximum length of a cell value as described in .
• minimum — a number that the minimum valid value (inclusive) of a cell value as described in .
• maximum — a number that the maximum valid value (inclusive) of a cell value as described in .
• minimum exclusive — a number that the minimum valid value (exclusive) of a cell value as described in .
• maximum exclusive — a number that the maximum valid value (exclusive) of a cell value as described in .

If the id of a datatype is that of a built-in datatype, the values of the other core annotations listed above MUST be consistent with the values defined in [[!xmlschema11-2]] or above. For example, if the id is xsd:integer then the base must be xsd:decimal.

Datatypes MAY have any number of additional annotations. The annotations on a datatype provide metadata about the datatype such as title or description. These arise from common properties defined on datatype descriptions within metadata documents, as defined in [[!tabular-metadata]].

The id annotation may reference an XSD, OWL or other datatype definition, which is not used by this specification for validating column values, but may be useful for further processing.

Overriding Metadata

Processors SHOULD provide users with the facility to provide their own metadata for tabular data files that they process. This might be provided:

• through processor options, such as command-line options for a command-line implementation or checkboxes in a GUI.
• by enabling the user to select an existing metadata file, which may be local or remote.
• by enabling the user to specify a series of metadata files, which are merged by the processor and handled as if they were a single file.

For example, a processor might be invoked with:

$ csvlint data.csv --datatypes:string,float,string,string
        

to enable the testing of the types of values in the columns of a CSV file, or with:

$ csvlint data.csv --schema:schema.json
        

to supply a schema that describes the contents of the file, against which it can be validated.

Metadata supplied in this way is called overriding, or user-supplied, metadata. Implementations SHOULD define how any options they define are mapped into the vocabulary defined in [[!tabular-metadata]]. If the user selects existing metadata files, implementations MUST NOT use metadata located through the Link header (as described in ) or site-wide location configuration (as described in ).

Users should ensure that any metadata from those locations that they wish to use is explicitly incorporated into the overriding metadata that they use to process tabular data. Processors may provide facilities to make this easier by automatically merging metadata files from different locations, but this specification does not define how such merging is carried out.

Link Header

If the user has not supplied a metadata file as overriding metadata, described in , then when retrieving a tabular data file via HTTP, processors MUST retrieve the metadata file referenced by any Link header with:

• rel="describedby", and
• type="application/csvm+json", type="application/ld+json" or type="application/json".

so long as this referenced metadata file describes the retrieved tabular data file (ie, contains a table description whose url matches the request URL).

If there is more than one valid metadata file linked to through multiple Link headers, then implementations MUST use the metadata file referenced by the last Link header.

For example, when the response to requesting a tab-separated file looks like:

HTTP/1.1 200 OK
Content-Type: text/tab-separated-values
...
Link: <metadata.json>; rel="describedBy"; type="application/csvm+json"
        

an implementation must use the referenced metadata.json to supply metadata for processing the file.

If the metadata file found at this location does not explicitly include a reference to the requested tabular data file then it MUST be ignored. URLs MUST be normalized as described in .

Link: <http://example.org/countries.csv>; rel="describes"; type="text/csv"
Link: <http://example.org/country_slice.csv>; rel="describes"; type="text/csv"

Site-wide Location Configuration

If the user has not supplied a metadata file as overriding metadata, described in , and no applicable metadata file has been discovered through a Link header, described in , processors MUST attempt to locate a metadata documents through site-wide configuration.

In this case, processors MUST retrieve the file from the well-known URI /.well-known/csvm. (Well-known URIs are defined by [[!RFC5785]].) If no such file is located, processors MUST proceed as if this file were found with the content:

{+url}-metadata.json
metadata.json
        

This file MUST contain a URI template, as defined by [[!URI-TEMPLATE]], on each line. Starting with the first such URI template, processors MUST:

1. Expand the URI template, with the variable url being set to the URL of the requested tabular data file (with any fragment component of that URL removed).
2. Resolve the resulting URL against the URL of the requested tabular data file.
3. Attempt to retrieve a metadata document at that URL.
4. If no metadata document is found at that location, or if the metadata file found at the location does not explicitly include a reference to the relevant tabular data file, perform these same steps on the next URI template, otherwise use that metadata document.

For example, if the tabular data file is at http://example.org/south-west/devon.csv then processors must attempt to locate a well-known file at http://example.org/.well-known/csvm. If that file contains:

{+url}.json
csvm.json
/csvm?file={url}
        

the processor will first look for http://example.org/south-west/devon.csv.json. If there is no metadata file in that location, it will then look for http://example.org/south-west/csvm.json. Finally, if that also fails, it will look for http://example.org/csvm?file=http://example.org/south-west/devon.csv.json.

If no file were found at http://example.org/.well-known/csvm, the processor will use the default locations and try to retrieve metadata from http://example.org/south-west/devon.csv-metadata.json and, if unsuccessful, http://example.org/south-west/metadata.json.

Embedded Metadata

Most syntaxes for tabular data provide a facility for embedding metadata within the tabular data file itself. The definition of a syntax for tabular data SHOULD include a description of how the syntax maps to an annotated data model, and in particular how any embedded metadata is mapped into the vocabulary defined in [[!tabular-metadata]]. Parsing based on the default dialect for CSV, as described in , will extract column titles from the first row of a CSV file.

The results of this can be found in .

For another example, the following tab-delimited file contains embedded metadata where it is assumed that comments may be added using a #, and that the column types may be indicated using a #datatype annotation:

# publisher City of Palo Alto
# updated 12/31/2010
#name GID on_street species trim_cycle  inventory_date
#datatype string  string  string  string  date:M/D/YYYY
  GID On Street Species Trim Cycle  Inventory Date
  1 ADDISON AV  Celtis australis  Large Tree Routine Prune  10/18/2010
  2 EMERSON ST  Liquidambar styraciflua Large Tree Routine Prune  6/2/2010
        

A processor that recognises this format may be able to extract and make sense of this embedded metadata.

Creating Annotated Tables

After locating metadata, metadata is normalized and coerced into a single table group description. When starting with a metadata file, this involves normalizing the provided metadata file and verifying that the embedded metadata for each tabular data file referenced from the metadata is compatible with the metadata. When starting with a tabular data file, this involves locating the first metadata file as described in and normalizing into a single descriptor.

If processing starts with a tabular data file, implementations:

1. Retrieve the tabular data file.
2. Retrieve the first metadata file (FM) as described in :
   1. metadata supplied by the user (see ).
   2. metadata referenced from a Link Header that may be returned when retrieving the tabular data file (see ).
   3. metadata retrieved through a site-wide location configuration (see ).
   4. embedded metadata as defined in with a single tables entry where the url property is set from that of the tabular data file.
3. Proceed as if the process starts with FM.

If the process starts with a metadata file:

1. Retrieve the metadata file yielding the metadata UM (which is treated as overriding metadata, see ).
2. Normalize UM using the process defined in Normalization in [[!tabular-metadata]], coercing UM into a table group description, if necessary.
3. For each table (TM) in UM in order, create one or more annotated tables:
   1. Extract the dialect description (DD) from UM for the table associated with the tabular data file. If there is no such dialect description, extract the first available dialect description from a group of tables in which the tabular data file is described. Otherwise use the default dialect description.
   2. If using the default dialect description, override default values in DD based on HTTP headers found when retrieving the tabular data file:
      • If the media type from the Content-Type header is text/tsv, set delimiter to TAB in DD.
      • If the Content-Type header includes the header parameter with a value of absent, set header to false in DD.
      • If the Content-Type header includes the charset parameter, set encoding to this value in DD.

   3. Parse the tabular data file, using DD as a guide, to create a basic tabular data model (T) and extract embedded metadata (EM), for example from the header line.

      This specification provides a non-normative definition for parsing CSV-based files, including the extraction of embedded metadata, in . This specification does not define any syntax for embedded metadata beyond this; whatever syntax is used, it's assumed that metadata can be mapped to the vocabulary defined in [[!tabular-metadata]].

   4. If a Content-Language HTTP header was found when retrieving the tabular data file, and the value provides a single language, set the lang inherited property to this value in TM, unless TM already has a lang inherited property.
   5. Verify that TM is compatible with EM using the procedure defined in Table Description Compatibility in [[!tabular-metadata]]; if TM is not compatible with EM validators MUST raise an error, other processors MUST generate a warning and continue processing.
   6. Use the metadata TM to add annotations to the tabular data model T as described in Section 2 Annotating Tables in [[!tabular-metadata]].

Metadata Compatibility

When processing a tabular data file using metadata as discovered using , processors MUST ensure that the metadata and tabular data file are compatible, this is typically done by extracting embedded metadata from the tabular data file and determining that the provided or discovered metadata is compatible with the embedded metadata using the procedure defined in Table Compatibility in [[!tabular-metadata]].

URL Normalization

Metadata Discovery and Compatibility involve comparing URLs. When comparing URLs, processors MUST use Syntax-Based Normalization as defined in [[!RFC3968]]. Processors MUST perform Scheme-Based Normalization for HTTP (80) and HTTPS (443) and SHOULD perform Scheme-Based Normalization for other well-known schems.

What is generally considered good practice for this in other specifications?

Parsing Cells

Unlike many other data formats, tabular data is designed to be read by humans. For that reason, it's common for data to be represented within tabular data in a human-readable way. The datatype, default, lang, null, required, and separator annotations provide the information needed to parse the string value of a cell into its (semantic) value annotation. This is used:

• by validators to check that the data in the table is in the expected format,
• by converters to parse the values before mapping them into values in the target of the conversion,
• when displaying data, to map it into formats that are meaningful for those viewing the data (as opposed to those publishing it), and
• when inputting data, to turn entered values into representations in a consistent format.

The process of parsing a cell creates a cell with annotations based on the original string value, parsed value and other column annotations and adds the cell to the list of cells in a row and cells in a column:

• The raw string value becomes the string value annotation on the cell.
• The ordered annotation on the column becomes the ordered annotation on the cell.
• The text direction annotation on the column becomes the text direction annotation on the cell.
• The row becomes the row annotation on the cell.
• The column becomes the column annotation on the cell.

After parsing, the cell value can be:

• null,
• a single value with an associated optional datatype or language, or
• a sequence of such values.

The process of parsing the string value into a single value or a list of values is as follows:

1. unless the datatype base is string, json, xml, html or anyAtomicType, replace all carriage return (#xD), line feed (#xA), and tab (#x9) characters with space characters.
2. unless the datatype base is string, json, xml, html, anyAtomicType, or normalizedString, strip leading and trailing whitespace from the string value and replace all instances of two or more whitespace characters with a single space character.
3. if the normalized string is an empty string, apply the remaining steps to the string given by the column default annotation.
4. if the column separator annotation is not null and the normalized string is an empty string, the cell value is an empty list. If the column required annotation is true, add an error to the list of errors for the cell.
5. if the column separator annotation is not null, the cell value is a list of values; set the list annotation on the cell to true, and create the cell value created by:
   1. if the normalized string is the same as any one of the values of the column null annotation, then the resulting value is null.
   2. split the normalized string at the character specified by the column separator annotation.
   3. unless the datatype base is string or anyAtomicType, strip leading and trailing whitespace from these strings.
   4. applying the remaining steps to each of the strings in turn.
6. if the string is an empty string, apply the remaining steps to the string given by the column default annotation.
7. if the string is the same as any one of the values of the column null annotation, then the resulting value is null. If the column separator annotation is null and the column required annotation is true, add an error to the list of errors for the cell.
8. parse the string using the datatype format if one is specified, as described below to give a value with an associated datatype. If the datatype base is string, or there is no datatype, the value has an associated language from the column lang annotation. If there are any errors, add them to the list of errors for the cell; in this case the value has a datatype of string; if the datatype base is string, or there is no datatype, the value has an associated language from the column lang annotation.
9. validate the value based on the length constraints described in , the value constraints described in and the datatype format annotation if one is specified, as described below. If there are any errors, add them to the list of errors for the cell.

The final value (or values) become the value annotation on the cell.

If there is a about URL annotation on the column, it becomes the about URL annotation on the cell, after being transformed into an absolute URL as described in URI Template Properties of [[!tabular-metadata]].

If there is a property URL annotation on the column, it becomes the property URL annotation on the cell, after being transformed into an absolute URL as described in URI Template Properties of [[!tabular-metadata]].

If there is a value URL annotation on the column, it becomes the value URL annotation on the cell, after being transformed into an absolute URL as described in URI Template Properties of [[!tabular-metadata]]. The value URL annotation is null if the cell value is null and the column virtual annotation is false.

  "datatype": "integer"
          

  "datatype": {
    "base": "integer",
    "minimum": 1,
    "maximum": 10
  },
  "null": "99"
          

  "datatype": {
    "base": "integer",
    "minimum": 1,
    "maximum": 10
  },
  "default": "5"
          

  "datatype": {
    "base": "integer",
    "minimum": 1,
    "maximum": 10
  },
  "default": "5",
  "separator": " "
          

  "datatype": "decimal",
  "separator": " ",
  "valueUrl": "{?values}"
          

Validating Tables

Validators test whether given tabular data files adhere to the structure defined within a schema. Validators MUST raise errors as defined in [[!tabular-metadata]] and in the following situations:

• if the table description is not compatible with the embedded metadata extracted from the tabular data file, as defined in Table Compatibility in [[!tabular-metadata]].
• if there is more than one row with the same primary key, that is where the cells listed for the primary key for the row have the same values as the cells listed for the primary key for another row,
• for each row that does not have a referenced row for each of the foreign keys on the table in which the row appears, or
• for each error on each cell.

Processors MAY continue validating after encountering the first error.

Converting Tables

Conversions of tabular data to other formats operate over a annotated table constructed as defined in Annotating Tables in [[!tabular-metadata]]. The mechanics of these conversions to other formats are defined in other specifications such as [[csv2json]] and [[csv2rdf]].

Conversion specifications MUST define a default mapping from an annotated table that lacks any annotations (i.e., that is equivalent to an un-annotated table).

Conversion specifications MUST use the property value of the propertyUrl of a column as the basis for naming machine-readable fields in the target format, such as the name of the equivalent element or attribute in XML, property in JSON or property URI in RDF.

Conversion specifications MAY use any of the annotations found on an annotated table group, table, column, row or cell, including non-core annotations, to adjust the mapping into another format.

Conversion specifications MAY define additional annotations, not defined in this specification, which are specifically used when converting to the target format of the conversion. For example, a conversion to XML might specify a http://example.org/conversion/xml/element-or-attribute property on columns that determines whether a particular column is represented through an element or an attribute in the data.