This topic explains how to best prepare your data for import so you can meet any requirements set up by the target data structure.
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.
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.
The following table mixes Boolean and String data in a single column.
Split out the mixed data into separate columns
|ParticipantId||Preexisting Condition||Condition Name|
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.
The following table has special characters in the column names.
|Participant #||Preexisting Condition?|
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:
Note that this does not apply to importing Excel files. Excel imports are handled differently, and text data within the cell is processed such that text is in quotes.
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.
- 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.
- The source files have a variety of names for the same visit id, for example, "M1", "Milestone #1", and "Visit 1".
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)
Also, a subject cannot have two different blood pressure readings at a given point in time, so datasets reflect this fact by having uniqueness constraints: each record in a dataset must have a unique combination of subject id plus a time point.
The following dataset has duplicate subject id / timepoint combinations.
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.
The following demographic dataset has a duplicate subject id.
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:
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