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:

-f	The full path to the location of the excel file. (Required)
-g	The full path to the location of the log file. (Optional)

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

python /path/to/file/SampleSheetImporter.py -f /Users/mywd/ClaritySampleSheetprojects.xlsx -g /Users/logs/samplesubmission.log

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:

• • Project researcher is System Administrator

  • Project open date is today

  • No project level UDFs are created

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.

• • If container type is not specified, TUBE will be assumed.

  • For TUBE, well location will always be 1:1

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.

• You are using Python version 2.6 or 2.7.

• The Python installation contains the non-standard xlrd library.

• The _auth_tokens.py file has been updated to include the information for your Clarity installation.

• The example code is provided for illustrative purposes only. It does not contain sufficient exception handling for use 'as is' in a production environment.

Attachments

_auth_tokens.py:

79B

_auth_tokens (1).py

ClaritySampleSheetprojects.xlsx:

34KB

ClaritySampleSheetprojects (1).xlsx

SampleSheetImporter.py:

10KB

SampleSheetImporter.py