Validation, conversion, display and search of tabular data on the web requires additional metadata that describes how the data should be interpreted. This document defines a vocabulary for metadata that annotates tabular data. This can be used to provide metadata at various levels, from collections of data from CSV documents and how they relate to each other down to individual cells within a table.

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 document aims to primarily satisfy the second of those Recommendations.

Introduction

Interpreting tabular data that is available on the web, particularly as CSV, usually requires additional metadata. As an example, say that the following CSV file were available at http://example.org/tree-ops.csv

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
3,EMERSON ST,Liquidambar styraciflua,Large Tree Routine Prune,6/2/2010
      

A human consumer of this data might be able to figure out the meaning of the different columns, particularly if there were some additional human-readable documentation made available. Automated processors would have a much harder time; realistically they would be limited to displaying the information in a table. Making available machine-readable metadata helps with the interpretation of the tabular data. For example, say that the following metadata file were available at http://example.org/trees-ops.csv-metadata.json:

{
  "@id": "tree-ops.csv",
  "@context": {
    "@language": "en"
  }
  "dc:title": "Tree Operations",
  "dc:keywords": ["tree", "street", "maintenance"],
  "dc:publisher": [{
    "sch:name": "Example Municipality",
    "sch:web": "http://example.org"
  }],
  "dc:license": "http://opendefinition.org/licenses/cc-by/",
  "dc:modified": "2010-12-31",
  "schema": {
    "columns": [{
      "name": "GID",
      "title": [
        "GID",
        "Generic Identifier"
      ],
      "dc:description": "An identifier for the operation on a tree.",
      "datatype": "string",
      "required": true,
      "unique": true
    }, {
      "name": "on-street",
      "title": "On Street",
      "dc:description": "The street that the tree is on.",
      "datatype": "string"
    }, {
      "name": "species",
      "title": "Species",
      "dc:description": "The species of the tree.",
      "datatype": "string"
    }, {
      "name": "trim-cycle",
      "title": "Trim Cycle",
      "dc:description": "The operation performed on the tree.",
      "datatype": "string"
    }, {
      "name": "inventory-date",
      "title": "Inventory Date",
      "dc:description": "The date of the operation that was performed.",
      "datatype": "date",
      "format": "M/D/YYYY"
    }]
    "primaryKey": "GID"  
  }
}
      

Given the location of the CSV file, this metadata document can be located by appending -metadata.json to the URL (as described in Model for Tabular Data and Metadata on the Web). It provides information for different types of applications:

The Model for Tabular Data and Metadata on the Web specification defines an Annotated Tabular Data Model in which tables, columns, rows and cells can be annotated with properties and values, and a Grouped Tabular Data Model in which a group of tables is annotated. That specification also describes how to locate metadata about a given CSV file.

This document defines the format and structure of metadata documents, and how these are interpreted to create an Annotated Tabular Data Model. It also defines how to validate tabular data based on some of these annotations. This metadata can be expressed as an RDF graph. However, all applications that conform to this specification (including validators and applications that read or convert tabular data) MUST read the JSON-based format described in this document.

We are aiming for the JSON format to be interpretable as JSON-LD, but without any requirement to include context within the JSON itself (to save people from having to do boilerplate). We invite comments on the utility of this approach: is it useful for CSV metadata to be interpretable as JSON-LD? Is it helpful to be able to map it to RDF? Would it be better to rename some of the JSON-LD keywords, such as @id and @type? GitHub >

Annotating Tables

The metadata defined in this specification is used to annotate an existing annotated table or group of tables, as defined in [[!tabular-data-model]]. Annotated tables form the basis for all further processing, such as validating or displaying the tables. All compliant applications MUST create annotated tables based on the algorithm defined here.

Metadata documents contain descriptions of groups of tables, tables, columns, rows, cells and regions which are used to create annotations on a tabular data model. There are two types of description objects:

The description objects themselves contain a number of properties. These are:

For example, in the column description

{
  "name": "inventory-date",
  "title": "Inventory Date",
  "dc:description": "The date of the operation that was performed.",
  "datatype": "date",
  "format": "M/D/YYYY"
}
      

the properties name, title and dc:description are direct annotations that become name, title and dc:description properties on the column in the data model. The datatype and format properties are inherited properties that become datatype and format properties on the cells within the column.

Direct Annotations

Direct annotations are properties on the description object for a given table, column, row or cell which map directly to properties on the described table, column, row or cell. The name of the annotation is the same as the name of the property on the annotation. The value of the annotation is the same as the value of the property on the description object.

Inherited Properties

A cell may be assigned annotations based on properties on the description objects for the group of tables, table, column or row that it appears in. These properties are known as inherited properties and are listed in . To ascertain a value for these annotations, an application MUST identify the relevant property in the descriptions of the table or column.

Applications MUST raise an error if the value of a property in a table description is not compatible with the value of that property on the group of tables. Applications MUST raise an error if the value of a property in a column description is not compatible with the value of that property on the table. Applications MUST raise an error if the value of a property on a cell is not compatible with the values of that property on the column that the cell is associated with.

A value for a cell, column or table is compatible with with a value on a column, table or group of tables if they are the same value or if the first value is a sub-value of the second value. The definitions of individual inherited properties indicate what values count as sub-values of others.

Metadata Format

This section defines a set of properties and permitted values for annotating tabular data, and how these annotations should be interpreted by applications.

A metadata document is a JSON document which holds an object at the top level. This object is a description object of either a table group or a single table. A description object is a JSON object that describes a component of the tabular data model (a table group, a table, a column, a row or a cell) and has one or more properties are mapped into properties on that component.

Property Syntax

There are different types of properties on description objects:

link properties

These hold one or more references to other resources by URL. Their values may be:

  • strings — resolved as URLs against the base URL
  • arrays — lists of strings which are resolved as URLs against the base URL

For example, the dc:hasVersion property is a link property. A table description might contain:

"dc:hasVersion": "example-2014-01-03.csv"
            

in which case the dc:hasVersion property on the table would have a single value, a link to example-2014-01-03.csv. Alternatively, the metadata document might contain:

"dc:hasVersion": [
  "example-2014-01-03.csv",
  "example-2014-01-17.csv",
  "example-2014-01-25.csv"
]
            

in which case the dc:hasVersion property on the table would be an array of three values, links to other versions of the table.

URI template properties

A URI template property contains a [[!URI-TEMPLATE]] which can be used to generate a URI. These URI templates are expanded in the context of each row by combining the template with a set of variables with values. The variables that are set are:

_row
_row is set to the row number of the row that is currently being processed
column names
a variable is set for each column within the schema; the name of the variable is the percent-encoded name of the column and the value is the canonical representation of the value of the cell in that column in the row that is currently being processed

For example, the urlTemplate property holds a URI template that is used to generate a URL identifier for each row, which might look like:

"urlTemplate": "http://example.org/example.csv#row={_row}"
            

The identifiers that are generated for the rows would then look like http://example.org/example.csv#row=1, http://example.org/example.csv#row=2 and so on.

Alternatively, with the CSV and metadata in the , the urlTemplate might look like:

"urlTemplate": "http://example.org/tree/{on%2Dstreet}/{GID}"
            

This would generate URIs such as http://example.org/tree/ADDISON%20AV/1 and http://example.org/tree/EMERSON%20ST/2.

Once the URI has been generated, it is resolved against the location of the resource (eg the CSV file) to create an absolute URI. For example, given a urlTemplate within a schema such as:

"urlTemplate": "#row={_row}"
            

and given a CSV file at http://example.com/temp.csv, the URL for the first row will be http://example.com/temp.csv#row=1.

column reference properties

These hold one or more references to other column description objects. The referenced description object must have an name property. Column reference properties can then reference column description objects through values that are:

  • strings — which MUST match the name on a column description object within the metadata document
  • arrays — lists of strings as above

For example, the primaryKey property is an column reference property on the schema. It has to hold references to columns defined elsewhere in the schema, and the descriptions of those columns must have name properties. It can hold a single reference, like this:

"schema": {
  "columns": [{
    "name": "GID"
  }, ... ],
  "primaryKey": "GID"
}
            

or it can contain an array of references, like this:

"schema": {
  "columns": [{
    "name": "givenName"
  }, {
    "name": "familyName"
  }, ... ],
  "primaryKey": [ "givenName", "familyName" ]
}
            
object properties

These hold one or more objects or references to objects by URL. Their values may be:

  • strings — resolved as URLs against the base URL
  • objects — interpreted as structured objects
  • arrays — lists of strings and/or objects, interpreted as URLs or structured objects

Object properties are often used when the values can be or should be values within controlled vocabularies, or structured information which may be held elsewhere. For example, the dc:creator of a table should be an object property. It could be provided as a URL that indicates the creator, like this:

"dc:creator": "http://ons.gov.uk"
            

or a structured object, like this:

"dc:creator": {
  "sch:name": "Office of National Statistics",
  "sch:url": "http://ons.gov.uk",
  "sch:email": "info@ons.gsi.gov.uk"
}
            

or an array of URLs, like this:

"dc:creator": [ "http://ons.gov.uk", "https://www.gov.uk/government/organisations/department-for-transport" ]
            

or an array of structured objects:

"dc:creator": [{
  "sch:name": "Office of National Statistics",
  "sch:url": "http://ons.gov.uk",
  "sch:email": "info@ons.gsi.gov.uk"
}, {
  "sch:name": "Department for Transport",
  "sch:url": "https://www.gov.uk/government/organisations/department-for-transport"
}]
            

