Programatically Importing the Sample Submission Excel File

Compatibility: API version 2

The Clarity LIMSweb interface provides an example Sample Import Excel file which can be manually uploaded to submit new samples to a selected project within the LIMS.

This application example shows how the Sample Import Sheet can be uploaded programatically with just one change to its format.

Solution

This example uses the same Sample Sheet with an additional column 'Project/Name'. The script processes the Sample Sheet, creates the samples and adds the samples to their respective projects.

This script leverages a python module xlrd, which is not included in the standard python library. It is used to extract data from .xls and .xlsx excel files.

Parameters

The script accepts the following parameters:

An example of the full syntax to invoke the script is as follows:

About the code

parseFile

This method carries out several operations:

• Opens the excel file and reads the text

• Stores the column headers in a dictionary variable called COLS

• Stores the row data accessibly in an array variable called ROWS

createProject

This method in turn carries out several operations:

• For each project name the script encounters it searches the LIMS to identify if a project with this name has already been created.

• If the project does not exist, the script will create the project. This example script is easily modifiable, however as written:

processRows

This method prepares the data needed to create a sample in LIMS:

• Assembles the UDF values, the project ID and container ID.

• For each non-tube container the script encounters it searches the LIMS to identify if a container with this name already exists.

• If the container does not exist the script will create the container.

The script contains additional supporting methods to generate XML which is POSTED to the API.

Assumptions & notes

• The UDFs in the Sample Sheet header have been configured in LIMS prior to submission.

• The following column headers are required: Sample/Name, Project/Name and any sample-level UDFs that are mandatory within your system.

• The script need not be run on the Clarity server, however it must have a connection to the Clarity LIMS API.

Attachments

_auth_tokens.py:

ClaritySampleSheetprojects.xlsx:

SampleSheetImporter.py: