excel-import-service.md

<td>Physical Storage</td>
<td>Storage Code</td>
<td>VARCHAR</td>
<td><br />
</td>
<td>Storage Code</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td>1</td>
<td>$STORAGE_POSITION.STORAGE_RACK_ROW</td>
<td>FALSE</td>
<td>TRUE</td>
<td>Physical Storage</td>
<td>Storage Rack Row</td>
<td>INTEGER</td>
<td><br />
</td>
<td>Number of Rows</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="odd">
<td>1</td>
<td>$STORAGE_POSITION.STORAGE_RACK_COLUMN</td>
<td>FALSE</td>
<td>TRUE</td>
<td>Physical Storage</td>
<td>Storage Rack Column</td>
<td>INTEGER</td>
<td><br />
</td>
<td>Number of Columns</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td>1</td>
<td>$STORAGE_POSITION.STORAGE_BOX_NAME</td>
<td>FALSE</td>
<td>TRUE</td>
<td>Physical Storage</td>
<td>Storage Box Name</td>
<td>VARCHAR</td>
<td><br />
</td>
<td>Box Name</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="odd">
<td>1</td>
<td>$STORAGE_POSITION.STORAGE_BOX_SIZE</td>
<td>FALSE</td>
<td>TRUE</td>
<td>Physical Storage</td>
<td>Storage Box Size</td>
<td>CONTROLLEDVOCABULARY</td>
<td>$STORAGE_POSITION.STORAGE_BOX_SIZE</td>
<td>Box Size</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td>1</td>
<td>$STORAGE_POSITION.STORAGE_BOX_POSITION</td>
<td>FALSE</td>
<td>TRUE</td>
<td>Physical Storage</td>
<td>Storage Box Position</td>
<td>VARCHAR</td>
<td><br />
</td>
<td>Box Position</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="odd">
<td>1</td>
<td>$STORAGE_POSITION.STORAGE_USER</td>
<td>FALSE</td>
<td>TRUE</td>
<td>Physical Storage</td>
<td>Storage User Id</td>
<td>VARCHAR</td>
<td><br />
</td>
<td>Storage User Id</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td>1</td>
<td>$XMLCOMMENTS</td>
<td>FALSE</td>
<td>FALSE</td>
<td><br />
</td>
<td>Comments</td>
<td>XML</td>
<td><br />
</td>
<td>Comments log</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="odd">
<td>1</td>
<td>$ANNOTATIONS_STATE</td>
<td>FALSE</td>
<td>FALSE</td>
<td><br />
</td>
<td>Annotations State</td>
<td>XML</td>
<td><br />
</td>
<td>Annotations State</td>
<td><br />
</td>
<td><p><br />
</p>
<p><br />
</p></td>
</tr>
</tbody>
</table>

### Entity Types Versioning

#### General Usage 

Version is a mandatory field for entity types, it just starts at 1; and
during updating a type definition is expected to increment it;
otherwise, the system will ignore the changes.

#### Explanation

Additionally, use the keyword FORCE to reinstall the type even if is
present and has been deleted.

The system keeps track of what versions of entities have been installed
storing this information, so in the future when one updates their types,
the version specified in the spreadsheet is checked against the stored
version.

For every TYPE found in the Excel sheet the next algorithm is performed:

    IF ENTITY OR (TYPE.Version > STORED_VERSION) OR (TYPE.Version == FORCE): // If is a new version
        IF ITEM NOT EXISTS in openBIS:
            CREATE ITEM                             
        ELSE: // Doesn't exist branch
            IF FAIL_IF_EXISTS:
                THROW EXCEPTION
            IF UPDATE_IF_EXISTS:
                UPDATE ITEM
            ELSE IF IGNORE_EXISTING:
                PASS // Ignore as requested
    ELSE:
        PASS // Ignore object that have not been updated

  

## Entity Definitions

Most entities can be created, excluding DataSets*.* There are
differences due to the nature of the defined elements themselves.

General Rules:

-   Header order is arbitrary.
-   When referring to another entity only Identifiers are allowed.
    Sample Variables are the only exception.
-   Vocabulary values in property value rows can be referred to by
    either the vocabulary term code or the vocabulary term label.

  

If a mandatory header is missing it results in an error.

