eLabJournal offers the option migrate samples in bulk into the system. To facilitate the migration of data of groups, the System Administrator can provide a sample import template to the group or end-user for which samples should be imported. The existing data should be transferred to the import template according to the specification below. Note that sample series and separate samples need to be imported from  a separate file. After quality assurance, the System Admin can execute the data migration.

 

The sample migration option is intended for use during initial data migration when new labs within the organisation are set-up. In case large batches of sample should be periodically added into the system, we recommend the use of the batch migration option available for end-users in the system. Note that this the batch migration should first be activated as an add-on in Marketplace


Important: The data in the sample import template should be formatted according to the below specification. 

Standard fields


Column

User (email)

Data entry:

Required field

Expected data:

Email address of existing active account (e.g. user@institute.country)

Data condition:

User with email address should exist and be linked to a user in eLabJournal. The user should be a member of lab in which samples are being imported

Data context:

Data in this column will be imported to the system as the sample owner.

Exception handling

If the person that created the sample does no longer work in the lab, choose to put the email address of the person now responsible for the sample (e.g the group-leader or other colleague). In case the original creator should be tracked, include that data in the "notes" field, "a custom sample type field' or the "created by" column.


Column

Created by (email)

Data entry:

Required field

Expected data:

Email address of existing account (e.g. user@institute.country), is not required to be active

Data condition:

User with email address should exist and be linked to a user in eLabJournal

Data context:

Data in this column will be imported to the system as the sample creator.

Exception handling

If the sample creator is a person does not work in the lab anymore and has never had an active account in eLabJournal, this account should be first be created and subsequently deactivated (to avoid it to be counted in the active license plan)


Column

Storage Location

Data entry:

Optional field: The storage location will not be imported from this column

Expected data:

When including the storage location when creating an sample import template this column will automatically contain all included storage locations. This location is linked the StorageLayerID column (see below)

Data condition:

The Storage location will not be imported from this column

Data context:

The Storage location will not be imported from this column.

Exception handling

The Storage location will not be imported from this column


Column

Position

Data entry:

Optional field: The position will not be imported from this column

Expected data:

When including the storage location when creating an sample import template this column will automatically contain all included positions in compartments. This location is linked the eLABPosition column (see below)

Data condition:

The position will not be imported from this column

Data context:

The position will not be imported from this column

Exception handling

The position will not be imported from this column


Column

Storage Layer ID

Data entry:

Optional field

Expected data:

The ID number of the location the sample should be imported in (which is linked to the location as displayed in the column Storage Location). Samples without StorageLayerID will be imported with "location unspecified" and can later be moved by the end-user

Data condition:

Location with ID number should exist and the combination StorageLayerID and eLABPosition must be unique for boxes

Data context:

The StorageLayerID in eLabJournal is linked to the compartment in which a sample will be imported. Each compartment has a unique StorageLayerID

Exception handling

When creating a template of an existing storage location and when after that a box in that storage location is deleted, the import will fail because the samples cannot be migrated into a location that does not exist any more. Recreate that compartment and change the StorageLayerID accordingly


Column

eLab Position

Data entry:

Optional field

Expected data:

The position in the compartment translated to numeric values (identical to when numeric boxes are created). Samples without eLABPosition are imported to an “unspecified location”

Data condition:

eLABPosition is always numeric  1,2,3,4 etc. independent of user configuration A1, A1 or 1A, 1B 1C. eLABPosition should not be higher that the box size (e.g. position 101 does not exist in a 10x10 box) and duplicate positions in boxes are not allowed.

Data context:

The position in this column will be used to visualize the sample in 2-dimensional compartments (e.g. boxes)

Exception handling

When creating a template of an existing storage location and when after that a box in that storage location is deleted, the import will fail because the samples cannot be migrated into a location that does not exist any more. Recreate that compartment and change the StorageLayerID accordingly


Column

Barcode

Data entry:

Optional field

Expected data:

Barcode of sample, will be linked to a sample as an external barcode

Data condition:

must be unique within group

Data context:

The barcode will be displayed in the Sample Information sheet, during the import every sample still receives an internal sampleID which may also be used as the barcode

Exception handling



Column

Expiration Date

Data entry:

Optional field

Expected data:

Date in the format yyyy-mm-dd

