Batch Import Collections with spreadsheets

When joining LabID or when collections are not regularly updated, many items need to be registered together. A feature to import items in batch based on spreadsheets is available. In short, to import multiple items, one should retrieve the template from collection category to be imported, fill this template out with item information and upload this template back.

How to import collections

1. Go to the list view of the collection type where new items need to be added, e.g. plasmids

2. Download the import template

   • Click the download template icon button to open the dropdown

   • Select the Download template for batch create option

     

   • Optionally add annotation columns to the template

3. Fill in the spreadsheet template locally (see below for details)

4. Upload the filled template
   • Click the upload template icon button to open the dropdown
   • Select the Batch create with template option
   • On the import page, upload the template to automatically trigger import

When errors occur, the import page will display the errors with associated lines. Please correct the template and upload the template again. In case of error, no item is created.

When importing big collectuion for the first time, we recommend to first attempt at creating fewer entries (2 or 3) and check how the created items look like (on the list and detail views). When the items appear as needed, then proceed with the entire collection upload.

Template table format details

Headers

Headers should remain at all time. This is what is used to parse the template and make sense of the information.

Controlled vocabulary

The template contains controlled vocabulary. When present - on certain relevant fields - cells offer dropdown selection, and the controlled vocabulary has to be used. New values cannot just be entered for these fields. If needed, contact the admins before importing the data, so that missing terms are added. When added, download the template again.

Mandatory columns

Mandatory columns appear in bold. Not many fields are enforced, but we do advise providing exhaustive information.

LabID locations (e.g. freezers, fridges ...)

As for controlled vocabulary, new locations cannot be created in the template, they must be registered first.

Field specific information:

• Multiple value fields: Multiple values can be specified for certain attributes. Separate the values with ~~~ (three tilde symbols) e.g. value1~~~value2
• Code: On certain items where the code is needed, a new unique one can automatically be generated using the value AUTO

Add annotations

Extra annotation columns can be explicitly added to the template. When the popup opens up after clicking Download template for batch create, selects the annotation that need to be added to the template.

Select annotations to be added to the template upon download. Here, a custom list of 3 annotation is selected, the template will contain 3 additional columns: Age, Antibody and Antibody Source

File attachments

File attachments can be provided for each item using a column with header Attachment and sub-header attachments (second row).

All the files to be attached should be placed in a folder accessible with full read permission so the LabID user can access and get a copy.

The absolute path to the files should be provided in the Attachment column. The path syntax should be compatible with the operating system of the server hosting LabID.

Multiple files can be specified for the same item by separating the file names with ~~~ (three tilde symbols) e.g. /path/to/attachments/file1.txt~~~/path/to/attachments/file2.txt.

Item location

The location of each item can be specified using the location columns with header Location Name, Container Type, Container Name, Container Position, Location Details with the following rules:

• All these 5 columns must be present in the template to specify the location of the items, or absent if no location is to be specified
• Location Name: name of the location where the item is stored (e.g., Freezer 1) ; this field is mandatory and the location must exist in LabID before the import
• Container Type: type of the container where the item is stored (e.g., Box, Shelf) ; this field is optional but if provided, the container type must exist in LabID before the import
• Container Name: name of the container where the item is stored (e.g., Plate 12) ; this field is optional but if provided, the container type must be provided
• Container Position: position of the item in the container (e.g., A1, B2) ; this field is optional but if provided, the container type and name must be provided
• Location Details: additional details about the location of the item (e.g., specific shelf or drawer) ; this field is optional

Examples of specifying item location in the Excel import template

Multiple locations

If needed, more than one location can be specified for the same item using the usual ~~~ separator. By default, the Excel template performs input data validation on the Location Name & Container Type that will prevent you from adding multiple values with the needed ~~~. You will therefore need to deactivate this data validation to be able to enter multiple locations with the proper formating.

In addition to the rules listed above, it is mandatory to use the same number of ~~~ separator in all the five columns even when no value is needed in a given column. For example, to specify that an item is stored in two different locations, the Location Name column can contain Freezer 1~~~Fridge 2, and the other location columns must contain the corresponding values separated by ~~~ in the matching order. Correct and wrong examples are provided below.

Correct and wrong examples of specifying multiple item locations in the import template

Limitations

Extra arbitrary columns

Extra arbitrary columns are not supported. Any column unknown to LabID added to the spreadsheet will generate an error upon its upload. Offending columns need to be removed before you can proceed.

If you need to capture specific information, please get in touch with us so that we can either adapt our models or add new annotations types for you.

It is not possible to provide all features available in online forms with Excel spreadsheet, i.e

• A unique note can be provided per item; using a column with header Note and sub-header notes (second row)

Admin specific options

When the user has admin rights, extra options are accepted in the template:

• The item owner can be specified using the Owner column, with sub-header owner (second row)
• It is currently not possible to specify the Attachment column when batch loading as an admin