Repeated headers will result in an error, in case a Property shares
Label with an Attribute is encouraged to use the property code instead.

### Space

<table>
<thead>
<tr class="header">
<th>Headers</th>
<th>Mandatory</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>Code</td>
<td>Yes</td>
</tr>
<tr class="even">
<td>Description</td>
<td>Yes</td>
</tr>
</tbody>
</table>

**Example**

<table>
<tbody>
<tr class="odd">
<td><strong>SPACE</strong></td>
<td style="text-align: left;"><br />
</td>
</tr>
<tr class="even">
<td><strong>Code</strong></td>
<td style="text-align: left;"><strong>Description</strong></td>
</tr>
<tr class="odd">
<td>ELN_SETTINGS</td>
<td style="text-align: left;">ELN Settings</td>
</tr>
<tr class="even">
<td>DEFAULT_LAB_NOTEBOOK</td>
<td style="text-align: left;">Default Lab Notebook</td>
</tr>
<tr class="odd">
<td>METHODS</td>
<td style="text-align: left;">Folder for methods</td>
</tr>
<tr class="even">
<td>MATERIALS</td>
<td style="text-align: left;">Folder for th materials</td>
</tr>
<tr class="odd">
<td>STOCK_CATALOG</td>
<td style="text-align: left;">Folder for the catalog</td>
</tr>
<tr class="even">
<td>STOCK_ORDERS</td>
<td style="text-align: left;">Folder for orders</td>
</tr>
<tr class="odd">
<td>PUBLICATIONS</td>
<td style="text-align: left;">Folder for publications</td>
</tr>
</tbody>
</table>

### Project

<table>
<thead>
<tr class="header">
<th>Headers</th>
<th>Mandatory</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>Identifier</td>
<td>Yes on UPDATES, ignored on INSERT</td>
</tr>
<tr class="even">
<td>Code</td>
<td>Yes</td>
</tr>
<tr class="odd">
<td>Space</td>
<td>Yes</td>
</tr>
<tr class="even">
<td>Description</td>
<td>Yes</td>
</tr>
</tbody>
</table>

**Example**

<table>
<tbody>
<tr class="odd">
<td><strong>PROJECT</strong></td>
<td style="text-align: left;"><br />
</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td><strong>Identifier</strong></td>
<td style="text-align: left;"><strong>Code</strong></td>
<td><strong>Description</strong></td>
<td><strong>Space</strong></td>
</tr>
<tr class="odd">
<td>/DEFAULT_LAB_NOTEBOOK/DEFAULT_PROJECT</td>
<td style="text-align: left;">DEFAULT_PROJECT</td>
<td>Default Project</td>
<td>DEFAULT_LAB_NOTEBOOK</td>
</tr>
<tr class="even">
<td>/METHODS/PROTOCOLS</td>
<td style="text-align: left;">PROTOCOLS</td>
<td>Protocols</td>
<td>METHODS</td>
</tr>
<tr class="odd">
<td>/STOCK_CATALOG/PRODUCTS</td>
<td style="text-align: left;">PRODUCTS</td>
<td>Products</td>
<td>STOCK_CATALOG</td>
</tr>
<tr class="even">
<td>/STOCK_CATALOG/SUPPLIERS</td>
<td style="text-align: left;">SUPPLIERS</td>
<td>Suppliers</td>
<td>STOCK_CATALOG</td>
</tr>
<tr class="odd">
<td>/STOCK_CATALOG/REQUESTS</td>
<td style="text-align: left;">REQUESTS</td>
<td>Requests</td>
<td>STOCK_CATALOG</td>
</tr>
<tr class="even">
<td>/STOCK_ORDERS/ORDERS</td>
<td style="text-align: left;">ORDERS</td>
<td>Orders</td>
<td>STOCK_ORDERS</td>
</tr>
<tr class="odd">
<td>/ELN_SETTINGS/TEMPLATES</td>
<td style="text-align: left;">TEMPLATES</td>
<td>Templates</td>
<td>ELN_SETTINGS</td>
</tr>
<tr class="even">
<td>/PUBLICATIONS/PUBLIC_REPOSITORIES</td>
<td style="text-align: left;">PUBLIC_REPOSITORIES</td>
<td>Public Repositories</td>
<td>PUBLICATIONS</td>
</tr>
</tbody>
</table>

### Experiment

<table>
<thead>
<tr class="header">
<th>Headers</th>
<th>Mandatory</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>Identifier</td>
<td>Yes on UPDATES, ignored on INSERT</td>
</tr>
<tr class="even">
<td>Code</td>
<td>Yes</td>
</tr>
<tr class="odd">
<td>Project</td>
<td>Yes</td>
</tr>
<tr class="even">
<td>Property Code</td>
<td>No</td>
</tr>
<tr class="odd">
<td>Property Label</td>
<td>No</td>
</tr>
</tbody>
</table>

**Example**

<table>
<tbody>
<tr class="odd">
<td><strong>EXPERIMENT</strong></td>
<td style="text-align: left;"><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td><strong>Experiment type</strong></td>
<td style="text-align: left;"><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="odd">
<td><strong>COLLECTION</strong></td>
<td style="text-align: left;"><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td><strong>Identifier</strong></td>
<td style="text-align: left;"><strong>Code</strong></td>
<td><strong>Project</strong></td>
<td><strong>Name</strong></td>
<td><strong>Default object type</strong></td>
</tr>
<tr class="odd">
<td>/METHODS/PROTOCOLS/GENERAL_PROTOCOLS</td>
<td style="text-align: left;">GENERAL_PROTOCOLS</td>
<td>/METHODS/PROTOCOLS</td>
<td>General Protocols</td>
<td>GENERAL_PROTOCOL</td>
</tr>
<tr class="even">
<td>/STOCK_CATALOG/PRODUCTS/PRODUCT_COLLECTION</td>
<td style="text-align: left;">PRODUCT_COLLECTION</td>
<td>/STOCK_CATALOG/PRODUCTS</td>
<td>Product Collection</td>
<td>PRODUCT</td>
</tr>
<tr class="odd">
<td>/STOCK_CATALOG/SUPPLIERS/SUPPLIER_COLLECTION</td>
<td style="text-align: left;">SUPPLIER_COLLECTION</td>
<td>/STOCK_CATALOG/SUPPLIERS</td>
<td>Supplier Collection</td>
<td>SUPPLIER</td>
</tr>
<tr class="even">
<td>/STOCK_CATALOG/REQUESTS/REQUEST_COLLECTION</td>
<td style="text-align: left;">REQUEST_COLLECTION</td>
<td>/STOCK_CATALOG/REQUESTS</td>
<td>Request Collection</td>
<td>REQUEST</td>
</tr>
<tr class="odd">
<td>/STOCK_ORDERS/ORDERS/ORDER_COLLECTION</td>
<td style="text-align: left;">ORDER_COLLECTION</td>
<td>/STOCK_ORDERS/ORDERS</td>
<td>Order Collection</td>
<td>ORDER</td>
</tr>
<tr class="even">
<td>/ELN_SETTINGS/TEMPLATES/TEMPLATES_COLLECTION</td>
<td style="text-align: left;">TEMPLATES_COLLECTION</td>
<td>/ELN_SETTINGS/TEMPLATES</td>
<td>Template Collection</td>
<td><br />
</td>
</tr>
<tr class="odd">
<td>/PUBLICATIONS/PUBLIC_REPOSITORIES/PUBLICATIONS_COLLECTION</td>
<td style="text-align: left;">PUBLICATIONS_COLLECTION</td>
<td>/PUBLICATIONS/PUBLIC_REPOSITORIES</td>
<td>Publications Collection</td>
<td>PUBLICATION</td>
</tr>
</tbody>
</table>

### Sample

<table>
<thead>
<tr class="header">
<th>Headers</th>
<th>Mandatory</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>$</td>
<td>No</td>
</tr>
<tr class="even">
<td>Identifier</td>
<td>Yes on UPDATES, ignored on INSERT</td>
</tr>
<tr class="odd">
<td>Code</td>
<td>No</td>
</tr>
<tr class="even">
<td>Project</td>
<td>No</td>
</tr>
<tr class="odd">
<td>Experiment</td>
<td>No</td>
</tr>
<tr class="even">
<td>Auto generate code</td>
<td>No</td>
</tr>
<tr class="odd">
<td>Parents</td>
<td>No</td>
</tr>
<tr class="even">
<td>Children</td>
<td>No</td>
</tr>
<tr class="odd">
<td>Property Code</td>
<td>No</td>
</tr>
<tr class="even">
<td>Property Label</td>
<td>No</td>
</tr>
</tbody>
</table>

