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.
- If you want to merge data for existing rows, check the box: Update data for existing rows during import. If this box is not checked, duplicate row IDs will cause the import to fail.
- 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 merge data for existing rows, check the box: Update data for existing rows during import. If this box is not checked, duplicate row IDs will cause the import to fail.
- To import 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 Merge
For bulk imports, you can check the box labelled
Update data for existing rows during import to merge data into rows that already exist. If this box is not checked, duplicate row IDs will cause the import to fail.
If this box is unchecked (the default) the following occurs:
- The import process will insert new rows based on the data/file provided.
- The operation will fail if there are existing row identifiers that match those being imported.
If this box is checked:
- 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 merge are recorded in the detailed audit log.
Related Topics
