excel-import-service.md

# Excel Import Service

-   Created by [Fuentes Serna Juan Mariano
    (ID)](%20%20%20%20/display/~juanf%0A), last modified on [Dec 05,
    2022](/pages/diffpagesbyversion.action?pageId=53745981&selectedPageVersions=7&selectedPageVersions=8 "Show changes")

  

## Introduction

The Excel import service reads xls definitions for both types and
entities and send them to openBIS. It is the replacement of the old
master data scripts adding support for the creation of openBIS entities.

**The goals are:**

For common users an import format with the following features to avoid
the shortcomings of the old format:  

-   Recognisable labels as column names.
-   Multi-type imports.
-   Parents/Children creation and linking on a single import.

For advanced users like consultants and plugin developers a tool that
allows to specify on an Excel sheet:

-   Metadata model.
-   Basic entity structures used for navigation.

## Modes

To support different use cases the import service supports the next
modes, specifying one of them is mandatory.

-   UPDATE IF EXISTS: This one should be the default mode to use to make
    incremental updates.
-   IGNORE EXISTING: This mode should be used when the intention is to
    ignore updates. Existing entities will be ignored. That way is
    possible to avoid unintentionally updating entities and at the same
    time adding new ones.
-   FAIL IF EXISTS: This mode should be used when the intention is to
    fail if anything is found. That way is possible to avoid making any
    unintentional changes.

## Organising Definition Files

All data can be arranged according to the needs of the user, in any
number of files and any number of worksheets. All files have to be in
one directory.

The names of the files and worksheets are ignored by the service, the
user is advised to use descriptive names that they can quickly
remember/refer to later.

If there are dependencies between files they should be submitted
together or an error will be shown.

**Example:**

We want to define vocabularies and sample types with properties using
these vocabularies. We can arrange our files in several ways:

1.  put vocabulary and sample types in separate files named i.e
    vocabulary.xls and sample\_types.xlsx respectively
2.  put vocabulary and sample types in different worksheets in the same
    xls file
3.  put everything in one worksheet in the same file

## Organising Definitions

**Type definitions:**

The order of type definitions is not important for the Excel import
service, with exception of Vocabularies, those need to be placed before
the property types that use them.

**Entity definitions:**

Type definitions for the entities should already exist in the database
at the time when entities are registered. Generally Entity definitions
are placed at the end.

### Text cell formatting (colours, fonts, font style, text decorations)

All types of formatting are permitted, and users are encouraged to use
them to make their excel files more readable. Adding any non text
element (table, clipart) will cause the import to fail.

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

(A valid, but not easily readable, example)

### Definition, rows and sheet formatting

-   A valid sheet has to start with definition on the first row.
-   Each definition has to be separated by one empty row.
-   Two or more consecutive empty rows mark the end of the definitions.
-   Empty spaces at the beginning or end of headers are silently
    eliminated.

  

If any content is placed after two consecutive empty rows it will result
in an error. This is to alert the user and avoid silently ignoring
content.

Header rows **NEED TO BE** a valid attribute of the entity or entity
type, property label or property code.

Any unintended header will result in an error. This is to avoid possible
misspellings and avoid silently ignoring content.

## Entity Types Definitions

All entity types can be created*.* There are differences due to the
nature of the defined elements themselves.

### Vocabulary and Vocabulary Term

Vocabulary

<table style="width:99%;">
<colgroup>
<col style="width: 49%" />
<col style="width: 50%" />
</colgroup>
<thead>
<tr class="header">
<th>Headers</th>
<th>Mandatory</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>Version</td>
<td>Yes</td>
</tr>
<tr class="even">
<td>Code</td>
<td>Yes</td>
</tr>
<tr class="odd">
<td>Description</td>
<td>Yes</td>
</tr>
</tbody>
</table>

Vocabulary Term

<table>
<thead>
<tr class="header">
<th>Headers</th>
<th>Mandatory</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>Version</td>
<td>Yes</td>
</tr>
<tr class="even">
<td>Code</td>
<td>Yes</td>
</tr>
<tr class="odd">
<td>Label</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>VOCABULARY_TYPE</strong></td>
<td><br />
</td>
<td><br />
</td>
<td><br />
</td>
</tr>
<tr class="even">
<td><strong>Version</strong></td>
<td><strong>Code</strong></td>
<td><strong>Description</strong></td>
<td><br />
</td>
</tr>
<tr class="odd">
<td>1</td>
<td>$STORAGE.STORAGE_VALIDATION_LEVEL</td>