Importing sample data from spreadsheets

contents | prev | next

In many labs sample data is stored in spreadsheets. You can use eCAT to import your sample spreadsheet data. Each line in your spreadsheet will be created as a sample or aliquot. Before you can import your spreadsheet into eCAT you first need to save your spreadsheet in a format called "Comma Separated Values" or CSV for short.

To do this in Excel, you just need to open the spreadsheet you want to import, then save it again. In the Save dialog, set the Save as type dropdown to CSV(Comma delimited)(*.csv). You then have a CSV file ready for import. From now on this document will talk about a CSV file instead of a spreadsheet. If you are using Open Office or some other spreadsheet package, there will be a very similar option.

There are two ways to import data into the inventory system.

Importing samples without container or location information

The simplest way to get sample data imported from a CSV file into eCAT is to use eCAT's Workbench. You just browse to there, and click "Import". You can then browse your local machine for the CSV file to import. Clicking next shows you the following screen:

Importing a CSV

Importing a CSV

Here you need to choose a sample template to import to, and specify which column in the CSV file is going to provide the name of the record. The sample template is very important here. The field names in the sample template must match the field names in the CSV file. Here is an example of a valid CSV file.

A valid CSV file

A valid CSV file

This will import into a template with the same field names - an example of this is shown below:

Field names

Field names

Note that the field names in the template have the exact same values as the top row of the CSV file - this is how the importer determines which value to place in which field. You must have an exact match of field name to column name in the CSV file.

In addition to making the names match, the data in the cells of the CSV must be of the correct type to import into that field type. So if the field in the class is a number, the data in the CSV file must be numerical, and so on. Details of each of the supported field types and the formats required is given in the Field types section below.

When the sample data has been imported you can use the normal controls in the Workbench container to move them into freezer boxes. Click here for more information about that.

Importing sample data with location information

It is also possible to import samples with location information specified in the CSV file - this means that the CSV file specifies the name of the container and the location in which the sample is stored for every sample. This import involves creating new containers for the imported samples - it is not possible to import directly into existing containers.

To do this kind of import, choose "Import" in the inventory section in any container except the Unallocated Samples container. The bottom of the second page of the import wizard has an extra control which looks as follows:

Choosing a container template

Choosing a container template

You need to use this to specify what kind of container you are going to create in this import.

Each row in the CSV file must contain two extra columns - container name and container location. The name of the container can be whatever you wish the container to be called when it is created. The location in the container is the row/column index of the container using its notation. So if you are selecting a 10x10 sample tray, valid locations would be 1/A, 2/A, 5/B etc as shown below:

Container information

Container information

The section of the CSV file shown above has the container name and container location columns to the right of the final data column (date). This CSV will create three samples in a container called Test Box, then two samples in a container called Second Box, then a final sample in the first Test Box. The locations are specified using X, Y co-ordinates split by a /.

On completion of the import you are returned to the destination container.

Importing aliquots

You can also import samples as aliquots. Aliquots have only the freeze thaw cycle count and volume as unique properties. All other elements of the sample are shared between all aliquots. Aliquots are specified by having an aliquot id column in the CSV file. This is a numerical indicator - rows with the same aliquot id will be treated as a set of aliquots. Note that you must specify data for each column in the CSV file for an aliquot - but because the data is taken from the first aliquot row then the data stored will be the data present in the first aliquot in the CSV. The image below shows a CSV file with the aliquot id column at the end.

Location information

Location information

Here you can see that the first 8 samples in Test Box will all be the same aliquot. In addition the 5 samples in Test Box 3 that have an aliquot id of 18 will be the same aliquot (but different to the 8 in Test Box). All other rows represent samples.

Field Types

eCAT support CSV import into a variety of different field types. The types are listed below with details on the text format required to successfully import to them.


Use the value "true" for checked, and "false" for unchecked.


Choice fields have a list of string values in the class definition. To select multiple values specify the string values delimited by colons. So if the class has values One,Two,Three a valid entry would be One:Three


Date fields support standard date representations - dd/mm/yyyy for example. So 14/10/2010 would be a valid date.


Number fields must be numerical data. No text data is allowed, so 1.2 is fine, as is 1.20, but $1.20 is not.


Radio fields have a list of string values in the template definition. To select a radio item just enter the string value in the CSV.


References are the most complicated type of field to import. To set a reference in the CSV file, the record you are referring to must already exist in eCAT. You need to browse to the record you are referring to and look at the URL bar. It will have a URL something like "/record.html?oid=1295365308624". The long number on the end will change. You will need this number. You also need to know the name of the record, and what you are linking from. The value you need to enter into the CSV is recordname:longnumber:destinationtype:sourcetype. So a valid value would be Project:1295365308624:record:sample. This means that you are linking to a record called Project with an OID of 1295365308624. The record you are linking to is a record, and the record you are linking from is a sample.


Time fields have two possible value types. You need to look at the class definition to work out which type to use. Valid values are either HH:mm in 24 hour format (so 16:23) or hh:mm AM/PM so (4:23 PM).


Text fields accept any text. You can also insert HTML here.