**Example**

<table>
<tbody>
<tr class="odd">
<td><strong>SAMPLE</strong></td>
<td style="text-align: left;"><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td><strong>Sample type</strong></td>
<td style="text-align: left;"><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="odd">
<td><strong>ORDER</strong></td>
<td style="text-align: left;"><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td><strong>$</strong></td>
<td style="text-align: left;"><strong>Identifier</strong></td>
<td><strong>Code</strong></td>
<td><strong>Space</strong></td>
<td><strong>Project</strong></td>
<td><strong>Experiment</strong></td>
<td><strong>Order Status</strong></td>
</tr>
<tr class="odd">
<td><br />
</td>
<td style="text-align: left;">/ELN_SETTINGS/TEMPLATES/ORDER_TEMPLATE</td>
<td>ORDER_TEMPLATE</td>
<td>ELN_SETTINGS</td>
<td>/ELN_SETTINGS/TEMPLATES</td>
<td>/ELN_SETTINGS/TEMPLATES/TEMPLATES_COLLECTION</td>
<td>Not yet ordered</td>
</tr>
</tbody>
</table>

#### Defining Parent and Children in Samples

Parent and child columns can be used to define relations between
samples. Samples can be addressed by:

1.  $ : Variables, only really useful during batch inserts for samples
    with autogenerated codes since Identifiers can't be known. Variables
    SHOULD start with $.
2.  Identifiers

  

Parents and children SHOULD be separated by an end of line, each sample
should be in its own line.

<table>
<tbody>
<tr class="odd">
<td><strong>SAMPLE</strong></td>
<td style="text-align: left;"><br />
</td>
<td style="text-align: left;"><br />
</td>
<td style="text-align: left;"><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td><strong>Sample type</strong></td>
<td style="text-align: left;"><br />
</td>
<td style="text-align: left;"><br />
</td>
<td style="text-align: left;"><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="odd">
<td><strong>ORDER</strong></td>
<td style="text-align: left;"><br />
</td>
<td style="text-align: left;"><br />
</td>
<td style="text-align: left;"><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td><strong>$</strong></td>
<td style="text-align: left;"><strong>Parents</strong></td>
<td style="text-align: left;"><strong>Children</strong></td>
<td style="text-align: left;"><strong>Identifier</strong></td>
<td><strong>Code</strong></td>
<td><strong>Space</strong></td>
<td><strong>Project</strong></td>
<td><strong>Experiment</strong></td>
<td><strong>Order Status</strong></td>
</tr>
<tr class="odd">
<td><br />
</td>
<td style="text-align: left;"><br />
</td>
<td style="text-align: left;"><br />
</td>
<td style="text-align: left;">/ELN_SETTINGS/TEMPLATES/ORDER_TEMPLATE_A</td>
<td>ORDER_TEMPLATE</td>
<td>ELN_SETTINGS</td>
<td>/ELN_SETTINGS/TEMPLATES</td>
<td>/ELN_SETTINGS/TEMPLATES/TEMPLATES_COLLECTION</td>
<td>Not yet ordered</td>
</tr>
<tr class="even">
<td>$B</td>
<td style="text-align: left;"><p><br />
</p></td>
<td style="text-align: left;"><br />
</td>
<td style="text-align: left;">/ELN_SETTINGS/TEMPLATES/ORDER_TEMPLATE_B</td>
<td>ORDER_TEMPLATE</td>
<td>ELN_SETTINGS</td>
<td>/ELN_SETTINGS/TEMPLATES</td>
<td>/ELN_SETTINGS/TEMPLATES/TEMPLATES_COLLECTION</td>
<td>Not yet ordered</td>
</tr>
<tr class="odd">
<td><br />
</td>
<td style="text-align: left;"><p>/ELN_SETTINGS/TEMPLATES/ORDER_TEMPLATE_A</p>
<p>$B</p></td>
<td style="text-align: left;">/ELN_SETTINGS/TEMPLATES/ORDER_TEMPLATE_D</td>
<td style="text-align: left;">/ELN_SETTINGS/TEMPLATES/ORDER_TEMPLATE_C</td>
<td>ORDER_TEMPLATE</td>
<td>ELN_SETTINGS</td>
<td>/ELN_SETTINGS/TEMPLATES</td>
<td>/ELN_SETTINGS/TEMPLATES/TEMPLATES_COLLECTION</td>
<td>Not yet ordered</td>
</tr>
<tr class="even">
<td><br />
</td>
<td style="text-align: left;"><br />
</td>
<td style="text-align: left;"><br />
</td>
<td style="text-align: left;">/ELN_SETTINGS/TEMPLATES/ORDER_TEMPLATE_D</td>
<td>ORDER_TEMPLATE</td>
<td>ELN_SETTINGS</td>
<td>/ELN_SETTINGS/TEMPLATES</td>
<td>/ELN_SETTINGS/TEMPLATES/TEMPLATES_COLLECTION</td>
<td>Not yet ordered</td>
</tr>
</tbody>
</table>

### Properties and Sample Variables

As a general rule, properties would only accept data of the specified
type.

Sample properties would typically require an Identifier to be given but
a variable '$' could be used instead for a sample declared at any point
of the document, including cyclical dependencies. This is useful for
scenarios where Sample codes are autogenerated and can't be known in
advance.

### Entity Versioning

They don't have versioning, only entity types have versioning.

## Master Data as a Core Plugin

The master data plugin is an AS core plugin.

Directory structure **(important)** :

![image info](img/1806.png)

Use standard initialize-master-data.py handle as it is ingested by
openbis on startup. **Excel files** should be organised **in
*master-data*** **directory** in the same plugin and **scripts** should
be contained in ***scripts* directory** under master-data.

Contents of initialize-master-data.py:

    from ch.ethz.sis.openbis.generic.server.asapi.v3 import ApplicationServerApi
    from ch.systemsx.cisd.openbis.generic.server import CommonServiceProvider
    from ch.ethz.sis.openbis.generic.asapi.v3.dto.service.id import CustomASServiceCode
    from ch.ethz.sis.openbis.generic.asapi.v3.dto.service import CustomASServiceExecutionOptions
    from ch.systemsx.cisd.openbis.generic.server.jython.api.v1.impl import MasterDataRegistrationHelper
    import sys

    helper = MasterDataRegistrationHelper(sys.path)
    api = CommonServiceProvider.getApplicationContext().getBean(ApplicationServerApi.INTERNAL_SERVICE_NAME)
    sessionToken = api.loginAsSystem()
    props = CustomASServiceExecutionOptions().withParameter('xls', helper.listXlsByteArrays()) \
        .withParameter('xls_name', 'ELN-LIMS-LIFE-SCIENCES').withParameter('update_mode', 'UPDATE_IF_EXISTS') \
        .withParameter('scripts', helper.getAllScripts())
    result = api.executeCustomASService(sessionToken, CustomASServiceCode("xls-import-api"), props)

  

There are following parameters to fill (Easiest is to use
MasterDataRegistrationHelper to evaluate parameter values):

-   'xls': Array of excel files. It can be easily acquired by calling
    helper.listXlsByteArrays or listCsvByteArrays.
-   'xls\_name' - Name for the batch, it is used by versioning system.
-   'update\_mode' - See "Modes" section.
-   'scripts' - if you have any scripts in your data, provide them here.
    It is easiest to get it with MasterDataRegistrationHelper
    getAllScripts function.

'results' object is a summary of what has been created.

**Example**

For an complete up to date example, please check the
eln-lims-life-sciences plugin that ships with the installer or on the
official Git repository:

<https://sissource.ethz.ch/sispub/openbis/-/tree/master/openbis_standard_technologies/dist/core-plugins/eln-lims-life-sciences/1/as>

Or download the complete plugin using the next link:

<https://sissource.ethz.ch/sispub/openbis/-/archive/master/openbis-master.zip?path=openbis_standard_technologies/dist/core-plugins/eln-lims-life-sciences>

## Known Limitations

-   Property type assignments to entity types cannot be updated since
    the current V3 API does not support this functionality. This means
    that a change in the order of assignments or group names during an
    update will be ignored.