LabKey Server provides a variety of different data structures for different uses: Assay Designs for capturing instrument data, Datasets for integrating heterogeneous clinical data, Lists for general tabular data, etc. Some of these data structures place strong constraints on the nature of the data to be imported, for example Datasets make uniqueness constraints on the data; other data structures, such as Lists, make few assumptions about incoming data.

• Avoid Mixed Data Types in a Column
• Avoid Special Characters in Column Headers
• Handling Backslash Characters
• Data Aliasing
• Clinical Dataset Details
• Demographic Dataset Details
• Merge List and Dataset Data
• Import to Unrecognized Fields
• Date Parsing Considerations
• Data Import Previews

General Advice: Avoid Mixed Data Types in a Column

LabKey tables (Lists, Datasets, etc.) are implemented as database tables. So your data should be prepared for insertion into a database. Most importantly, each column should conform to a database data type, such as String, Integer, Decimal Number, etc. Mixed data in a column will be rejected when you try to upload it.

Wrong

The following table mixes Boolean and String data in a single column.

Right

Split out the mixed data into separate columns

General Advice: Avoid Special Characters in Column Headers

Column names should avoid special characters such as !, @, #, $, etc. Column names should contain only letters, numbers, spaces, and underscores; and should begin only with a letter or underscore. We also recommend underscores instead of spaces.

Wrong

The following table has special characters in the column names.

Right

The following table removes the special characters and replaces spaces with underscores.

Handling Backslash Characters

If you have a TSV or CSV file that contains backslashes in any text field, the upload will likely ignore the backslash and either substitute it for another value or ignore it completely. This occurs because the server treats backslashes as escape characters, for example, \n will insert a new line, \t will insert a tab, etc.

To import text fields that contain backslashes, wrap the values in quotes, either by manual edit or by changing the save settings in your editor. For example:

Test\123

"Test\123"

Data Aliasing

Use data aliasing to work with non-conforming data, meaning the provided data has different columns names or different value ids for the same underlying thing. Examples include:

• A lab provides assay data which uses different participant ids than those used in your study. Using different participant ids is often desirable and intentional, as it provides a layer of PHI protection for the lab and the study.
• Learn more here: Alias Participant IDs
• Excel files have different column names for the same data, for example some files have the column "Immune Rating" and other have the column "Immune Score". You can define an arbitrary number of these import aliases to map to the same column in LabKey.
• Learn more here: Import Aliases
• The source files have a variety of names for the same visit id, for example, "M1", "Milestone #1", and "Visit 1".
• Learn more here: Import Visit Names / Aliases

Clinical Dataset Details

Datasets are intended to capture measurements events on some subject, like a blood pressure measurement or a viral count at some point it time. So datasets are required to have two columns:

• a subject id
• a time point (either in the form of a date or a number)

Wrong

The following dataset has duplicate subject id / timepoint combinations.

Right

The following table removes the duplicate row.

Demographic Dataset Details

Demographic datasets have all of the constraints of clinical datasets, plus one more: a given subject identifier cannot appear twice in a demographic dataset.

Wrong

The following demographic dataset has a duplicate subject id.

Right

The following table removes the duplicate row.

Merge List and Dataset Data

To merge data for a List or Dataset, choose any bulk import method and check the box to Update data for existing rows during import. Otherwise, if the import includes data for existing rows, it will fail. This option is not supported for Lists with auto-incrementing integer keys.

Learn more about importing lists and datasets in these topics:

• Populate a List
• Import Data to a Dataset

Import to Unrecognized Fields

When importing data, if there are unrecognized fields in your spreadsheet, meaning fields that are not included in the data structure definition, they will be ignored. In some situations, you will see a warning banner explaining that this is happening:

Date Parsing Considerations

Whether to parse user-entered or imported date values as Month-Day-Year (as typical in the U.S.) or Day-Month-Year (as typical outside the U.S.) is set at the site level. For example, 11/7/2020 is either July 11 or November 7; value like 11/15/2020 is only valid Month-Day-Year (US parsing).

If you attempt to import date values with the "wrong" format, they will be interpreted as strings, so you will see errors similar to ""Could not convert value '11/15/20' (String) for Timestamp field 'FieldName'."

If you need finer grained control of the parsing pattern, such as to include specific delimiters or other formatting, you can specify additional parsing patterns at the site, project, or folder level.

Data Import Previews

In some contexts, such as creating a list definition and populating it at the same time and importing samples from file into Sample Manager or LabKey Biologics, you will see a few lines "previewing" the data you are importing.

These data previews shown in the user interface do not apply field formatting from either the source spreadsheet or the destination data structure.

In particular, when you are importing Date and DateTime fields, they are always previewed in ISO format (yyyy-MM-dd hh:mm) regardless of source or destination formatting. The Excel format setting is used to infer that this is a date column, but is not carried into the previewer or the imported data.

For example, if you are looking at a spreadsheet in Excel, you may see the value with specific date formatting applied, but this is not how Excel actually stores the date value in the field. In the image below, the same 'DrawDate' value is shown with different Excel formatting applied. Date values are a numeric offset from a built-in start date. To see the underlying value stored, you can view the cell in Excel with 'General' formatting applied (as shown in the fourth line of the image below), though note that 'General' formatted cells will not be interpreted as date fields by LabKey. LabKey uses any 'Date' formatting to determine that the field is of type 'DateTime', but then all date values are shown in ISO format in the data previewer, i.e. here "2022-03-10 00:00".

After import, display format settings on LabKey Server will be used to display the value as you intend. Learn about setting display values for dates in this topic: Date & Number Display Formats

Related Topics

• LabKey Data Structures
• Populate a List
• Import Data to a Dataset
• Prepare Specimen Data for Import