TSV functionality and syntax

Column Names

The column headers in the TSV - i.e. the names of the attributes in your data - cannot start with "sys_" and must only contain letters, numbers, spaces, dashes or underscores.

Record Id (Primary Key)

The record id is the primary key of the record. Its value must be specified when retrieving or updating individual records. Any rows in a TSV that have the same value for the record id will be combined into the same record.

During TSV upload, the optional primaryKey parameter specifies which column in the TSV represents the record id.

If you do not specify a column, WDS will default to using the first column of the TSV.

Relations

To create a relation from one record to another, represent that relation as a string URI within your TSV. The syntax for a relation is:

terra-wds:/{target-type}/{target-id}

For instance, to create a relation to a record of type "participant" with an id of "user-123":

terra-wds:/participant/user-123

Numbers

Unquoted numbers in your TSV will be created as numbers in WDS. 2, 3.14, and -999 will all be created as numbers.

"123" will be created as a string, not a number, because it is in quotes.

WDS does NOT allow leading zeros in numbers. A value of 00045 will be created as a string.

Booleans

All unquoted casings of "true" and "false" will be created as booleans in WDS. True, true, tRuE, and TRUE will all be booleans.

"true" (e.g. a quoted value in the TSV) will also be created as a boolean

Only "true" and "false" variants will become booleans. Other values which indicate booleans in some programming languages such as 0, 1, Yes, or No will be created as strings.

Dates

To create a Date in WDS, specify a date value using ISO-8601 format of YYYY-MM-DD, for instance 2011-12-03.

Datetimes

To create a Datetime in WDS, specify a date value using ISO-8601 format of YYYY-MM-DDTHH:MM:SS, for instance 2011-12-03T10:15:30.

Datetimes support nanoseconds as well, for example 2011-12-03T10:15:30.123456789.

Strings

Any values in your TSV that do not resolve to a relation, number, boolean, date, or datetime will be created as strings.

To explicitly create a string in WDS with a value that would otherwise resolve to a relation, number, boolean, date, or datetime, wrap your string in double quotes. For instance: "123" or "true".

Special Characters

To include most special characters inside a string value inside your TSV, simply wrap your string in double quotes. For instance, to include the tab character inside a string: "hello world". Without the quotes, the tab character would be treated as a delimiter.

To include the double-quote character itself inside a string value, escape it with a backslash: "I said \"hello\"".

Arrays

To create an array value, represent that array using JSON syntax. That is, include all array values as a comma-delimited list inside square brackets []. Values inside an array can be any of the datatypes above: relation, number, boolean, date, datetime, string.

For instance, to create an array value containing the numbers 2, 4, and 6:

[2,4,6]

To create an array value containing strings:

["foo","bar","\"baz\" is the best"]

Note that without the inner quotes, an array of strings will evaluate to a single string: [foo, bar, "baz" is the best] becomes "[foo, bar, "baz" is the best]"

To create an array value containing booleans:

[true, false, true]

however, WDS is smart enough to ingest different casings of the booleans -- e.g. something like such will work too:

[TRUE, fALSE, True]

Arrays of relations must all relate to the same record type, e.g.

["terra-wds:/type/1", "terra-wds:/type/2"]

But not:

["terra-wds:/sample/1", "terra-wds:/type/2"]

Mixing types in an array will result in an array of strings, e.g.:

["hello", 123, True]

becomes

["hello", "123", "True"]

Smart Quotes

When exporting a TSV from a spreadsheet program, some applications will create smart quotes, aka angled quotes “..” instead of straight quotes "…". WDS does its best to interpret smart quotes equal to straight quotes.

In TSVs, all scalar values and most array values can use smart or straight quotes. The following will be treated as equivalent by WDS:

• “hello“ and "hello" will both create a value of hello in WDS
• [“hello“, “world“] and ["hello","world"] will both create an array of hello and world in WDS

In an uncommon case, smart quotes within your TSV will cause problems. This happens only under the following circumstances:

• your data is an array of strings
• values within the array contain internal smart quotes - that is, the smart quotes are not the first and last characters.

In this situation, WDS will not create an array, and will instead treat the entire TSV cell as a single string.

For instance, a TSV value of ["I said “hello“", "You said “world“"] will create a string scalar in WDS of [\"I said “hello“\", \"You said “world“\"].

For more examples of smart quote handling, see https://github.com/DataBiosphere/terra-workspace-data-service/wiki/TSV-smart-quote-behavior.