Import Data to a Dataset

2024-03-29

This topic describes how to import data to an existing dataset in a LabKey study. Authorized users can add data a row at a time, or import data from spreadsheets or other data files. Imported data must match the structure of the dataset into which it is imported.

The following import methods are available:

Insert Single Row

  • Navigate to the dataset of interest.
  • Select (Insert Data) > Insert New Row above the dataset grid.
  • Enter data for the fields.
    • Fields marked with '*' are required.
    • Hover over the '?' for any field to see more information, such as the type expected.
    • If an administrator has limited input to a set of values, you will see a dropdown menu and can type ahead to narrow the list of options.
  • When finished, click Submit.

Import from File

  • Navigate to the dataset of interest.
  • Select (Insert Data) > Import Bulk Data.
  • To confirm that the data structures will match, click Download Template to obtain an empty spreadsheet containing all of the fields defined. Fill in your data, or compare to your existing spreadsheet and adjust until the spreadsheet format will match the dataset.
  • By default you will Add rows. Any data for existing rows will cause the import to fail. See update and merge options below.
  • To import the data from the file, click the Upload file panel to open it.
    • Click Browse or Choose File to select and upload the .xlsx, .xls, .csv, or .txt file.
    • Use the Import Lookups by Alternate Key if you want lookups resolved by something other than the primary key.
  • Click Submit.

Import via Copy/Paste

  • Navigate to the dataset of interest.
  • Select (Insert Data) > Import Bulk Data.
  • To confirm that the data structures will match, click Download Template to obtain an empty spreadsheet containing all of the fields defined. Fill in your data, or compare to your existing spreadsheet and adjust until the spreadsheet format will match the dataset.
  • If you want to update data for existing rows, select Update rows. To merge both updates and new rows, check the box: Allow new rows during update. Learn more below.
  • To provide the data either:
    • Click Upload File to select and upload the .xlsx, .xls, .csv, or .txt file.
    • Copy and paste your tabular data into the Copy/paste text box. Select the Format (either tab-separated or comma-separated).
    • Use the Import Lookups by Alternate Key if you want lookups resolved by something other than the primary key.
  • Click Submit.

Dataset Update and Merge

For bulk imports, you can select Update rows to update existing data rows during import. By default, any data for new rows will cause the update to fail.

Check the box to Allow new rows during update to merge both existing and new rows.

If Update rows and Allow new rows during update are both selected:

  • Data will be updated for existing row identifiers, and new rows will be created for any that do not match.
  • Row identifiers are determined by how the dataset is keyed, specified under Data Row Uniqueness in the dataset design.
    • Note that for a dataset with a system-managed additional key, no row updates will occur, as this key is present specifically to ensure all rows are unique when new data is imported.
  • For rows being updated, each column in the import file (or pasted data) will be checked. If the import contains a value, the existing field in the row will be replaced with the new value.
When datasets are updated, "smart diffing" identifies and imports only the changed rows. In some cases this can result in considerable performance improvement. Only the minimal set of necessary records are updated, and the audit log will record only the rows and fields that have changed. Both values, "before" and "after" the update are recorded in the detailed audit log.


Premium Resource Available

Subscribers to premium editions of LabKey Server can learn about adding a Bulk Edit button with the example code in this topic:

Note that SequenceNum values cannot be bulk edited, as they are used in aligning subject and visit uniqueness for datasets.


Learn more about premium editions

Related Topics