or an array that mixes URLs and objects:

"dc:creator": [{
  "sch:name": "Office of National Statistics",
  "sch:url": "http://ons.gov.uk",
  "sch:email": "info@ons.gsi.gov.uk"
}, "https://www.gov.uk/government/organisations/department-for-transport" ]
            
natural language properties

These hold natural language strings. Their values may be:

  • strings — interpreted as natural language strings in the default language
  • arrays — interpreted as alternative natural language strings in the default language
  • objects whose properties MUST be language codes as defined by [[!RFC3066]] and whose values are either strings or arrays, providing natural language strings in that language

Natural language properties are used for things like descriptions and titles. For example, the title property provides a natural language label for a column. If it's a plain string like this:

"title": "Project title"
            

then that string is assumed to be in the language provided through the @language property of the nearest @context (or have no assumed language, if there is no such property). Multiple alternative values can be given in an array:

"title": [
  "Project title",
  "Project"
]
            

It's also possible to provide multiple values in different languages, using an object structure. For example:

"title": {
  "en": "Project title",
  "fr": "Titre du projet"
}
            

and within such an object, the values of the properties can themselves be arrays:

"title": {
  "en": [ "Project title", "Project" ],
  "fr": "Titre du projet"
}
            

We invite comment on whether it would be useful to enable some markup in natural language strings, for example by stating that they are interpreted as HTML or Markdown. GitHub >

atomic properties

These hold atomic values. Their values may be:

  • numbers — interpreted as integers or doubles
  • booleans — interpreted as booleans (true or false)
  • strings — interpreted as defined by the property
  • arrays — lists of numbers, booleans or strings

JSON does not have date or time types. Where a property takes a date as a value, this MUST be a string in the format YYYY-MM-DD.

Top-Level Properties

The top-level object (whether it is a table group description or a table description) MAY have a @context property. This holds an object that provides metadata for interpreting other properties, namely:

@language

indicates the default language for the values of properties in the metadata document; if present, its value MUST be a language code [[!BCP47]] which is the default language for the values of other properties in the metadata document

Note that the @language property of the @context object, which gives the default language used within the metadata file, is distinct from the language property on a description object, which gives the language used in the data within a group of tables, table or column.

@base

indicates the base URL against which other URLs within the description are resolved; if present, its value MUST be a URL which is resolved against the base URL of the metadata document (the location from which it was retrieved) to provide the base URL for other URLs in the metadata document

Note that the @base property of the @context object provides the base URL used for URLs within the metadata document, not the URLs that appear within the group of tables or table it describes.

Common Properties

Descriptions of groups of tables, tables, schemas, columns, rows and cells MAY contain any properties whose names are either absolute URLs or prefixed names. For example, a table description may contain dc:description, dcat:keyword or schema:copyrightHolder properties to provide a description, keywords or the name of the copyright holder, as defined in Dublin Core Terms, DCAT or schema.org.

The same prefixes are pre-defined as for [[rdfa-core]] within the RDFa 1.1 Initial Context and MUST NOT be overridden. Properties from other vocabularies MUST be defined using full URLs.

Forbidding the declaration of new prefixes ensures consistent processing between JSON-LD-aware and non-JSON-LD-aware processors.

Table Groups

A table group description is a JSON object that describes a group of tables.

Required Properties

resources
An array of table descriptions for the tables in the group.

Optional Properties

The description of a group of tables MAY also contain:

schema
An object property that provides a schema description as described in , for all the tables in the group. This may be provided as an embedded object within the JSON metadata or as a URL reference to a separate JSON schema document.
table-direction

One of "rtl", "ltr" or "default". Indicates whether the tables in the group should be displayed with the first column on the right, on the left, or based on the first character in the table that has a specific direction. See for more details.

dialect
If provided, dialect provides hints to processors about how to parse the referenced files for to create tabular data models for the tables in the group. See for more details.
templates
An array of template specifications that provide mechanisms to transform the tabular data into other formats. See for more details.
@type
If included, @type MUST be set to "TableGroup". Publishers MAY include this to provide additional information to JSON-LD based toolchains.

The description MAY contain any common properties as defined in to provide extra metadata about the set of tables as a whole.

The description MAY contain any of the properties defined in to describe cells within the tables.

Tables

A table description is a JSON object that describes a table within a CSV file.

A CSV file might not be the same as the table that it contains. For example, a given CSV file might contain two tables (in different regions of the CSV file), or might contain a table that isn't positioned at the top left of the CSV file. We invite comment about whether we should assume that pre-processing is used to extract tables where there isn't a 1:1 correspondence between CSV file and table, or not. GitHub >

Required Properties

@id

This gives the URL of the CSV file that the table is held in, relative to the location of the metadata document.

Optional Properties

The description of a table MAY also contain:

schema
An object property that provides a schema description as described in . This may be provided as an embedded object within the JSON metadata or as a URL reference to a separate JSON schema document.
notes

An array of objects representing annotations. This specification does not place any constraints on the structure of these objects.

The Web Annotation Working Group is developing a vocabulary for expressing annotations. In future versions of this specification, we anticipate referencing that vocabulary.

table-direction

One of "rtl", "ltr" or "default". Indicates whether the table should be displayed with the first column on the right, on the left, or based on the first character in the table that has a specific direction. See for more details.

This should be a defined controlled vocabulary in JSON-LD, so that the values map on to URIs in the RDF version rather than strings. We invite comment on how to configure the JSON-LD context to enable these values to be interpreted in this way. GitHub >

templates
An array of template specifications that provide mechanisms to transform the tabular data into other formats. See for more details.
dialect
If provided, dialect provides hints to processors about how to parse the referenced file to create a tabular data model. See for more details.
@type
If included, @type MUST be set to "Table". Publishers MAY include this to provide additional information to JSON-LD based toolchains.

We invite comment on whether we should include properties that help in checking the integrity of the file: datapackage includes bytes and hash. We could reuse the Subresource Integrity work here. GitHub >

The description MAY contain any common properties as defined in to provide extra metadata about the table as a whole.

The description MAY contain any of the properties defined in to describe cells within the table.

Dialect Descriptions

Much of the tabular data that is published on the web is messy, and CSV parsers frequently need to be configured in order to correctly read in CSV. A dialect description provides hints to parsers about how to parse the file linked to from the @id property. It can have any of the following properties, which relate to the flags described in Section 5 Parsing Tabular Data within [[!tabular-data-model]]:

encoding
Sets the encoding flag to the provided value, which MUST be a defined [[!encoding]].
lineTerminator
Sets the line terminator flag to the provided string value.
quoteChar
Sets the quote character flag to the provided value, which MUST be a single character.
doubleQuote
If true, sets the escape character flag to ". If false, to \.
skipRows
Sets the skip rows flag to the provided numeric value, which MUST be a non-negative integer.
commentPrefix
Sets the comment prefix flag to the provided value, which MUST be a single character string.
header
If true, sets the header row count flag to 1, and if false to 0, unless headerRowCount is provided, in which case the value provided for the header property is ignored.
headerRowCount
Sets the header row count flag to the provided value, which MUST be a non-negative integer.
delimiter
Sets the delimiter flag to the provided value, which MUST be a single character string.
skipColumns
Sets the skip columns flag to the provided numeric value, which MUST be a non-negative integer.
headerColumnCount
Sets the header column count flag to the provided value, which MUST be non-negative integer.
skipBlankRows
Sets the skip blank rows flag to the provided boolean value.
skipInitialSpace
If true, sets the trim flag to "start". If false, to false. If the trim property is provided, the skipInitialSpace property is ignored.
trim
If the boolean true, sets the trim flag to true and if the boolean false to false. If the value provided is a string, sets the trim flag to the provided value, which MUST be one of "true", "false", "start" or "end".
@type
If included, @type MUST be set to "Dialect". Publishers MAY include this to provide additional information to JSON-LD based toolchains.

The default dialect description for CSV files is:

{
  "encoding": "utf-8",
  "lineTerminator": "\r\n",
  "quoteChar": "\"",
  "doubleQuote": true,
  "skipRows": 0,
  "header": true,
  "headerRowCount": 1,
  "delimiter": ",",
  "skipColumns": 0,
  "headerColumnCount": 0,
  "skipBlankRows": false,
  "skipInitialSpace": false,
  "trim": false
}
          

Template Specifications

A template specification is a definition of how tabular data can be transformed into another format. It has the following properties:

Required Properties

Template specifications MUST have the following properties:

targetFormat

A URL for the format that will be created through the transformation. If one has been defined, this should be a URL for a media type, in the form http://www.iana.org/assignments/media-types/media-type such as http://www.iana.org/assignments/media-types/text/calendar. Otherwise, it can be any URL that describes the target format.

The targetFormat URL is intended as an informative identifier for the target format, and applications MAY NOT access the URL.

templateFormat

A URL for the format that is used by the template. If one has been defined, this should be a URL for a media type, in the form http://www.iana.org/assignments/media-types/media-type such as http://www.iana.org/assignments/media-types/application/javascript. Otherwise, it can be any URL that describes the template format.

The templateFormat URL is intended as an informative identifier for the template format, and applications MAY NOT access the URL. The template formats that an application supports are implementation defined.

Optional Properties

Template specifications MAY have the following properties:

title
A natural language property that describes the format that will be generated from the transformation. This is useful if the target format is a generic format (such as application/json) and the transformation is creating a specific profile of that format.
source
If included, the format to which the tabular data should be transformed prior to the transformation using the template. If the value is "json", the tabular data should first be transformed first to JSON based on the simple mapping defined in Generating JSON from Tabular Data on the Web. If the value is "rdf", it should similarly first be transformed to XML based on the simple mapping defined in Generating RDF from Tabular Data on the Web. If the source property is missing or null then the source of the transformation is the annotated tabular data model.
@type
If included, @type MUST be set to "Template". Publishers MAY include this to provide additional information to JSON-LD based toolchains.

The template specification MAY contain any common properties as defined in to provide extra metadata about the transformation.

Example

The following template specification will enable a processor that supports it to generate an iCalendar document using a Mustache template based on the JSON created from the simple mapping to JSON.

{
  "title": "iCalendar",
  "targetFormat": "http://www.iana.org/assignments/media-types/text/calendar",
  "templateFormat": "https://mustache.github.io/",
  "source": "json"
} 
          

Schemas

A schema is a definition of a tabular format that may be common to multiple tables. For example, multiple tables from different sources may have the same columns and be designed such that they can be aggregated together.

A schema description is a JSON object that encodes the information about a schema. All the properties of a schema description are optional.

columns

An array of column descriptions as described in . These are matched to columns in table that use the schema by position: the first column description in the array applies to the first column in the table, the second to the second and so on.

The name properties of the column descriptions MUST be unique within a given table description.

primaryKey

A column reference property that holds either a single reference to a column description object or an array of references.

Validators MUST check that each row has a unique combination of cells in the indicated columns. For example, if primaryKey is set to ["familyName", "givenName"] then every row must have a unique value for the combination of the familyName and givenName columns.

foreignKeys

An array of foreign key definitions that define how columns within this table link to columns within other tables. A foreign key definition is a JSON object with the properties:

columns
A column reference property that holds either a single reference to a column description object within this schema, or an array of references.
reference

An object with the properties:

resource
A URL that is the identifier for a specific resource that is being referenced. If this is present then schema MUST NOT be present. The metadata document MUST contain a description of the resource.
schema
A URL that is the identifier for a schema that is being referenced. If this is present then resource MUST NOT be present. The metadata document that forms the basis of processing MUST contain a description of a resource that uses the referenced schema, and there MUST NOT be more than one such resource.
columns
A column reference property that holds either a single reference to a column description object within this schema, or an array of references.

It is not required for the resource or schema referenced from a foreignKeys property to have a similarly defined primaryKey.

urlTemplate
A URI template property that MAY be used to create a unique identifier for each row when mapping data to other formats.
@type
If included, @type MUST be set to "Schema". Publishers MAY include this to provide additional information to JSON-LD based toolchains.

The description MAY contain any common properties as defined in to provide extra metadata about the schema as a whole.

The description MAY contain any of the inherited properties defined for cells in .

Examples

Foreign Key Reference Between Resources

A list of countries is published at http://example.org/countries.csv with the structure:

countryCode,latitude,longitude,name
AD,42.546245,1.601554,Andorra
AE,23.424076,53.847818,"United Arab Emirates"
AF,33.93911,67.709953,Afghanistan
            

Another file contains information about the population in some countries each year, at http://example.com/country_slice.csv with the structure:

countryRef,year,population
AF,1960,9616353
AF,1961,9799379
AF,1962,9989846
            

The following metadata for the group of tables links the two together by defining a foreignKeys property:

{
  "resources": [{
    "@id": "https://example.org/countries.csv",
    "schema": {
      "columns": [{
        "name": "countryCode",
        "datatype": "string"
      }, {
        "name": "latitude",
        "datatype": "number"
      }, {
        "name": "longitude",
        "datatype": "number"
      }, {
        "name": "name",
        "datatype": "string"
      }],
      "urlTemplate": "http://example.org/countries.csv{#countryCode}",
      "primaryKey": "country"
    }
  }, {
    "@id": "http://example.com/country_slice.csv",
    "schema": {
      "columns": [{
        "name": "countryRef",
        "datatype": "string"
      }, {
        "name": "year",
        "datatype": "gYear"
      }, {
        "name": "population",
        "datatype": "integer"
      }],
      "foreignKeys": [{
        "columns": "countryRef",
        "reference": {
          "resource": "http://example.org/countries.csv",
          "columns": "countryCode"
        }
      }]
    }
  }]
}
            

When the population data in country_slice.csv is processed (displayed or mapped into another format), a link can be made from the content of the countryRef column based on the urlTemplate for country.csv. For example, if the countryRef column (the value of columns in the foreignKeys object) in country_slice.csv contains the value UK then the processor will use that value to populate the countryCode variable (the value of reference.columns in the foreignKeys object) when interpreting the urlTemplate for country.csv, and create the URL http://example.org/countries.csv#UK. The processor does not need to retrieve http://example.org/countries.csv or check that the value UK appears within the countryCode column to create this link: it is created purely based on the urlTemplate in the description of the referenced resource.

Foreign Key Reference Between Schemas

When publishing information about public sector roles and salaries, as in Use Case 4, the UK government requires departments to publish two files which are interlinked. The first lists senior grades (simplified here) eg at HEFCE_organogram_senior_data_31032011.csv:

Post Unique Reference,              Name,Grade,             Job Title,Reports to Senior Post
                90115,        Steve Egan,SCS1A,Deputy Chief Executive,                 90334
                90250,     David Sweeney,SCS1A,              Director,                 90334
                90284,       Heather Fry,SCS1A,              Director,                 90334
                90334,Sir Alan Langlands, SCS4,       Chief Executive,                    xx
            

The second provides information about the number of junior positions that report to those individuals (simplified here) eg at HEFCE_organogram_junior_data_31032011.csv:

Reporting Senior Post,Grade,Payscale Minimum (£),Payscale Maximum (£),Generic Job Title,Number of Posts in FTE,          Profession
                90284,    4,               17426,               20002,    Administrator,                     2,Operational Delivery
                90284,    5,               19546,               22478,    Administrator,                     1,Operational Delivery
                90115,    4,               17426,               20002,    Administrator,                  8.67,Operational Delivery
                90115,    5,               19546,               22478,    Administrator,                   0.5,Operational Delivery
            

The schemas are reused by multiple departments and for multiple pairs of files. The schemas are therefore defined in separate files, and they need to define links between the schemas which are then picked up as applying between tables that use those schemas.

The metadata file for the particular publication of the files above is:

{
  "resources": [{
    "@id": "HEFCE_organogram_senior_data_31032011.csv",
    "schema": "http://example.org/schema/senior-roles.json"
  }, {
    "@id": "HEFCE_organogram_junior_data_31032011.csv",
    "schema": "http://example.org/schema/junior-roles.json"
  }]
}
            