Data condition:

In Excel, the date format should be converted to text using the Excel Function =TEXT(A1,"yyyy-mm-dd"). A1 is the selected cell in which the date is formatted. Make sure to covert the function output to text after conversion.

Data context:

Date to indicate when the sample expiration date is. After passing of the expiration date, the sample will be marked

Exception handling



Column

Parent Sample

Data entry:

Optional field

Expected data:

Sample name or SampleID:111

Data condition:

May only contain 1 sample (use sample link field to link multiple samples). Sample name may only be used when the sample name is unique. In case multiple samples with the same name exist the SampleID must be appended to SampleID: to result in SampleID:111 for instance. The SampleID can be retrieved with the sample export.

Data context:

To mark from which the sample the imported sample is derived from

Exception handling



Column

Sample Name

Data entry:

Required field

Expected data:

Any text

Data condition:

Maximum of 255 characters

Data context:

The data entered in this column will be imported in the Samle Name field. This is usually the same as the way the sample is labelled. This is the primary sample identifier for users (a internal sampleID is automatically generated during the import which is also used to generate the sample barcode)

Exception handling



Column

Description

Data entry:

Optional

Expected data:

Any text up to 4000 characters

Data condition:

None

Data context:

General description of a sample. Sometimes labs have data in Excel with similar kind of content (e.g.  remarks or comment  etc.). Preferably put this data in the standard "description" or "notes"  field rather than a custom field

Exception handling



Column

Notes

Data entry:

Optional

Expected data:

Any text up to 4000 characters

Data condition:

None

Data context:

Notes about a sample. Sometimes labs have data in Excel with similar kind of content (e.g.  remarks or comment  etc.). Preferably put this data in the standard "description" or "notes"  field rather than a custom field

Exception handling



Column

Storage Date

Data entry:

Required

Expected data:

Date in the format yyyy-mm-dd

Data condition:

In Excel, the date format should be converted to text using the Excel Function =TEXT(A1,"yyyy-mm-dd"). A1 is the selected cell in which the date is formatted. Make sure to covert the function output to text after conversion.

Data context:

Each sample must have a storage date. If no storage date for a sample is known, choose for a standard date in the past like 2000-01-01 

Exception handling

Dates before 1970-01-01 are not supported and also no dates in the future


Column

Quantity

Data entry:

Optional

Expected data:

numeric value, use the . a the separator for decimal numbers 

Data condition:

Lab requires the ordering module

Data context:

Sets the sample quantity field, which is available when the ordering module is active

Exception handling



Column

Unit

Data entry:

Optional; required if the quantity column is used

Expected data:

Unit of quantity

Data condition:

Accepted Values as specified between quotes:

- "Liter"

- "Milliliter"

- "Microliter"

- "Kilogram"

- "Gram"

- "Milligram"

- "Microgram"

- "Unit"

Data context:

Sets the unit of the sample quantity, value should be identical accepted Unit values

Exception handling

Unit as arbitrary (e.g. to indicate pieces)


Column

Series

Data entry:

Optional

Expected data:

arbitrary numeric value to group samples to be imported as a sample series (e.g. all sample with for instance the value 1 in this column will be imported into the same series. All samples with the value 2 will be imported as a different series.

Data condition:

should be a numeric value

Data context:


Exception handling



Column

Series Name

Data entry:

Optional; required if the Series column is used

Expected data:

Sample series name

Data condition:

None

Data context:

Sets the name of the samples series as used in the system

Exception handling



Custom Sample Type Fields


eLabJournal offers the option to customize the sample type by adding custom sample type field. The custom fields can be added can be of different types and therefore data in columns should be entered according the following specifications


Note that custom fields are always marked: ##fieldname


Column

short text field

Data entry:

Optional

Expected data:

Text  

Data condition:

Maximum of 255 characters (longer texts will be trimmed)

Data context:

Custom field in sample type as defined to add text

Exception handling



Column

long text field

Data entry:

Optional

Expected data:

Text  

Data condition:

Maximum of 2000 characters (longer texts will be trimmed)

Data context:

Custom field in sample type as defined to add long text

Exception handling



Column

numeric field

Data entry:

Optional

Expected data:

Numeric value  

Data condition:

Use . to separate decimal numbers

Data context:

Custom field in sample type as defined to add numeric values

Exception handling



Column

date field

Data entry:

Optional

Expected data:

date in format yyyy-mm-dd

Data condition:

In Excel, the date format should be converted to text using the Excel Function =TEXT(A1,"yyyy-mm-dd"). A1 is the selected cell in which the date is formatted. Make sure to covert the function output to text after conversion.

Data context:

Custom field in sample type as defined to add a date

Exception handling



Column

date and time field

Data entry:

Optional

Expected data:

date and time in format yyyy-mm-dd hh:mm

Data condition:

In Excel the format should set to English (Great-Britain) for instance 2017-01-01 12:00 in UTC time

Data context:

Custom field in sample type as defined to add a date and time 

Exception handling

Based on the timezone, the date should be converted to UTC for correct conversion in the interface if this is important


Column

dropdown menu field

Data entry:

Optional

Expected data:

one of the options as available in the dropdown menu

Data condition:

Should be identical to one of the values in the dropdown menu. Values are case sensitive, should not be preceded or followed by a space and can only contain 1 value

Data context:

Custom field in sample type as defined to select one of the set values

Exception handling

If multiple values should be selected, change the field type to a checkbox field


Column

radio button field

Data entry:

Optional

Expected data:

One of the options as available in the radio button field

Data condition:

Should be identical to one of the values of the radio button field. Values are case sensitive, should not be preceded or followed by a space and can only contain 1 value

Data context:

Custom field in sample type as defined to select one of the set values

Exception handling

If multiple values should be selected, change the field type to a checkbox field


Column

checkbox field

Data entry:

Optional

Expected data:

one of the options as available in the checkbox field

Data condition:

Should be identical to one of the values of the checkbox field. Values are case sensitive, should not be preceded or followed by a space. If multiple values should be selected use the , to separate values (e.g. value1,value2)

Data context:

Custom field in sample type as defined to select one or more of the set values

Exception handling



Column

link to file field

Data entry:

Optional

Expected data:

File name + extension of the file (e.g. filename.pdf or document.docx)

Data condition:

The to be linked file should be uploaded in the file storage prior to the import. In case multiple files should be linked use | a the separator (e.g. file1.pdf|file2.pfd)

Data context:

Custom field in sample type as defined to link files.  

Exception handling



Column

link to sample field

Data entry:

Optional

Expected data:

Sample name

Data condition:

Must be identical to the sample name. Sample Name must be unique. In case multiple samples with the same sample name are stored, it is not possible to indicate to which should be linked. In case multiple samples should be linked use , a the separator (e.g. sample name1,sample name 2) 

In case multiple samples with the same name exist the SampleID must be appended to SampleID: to result in SampleID:111 for instance. The SampleID can be retrieved with the sample export.

Data context:

Custom field in sample type as defined to link a sample

Exception handling




To import samples, start with the creation of an import template for a sample type. Navigate to the Import tab in the System Admin Panel and open Import Samples from Template. Please read the instructions above for on the formatting of data prior to executing the data migration. 


Prior to importing the file, make sure that the imported sample type template is created according to the following specifications:


  • The name of the sample import template must be contained in the imported file. It is allowed add additional info using only letters in the file name (e.g. the file name should be SamplesImportTemplate_24_228.xlsx or SamplesImportTemplate_24_228.csv)
  • The file contains a maximum of 1000 rows (e.g import up to 1000 samples at a time)
  • The to be imported data is formatted according to the import specifications (see here). The common reasons problems in data formatting are wrong or mixed date formatting (should be yyy-mm-dd), use of comma (,) to separate decimal numbers and importing multiple samples on the same position.
  • In case of import from CSV, the file must be converted to a .CSV file with a ; as the separator
  • In case special characters are used (e.g. Greek characters or symbols), make sure that the .CSV file is created with the UTF-8 character set. Note that there are known issues in Excel when converting the file to CSV with special characters. LibreOffice provides better options to concert Excel to CSV files.



To execute the sample import, Upload the file and click Import Samples. Depending on the number of samples to be imported, the application is processing the file and importing the sample. Note that if the file is not formatted according to specifications, the import is not executed and will display an error. 

Imported samples are indexed every night at midnight. When the samples are indexed they can be found using the Sample List Search function.