# 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:

<table data-header-hidden><thead><tr><th width="116"></th><th></th></tr></thead><tbody><tr><td>-f</td><td>The full path to the location of the excel file. (Required)</td></tr><tr><td>-g</td><td>The full path to the location of the log file. (Optional)</td></tr></tbody></table>

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

{% code overflow="wrap" %}

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

{% endcode %}

### About the code <a href="#code" id="code"></a>

*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 <a href="#assumptions" id="assumptions"></a>

* 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:

{% file src="<https://2084401275-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FfjuebS41N49G1Eh55hP7%2Fuploads%2Fgit-blob-dae4353245ec31654d8f505ecea1946ca6114175%2F_auth_tokens%20(1).py?alt=media&token=b48bc0bf-e4c9-4870-b0a8-4f421912e752>" %}

ClaritySampleSheetprojects.xlsx:

{% file src="<https://2084401275-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FfjuebS41N49G1Eh55hP7%2Fuploads%2Fgit-blob-26dd0a10e2a5cebdd17bd2a93d017e8dbf1dfd17%2FClaritySampleSheetprojects%20(1).xlsx?alt=media&token=f9503f11-55e0-4837-bce4-ac70d1fd6135>" %}

SampleSheetImporter.py:

{% file src="<https://2084401275-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FfjuebS41N49G1Eh55hP7%2Fuploads%2Fgit-blob-0401f4a07cd0b6a32d1788d7d80749c628c29d1e%2FSampleSheetImporter.py?alt=media&token=7a276bc7-c0d9-4b49-a328-70b2644c84c1>" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://help.connected.illumina.com/clarity-lims/api-and-database/api-docs/application-examples/page-13/programatically-importing-the-sample-submission-excel-file.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.