The schema for the senior role CSV (at http://example.org/schema/senior-roles.json) is as follows; it includes a foreign key reference to itself:

{
  "@id": "http://example.org/schema/senior-roles.json",
  "columns": [{
    "name": "ref",
    "title": "Post Unique Reference"
  }, {
    "name": "name",
    "title": "Name"
  }, {
    "name": "grade",
    "title": "Grade"
  }, {
    "name": "job",
    "title": "Job Title"
  }, {
    "name": "reportsTo",
    "title": "Reports to Senior Post"
  }],
  "primaryKey": "ref",
  "urlTemplate": "#post-{ref}",
  "foreignKeys": [{
    "columns": "reportsTo",
    "reference": {
      "schema": "http://example.org/schema/senior-roles.json",
      "columns": "ref"
    }
  }]
}
            

The schema for the junior role CSV (at http://example.org/schema/junior-roles.json) is as follows; it includes a foreign key reference to the senior roles schema:

{
  "@id": "http://example.org/schema/junior-roles.json",
  "columns": [{
    "name": "reportsTo",
    "title": "Reporting Senior Post"
  }, 
  ...
  ],
  "foreignKeys": [{
    "columns": "reportsTo",
    "reference": {
      "schema": "http://example.org/schema/senior-roles.json",
      "columns": "ref"
    }
  }]
}
            

In the first line of HEFCE_organogram_junior_data_31032011.csv, the reportsTo (Reporting Senior Post) column contains the value 90284. When creating a link from that column, the urlTemplate defined within the schema at http://example.org/schema/senior-roles.json is used to generate a URL by expanding the variable reference for ref based on the value from the reportsTo column. This gives the relative URL #post-90284 which is then resolved against the base URL of the resource that uses the senior-roles.json schema within the original metadata file, namely HEFCE_organogram_senior_data_31032011.csv.

Columns

A column description is a simple JSON object that describes a single column. The description provides additional human-readable documentation for a column, as well as additional information that may be used to validate the cells within the column, create a user interface for data entry, or inform conversion into other formats.

Required Properties

name

An atomic property that gives a canonical name for the column. This MUST be a string. Conversion specifications MUST use this property as the basis for the names of properties/elements/attributes in the results of conversions.

For ease of reference within URI template properties, column names SHOULD consist only of alphanumeric characters or underscores ([a-zA-Z0-9_]+). Names beginning with _ are reserved by this specification and MUST NOT be used.

We invite comment on what the syntactic limitations should be on column names to make them most useful when used as the basis of conversion into other formats, bearing in mind that different target languages such as JSON, RDF and XML have different syntactic limitations and common naming conventions. GitHub >

During validation, if there is no title property and the column already has a title annotation then a validator MUST issue a warning if the existing title annotation does not match the name specified in the column description.

Optional Properties

title

A natural language property that provides possible alternative names for the column. The possible column titles are defined as:

  • if the value of title is a string, that string
  • if the value of title is an array, the strings in that array
  • if the value of title is an object, the string or strings that are the value of the property of that object whose name is the column language

where the column language is the value of the language property on the column description, or (if there is no such language), the value of the language property on the table description.

If the column already has a title annotation (because a header row has been included in the original CSV file) then a validator MUST issue a warning if the existing title annotation is not the same as any of the possible column titles.

The facility to specify multiple potential titles for a column is important when the same column description is used for multiple CSVs, through a mechanism yet to be defined by this specification.

required
A boolean value which indicates whether every cell within the column must have a non-null value.
@type

If included, @type MUST be set to "Column". Publishers MAY include this to provide additional information to JSON-LD based toolchains.

The description MAY contain any common properties as defined in to provide extra metadata about the column as a whole, such as a full description.

The description MAY contain any of the inherited properties defined for cells in .

Inherited Properties

Cell descriptions may override inherited properties, as described in . It is good practice to define these properties on columns, so that all cells within a given column are handled in the same way, or on tables if appropriate. These properties are:

null

The string used for null values. If not specified, the default for this is the empty string.

language

A language code as defined by [[!BCP47]]. Indicates the language of the value within the cell.

text-direction

One of "rtl" or "ltr" (the default). Indicates whether the text within cells should be displayed by default as left-to-right or right-to-left text. See for more details.

separator

The character used to separate items in the string value of the cell. If null or unspecified, the cell does not contain a list. Otherwise, application MUST split the string value of the cell on the specified separator character and parse each of the resulting strings separately. The cell's value will then be a list. Conversion specifications MUST use the separator to determine the conversion of a cell into the target format. See for more details.

format

A definition of the format of the cell, used when parsing the cell as described in .

datatype

The main datatype of the values of the cell. If the cell contains a list (ie separator is specified and not null) then this is the datatype of each value within the list. Conversion specifications MUST use the datatype of the value to determine the conversion of a cell into the target format. See for more details.

length

The exact length of the value of the cell. See for details.

minLength

The minimum length of the value of the cell. See for details.

maxLength

The maximum length of the value of the cell. See for details.

minimum

The minimum value for the cell (inclusive); equivalent to minInclusive. See for details.

maximum

The maximum value for the cell (inclusive); equivalent to maxInclusive. See for details.

minInclusive

The minimum value for the cell (inclusive). See for details.

maxInclusive

The maximum value for the cell (inclusive). See for details.

minExclusive

The minimum value for the cell (exclusive). See for details.

maxExclusive

The maximum value for the cell (exclusive). See for details.

Datatypes

Cells within tables may be annotated with a datatype which indicates the type of the value obtained by parsing the value of the cell. The format expected in the cell is determined by the format annotation, if there is one, or uses a default format determined by the type.

The possible datatypes are:

Length Constraints

The length, minLength and maxLength properties indicate the exact, minimum and maximum lengths of the values of cells.

Applications MUST raise an error if both length and minLength are specified and they do not have the same value. Similarly, applications MUST raise an error if both length and maxLength are specified and they do not have the same value. Applications MUST raise an error if length, maxLength or minLength are specified and the cell value is not a list (ie separator is not specified), a string or one of its subtypes, or a binary value.

The length of a value of a cell is determined as follows:

  • if the cell is null its length is zero
  • if the value is a list, its length is the number of items in the list
  • if the value is a string or one of its subtypes, its length is the number of characters in the value
  • if the value is of a binary type, its length is the number of bytes in the binary value

Value Constraints

The minimum, maximum, minInclusive, maxInclusive, minExclusive and maxExclusive properties indicate limits on the values of cells. These apply to numeric and date/time types. The minimum property is equivalent to the minInclusive property and the maximum property is equivalent to the maxInclusive property.

Validation against these properties is as defined in [[!xmlschema-2]].

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 separator and format properties indicates the format used to represent data within the table. This is used:

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

  1. unless the datatype is string or anySimpleType or any, strip leading and trailing whitespace from the value
  2. if the value is the same as the null value, then the value is null
  3. if the separator property is not null, create a list of values by splitting the string at the character specified by the separator property
  4. validate the value(s) against the format, if one is specified, as described below; raise an error if any of the values do not match the specified format
  5. parse the value(s) using the format, as described below

Formats for strings

If the datatype is a string type, the format property provides a regular expression for the string values, in the syntax defined by [[!ECMASCRIPT]].

We invite comment about which reference to use for regular expression syntax. Other possibilities are to use that defined by XML Schema or XPath. GitHub >

Formats for numeric types

It is not uncommon for numbers within tabular data to be formatted for human consumption, which may involve using commas for decimal points, grouping digits in the number using commas, or adding currency symbols or percent signs to the number.

If the datatype is a numeric type, the format property indicates the expected format for that number. Validators MUST check that the numbers in the column adhere to the specified format. Converters MUST use the format property to parse the number when mapping it into a suitable type in the target language of the conversion.

When the datatype is a numeric type, the format property's value MUST be a number format as specified in [[!xslt-21]].

We invite comment on the best format to specify how to parse numbers. GitHub >

Formats for booleans

Boolean values may be represented in many ways aside from the standard 1 and 0 or true and false.

If the datatype is boolean, the format property provides the true and false values expected, separated by |. For example if format is Y|N then cells must hold either Y or N with Y meaning true and N meaning false.

Formats for dates and times

Dates and times are commonly represented in tabular data in formats other than those defined in [[!xmlschema-2]].

If the datatype is a date or time type, the format property indicates the expected format for that date or time. Validators MUST check that the dates or times in the column adhere to the specified format. Converters MUST use the format property to parse the date or time when mapping it into a suitable type in the target language of the conversion.

When the datatype is a date or time type, the format property's value MUST be a date/time format as specified in [[!xslt-21]].

We invite comment on which format to use when parsing dates and times. GitHub >

Formats for durations

We invite comment on whether there are standard formats to use when parsing durations. GitHub >

Processing Tables

This section describes how particular types of applications should use the metadata supplied about a CSV file when they process that CSV file.

Displaying Tables

We intend to include other sections here about:

  • displaying metadata about groups of tables, tables, columns, rows, cells and regions
  • what headings to use for columns when displaying tabular content
  • how to format values in cells

Much of this is likely to be non-normative. We invite comment on whether it's useful to provide this kind of guidance.

Bidirectional Tables

There are two levels of bidirectionality to consider when displaying tables: the directionality of the table (ie whether the columns should be arranged left-to-right or right-to-left) and the directionality of the content of individual cells.

The table-direction property provides information about the desired display of the table. If table-direction=ltr then the first column SHOULD be displayed on the left and the last column on the right. If table-direction=rtl then the first column SHOULD be displayed on the right and the last column on the left.

If table-direction=default then tables SHOULD be displayed with attention to the bidirectionality of the content of the file. Specifically, the values of the cells in the table should be scanned breadth first: from the first cell in the first column through to the last cell in the first column, down to the last cell in the last column. If the first character in the table with a strong type as defined in [[!UNICODE-BIDI]] indicates a RTL directionality, the table should be displayed with the first column on the right and the last column on the left. Otherwise, the table should be displayed with the first column on the left and the last column on the right. Characters such as whitespace, quotes, commas and numbers do not have a strong type, and therefore are skipped when identifying the character that determines the directionality of the table.

Implementations SHOULD enable user preferences to override the indicated metadata about the directionality of the table.

Once the directionality of the table has been determined, each cell within the table should be considered as a separate paragraph, as defined by the UBA in [[!UNICODE-BIDI]]. The default directionality for the cell is determined by looking at the text-direction property, which is an inherited property.

Thus, as defined by the UBA, if a cell contains no characters with a strong type (if it's a number or date for example) then the way the cell is displayed should be determined by the text-direction property of the cell. However, when the cell contains characters with a strong type (such as letters) then they MUST be displayed according to the Unicode Bidirectional Algorithm as described in [[!UNICODE-BIDI]].

Validating Tables

We intend to detail how to validate groups of tabular data files against metadata. This would be normative: compliant validators would have to report the errors and warnings that we define. We invite comment on whether this is a useful thing to specify.

Converting Tables

Conversions of tabular data to other formats operate over a annotated table constructed as defined in . The mechanics of these conversions to other formats are defined in other specifications.

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

Conversion specifications MUST use the name 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 properties defined in this specification to adjust the mapping of an annotated table into another format.

Conversion specifications MAY define additional properties, 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 element-or-attribute property on columns that determines whether a particular column is represented through an element or an attribute in the data.

Conversion specifications SHOULD specify format-specific properties specifying external processing steps to provide more control to people defining conversions. If these are specified, the conversion specification MUST specify at what point in the processing this external processing takes place, and what it takes place on. Examples might be:

Acknowledgements

This document is largely a copy of content from the Data Package specification and the JSON Table Schema, which are maintained as part of Data Protocols. Particular contributors to that work are Rufus Pollock, Paul Fitzpatrick, Andrew Berkeley, Francis Irving, Benoit Chesneau, Leigh Dodds, Martin Keegan, and Gunnlaugur Thor Briem.

IANA Considerations

Registration of application/csvm+json

We intend to include a registration for a new datatype, namely application/csvm+json. We invite comment about how to indicate that this is consistent with application/ld+json, or whether we should just use application/json or application/ld+json and not create a specific media type for the metadata files defined in this document.

JSON-LD Context

The following JSON document is the JSON-LD context document that can be used to interpret metadata documents as RDF.

See csvm-context.json.