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.

• Choose Column Data Types
• Avoid Mixed Data Types in a Column
• Avoid Special Characters in Column Headers
• Handling Backslash Characters
• Data Aliasing
• Clinical Dataset Details
• Demographic Dataset Details
• Import Options: Add, Update, and Merge Data
• Import to Unrecognized Fields
• Date Parsing Considerations
• Data Import Previews
• OOXML Documents Not Supported

Choose Column Data Types

When deciding how to import your data, consider the data type of columns to match your current and future needs. Considerations include:

• Available Types: Review the list at the top of this topic.
• Type Conversions are possible after data is entered, but only within certain compatibilities. See the table of available type changes here.
• Number type notes:
• Integer: A 4-byte signed integer that can hold values ranging -2,147,483,648 to +2,147,483,647.
• Decimal (Floating Point): An 8-byte double precision floating point number that can hold very large and very small values. Values can range approximately 1E-307 to 1E+308 with a precision of at least 15 digits. As with most standard floating point representations, some values cannot be converted exactly and are stored as approximations. It is often helpful to set on Decimal fields a display format that specifies a fixed or maximum number of decimal places to avoid displaying approximate values.

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 Text, Integer, Decimal, 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.

Import Options: Add, Update and Merge Data

When bulk importing data (via > Import bulk data) the default Import Option is Add rows, for adding new rows only. If you include data for existing rows, the import will fail.

To update data for existing rows, select the option to Update rows. Note that update is not supported for Lists with auto-incrementing integer keys or Datasets with system-managed third keys.

If you want to merge existing data updates with new rows, select Update rows, then check the box to Allow new rows during update. Note that merge is not supported for Lists with auto-incrementing integer keys or Datasets with system-managed third keys.

Learn more about updating and merging data in these topics:

• Populate a List
• Import Data to a Dataset
• Update and Merge Data in Sample Manager
• Update and Merge Data in Biologics LIMS

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, such as when importing sample data, 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

OOXML Documents Not Supported

Excel files saved in the "Strict Open XML Spreadsheet" format will generate an .xlsx file, however this format is not supported by POI, the library LabKey uses for parsing .xlsx files. When you attempt to import this format into LabKey, you will see the error:

There was a problem loading the data file. Unable to open file as an Excel document. "Strict Open XML Spreadsheet" versions of .xlsx files are not supported.

As an alternative, open such files in Excel and save as the ordinary "Excel Workbook" format.

Learn more about this lack of support in this Apache issue.

Related Topics

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