Description of XLSX File Format¶
The Excel file is used to export records to Excel and import records from Excel into the system. And, you can either download an empty template for importing or use the results of the export (available in batch operations with records).
An Excel file can consist of several sheets, each one containing a description of a different object. The type of object can be determined by the first letter in the sheet name:
E - entity/lookup entity. Is the main sheet;
N - complex attribute (from Nested);
R - relation.
Also the name of the sheet specifies the logical name of the object (but no more than 31 characters).
When re-importing records (editing existing data) using ETALON_ID, regardless of which source system is specified, the records will be loaded on behalf of the internal (system) source. To load data on behalf of other sources, use EXTERNAL_ID.
Tip
It is recommended to explore the Excel file using a ready-made example. For example, perform a batch export operation for any entity/lookup entity, or, if no data is available, download an import template
Main Entity/Lookup Entity Sheet¶
Column |
Description |
Import |
Export |
ID |
Virtual key for matching objects from different sheets. The ID value is not stored in the system |
The ID values are read from all sheets of the file. When the ID matches, the entity/lookup entity record with the specified ID includes the objects that have the matched ID. |
Not unloaded when exporting |
ETALON_ID |
System identifier of the etalon record |
Optional. Can be empty. If ETALON_ID is specified, then during import a record with a matched identifier will be overwritten (the empty or undeleted attributes will be changed). When importing, ETALON_ID is paired with the source system you specified at Step 1 of the record import. This is necessary to identify the record if there is no value in the EXTERNAL_ID column. ETALON_ID must not be filled in during the initial data loading. Otherwise, the records will be rejected. When editing existing records through ETALON_ID, the data will always be loaded from the source system name |
Unloaded when exporting |
EXTERNAL_ID |
External record key (identifier of the record in the source system) |
Can be empty for existing records. When importing, EXTERNAL_ID is paired with the source system that was specified in step 1 of importing records. This is necessary to identify new record. It is mandatory for importing records with more than one period |
Not unloaded during export |
If ETALON_ID and EXTERNAL_ID of the imported record does not match ETALON_ID and EXTERNAL_ID of the existing record, the record loading will fail. In this case, if only EXTERNAL_ID is specified, the record will be loaded as a new one |
|||
IS_ACTIVE |
Activity indicator, which determines whether the record validity period is marked for deletion |
Optional. One of the following states must be specified: TRUE or FALSE. Where FALSE is the period marked for deletion. If IS_ACTIVE is empty, then the action is the same as in the TRUE state |
Unloaded when exporting |
For hierarchical lookup entities - the indicator determines whether the record is marked for deletion |
Optional. One of the states must be specified: TRUE or FALSE. Where FALSE is the record marked for deletion. If IS_ACTIVE is empty, then the behavior is similar to the parameter in state FALSE |
Unloaded when exporting |
|
EXTERNAL_KEYS |
List of pairs of record external keys and source systems. Each key/source pair is written with a separator |
Not used during import |
Unloaded when exporting |
Only for entity and simple lookup entity |
|||
FROM |
Start date/time of the record time period |
Optional. Can be empty. The cell must contain the date in the format DD.MM.YYYY HH:MI:SS |
Unloaded during export if it has a value different from “-∞” |
TO |
End date/time of the record time period |
Optional. Can be empty. The cell must contain the date in the format DD.MM.YYYY HH:MI:SS |
Unloaded during export if it has a value different from “+∞” |
*No value in the FROM field is interpreted as “-∞”, TO - as “+∞”. For example, if you specify the current date as the TO value and leave the FROM field empty, the record will be relevant up to and including today |
|||
Only for hierarchical lookup entity |
|||
PARENT_ID |
Parent node identifier |
Optional. Can be empty - in this case the imported record becomes the root node |
Unloaded when exporting |
The following columns contain the main attributes of the entity/lookup entity. The identifier of each attribute has the form “Entity/lookup_entity_name”. For example, COUNTRY.NAME for the COUNTRY lookup entity and the NAME attribute. A hint is also specified. For example, Country List >> Name.
List of Complex Attributes¶
Only complex attributes of the first level are processed.
Column |
Description |
Import |
Export |
ID |
Virtual key for matching objects from different sheets. The ID value is not stored in the system |
Used only for linking to the validity periods of a record (table row) from the main sheet and the subsequent search. If there is a record on the main sheet with the corresponding ID, FROM, TO columns, the complex attribute will be added to the found row and will be used to update the corresponding validity period of the record. If the record on the main sheet is not found, the complex attribute is ignored |
Not unloaded during export |
ETALON_ID |
System identifier of the Etalon record |
Used together with FROM, TO to bind to the record validity periods (table row). If the key is set, the search will be performed among the rows of the main sheet (which contains the data inserts). If the main sheet contains a record with the corresponding ETALON_ID, FROM, TO columns and an empty ID column, the complex attribute will be added to the found row. If no record is found on the main sheet, the complex attribute is ignored |
Unloaded during export |
EXTERNAL_ID |
External record key (identifier of the record in the source system) |
Used together with FROM, TO for binding to the record validity periods (table row). If the key is set, the search will be performed among the rows of the main sheet (which contains data inserts). If there is a record with the corresponding EXTERNAL_ID, FROM, TO columns and empty ID and ETALON_ID columns on the main sheet, the complex attribute will be added to the found line. If the record on the main sheet is not found, the complex attribute is ignored |
Not unloaded during export |
If ETALON_ID and EXTERNAL_ID of the imported attribute does not match the ETALON_ID and EXTERNAL_ID of the existing attribute, then the loading of the attribute will fail |
|||
FROM |
Start date/time of the record validity period (the record to which the complex attribute is added) |
Optional. Can be empty. (interpreted as “-∞”) The cell must contain the date in DD.MM.YYYY HH:MI:SS format. Complex attributes are imported when the ID, FROM and TO match the same fields in the entity/lookup entity record on the main sheet |
Unloaded during export if it has a value different from “-∞” |
TO |
End date/time of the record validity period (the record to which the complex attribute is added) |
Optional. Can be empty. (interpreted as “+∞”) The cell must contain the date in DD.MM.YYYY HH:MI:SS format. Complex attributes are imported when the ID, FROM and TO match the same fields in the entity/lookup entity record on the main sheet |
Unloaded during export if it has a value different from “+∞” |
IS_ACTIVE |
There is no for complex attributes |
||
EXTERNAL_KEYS |
List of pairs of record external keys and source systems. Each key/source pair is written with a separator |
Filled in the same way as for the main sheet |
Filled in the same way as for the main sheet |
List of Relations¶
Column |
Description |
Import |
Export |
|---|---|---|---|
ID |
Virtual key for matching objects from different sheets. The ID value is not stored in the system |
Filled in the same way as for the complex attribute |
Filled in the same way as for the complex attribute |
ETALON_ID |
System identifier of the Etalon record |
Filled in the same way as for the complex attribute |
Filled in the same way as for the complex attribute |
EXTERNAL_ID |
External record key (identifier of the record in the source system) |
Filled in the same way as for the complex attribute |
Filled in the same way as for the complex attribute |
If the ETALON_ID and EXTERNAL_ID of the imported record does not match the ETALON_ID and EXTERNAL_ID of the existing record, the record loading will fail. In this case, if only EXTERNAL_ID is specified, the record will be loaded as new |
|||
FROM |
Start date/time of the record validity period (for main sheet) |
Optional. Can be empty. (interpreted as “-∞”) The cell must contain the date in DD.MM.YYYY HH:MI:SS format. |
Unloaded during export if it has a value different from “-∞” |
TO |
End date/time of the record validity period (for main sheet) |
Optional. Can be empty. (interpreted as “+∞”) The cell must contain the date in DD.MM.YYYY HH:MI:SS format. |
Unloaded during export if it has a value different from “+∞” |
No value in FROM field is interpreted as “-∞”, TO - as “+∞”. For example, if you specify the current date as the TO value and leave the FROM field empty, the record will be relevant up to and including today |
|||
TO_ETALON_ID |
Etalon key of the right end of relation (ID of the record which is pointed by relation) |
Used to bind records during import |
Unloaded during export |
IS_ACTIVE |
There is no for relations |
||
TO_EXTERNAL_ID |
External record key of the right end of relation |
Used to bind records during import |
Not unloaded during export |
TO_DISPLAY_NAME |
Display name of the record which is pointed by relation |
Not used during import |
Unloaded during export |
Additional Info¶
Each sheet has system attributes (columns in blue) and main object attributes.
Each attribute corresponds to a separate column.
Each new row corresponds to a separate record with a validity period (if a record contains several validity periods, each period will be a separate row).
The ID, ETALON_ID and EXTERNAL_ID in the attributes and relations sheets are used to identify the record (validity period) to which they are related.
Identification is made by the contents of the main sheet. If no record is found, the inserted object (complex attribute or relation) will be ignored.
Searching Records by System Attributes¶
When filling out the template, you should note the possible content of the three main system record attributes: ID, ETALON_ID and EXTERNAL_ID. In addition, all objects (relations, complex attributes) for binding include validity periods (FROM and TO field values).
Priority of columns when identifying: ID, ETALON_ID, then EXTERNAL_ID.
If none of the keys are set in the main sheet, it will be inserted as a new one. It is impossible to attach objects to such a record.
If there is an ID is Set, ETALON_ID is Empty, EXTERNAL_ID is Empty: the record will be inserted as a new one. Only this key will be used for searching between sheets.
If the main sheet ID is Empty, ETALON_ID is Set, EXTERNAL_ID is Empty: ETALON_ID will be used for searching between sheets. Only this key will be used to search between sheets.
If the main sheet ID is Empty, ETALON_ID is Empty, EXTERNAL_ID is Set: EXTERNAL_ID will be used for searching between sheets. Only this key will be used to search between sheets.
If the main sheet has ID is Set, ETALON_ID is Set, EXTERNAL_ID is Set: ID will be used for searching between sheets. Only this key will be used to search between sheets.
If main sheet ID is Set, ETALON_ID is Set, EXTERNAL_ID is Empty: ID will be used for searching between sheets. Only this key will be used to search between sheets.
If main sheet ID is Set, ETALON_ID is Empty, EXTERNAL_ID is Set: ID will be used for searching between sheets. Only this key will be used to search between sheets.
It is recommended to fill one key type for all objects of the particular record (for entity/lookup entity itself, for complex attributes, relations).
Importing Records with Multiple Validity Periods¶
Multiple periods of the same record are loaded only with EXTERNAL_ID.
In the .xlsx file, such a record with several validity periods should occupy several rows, where each of the rows should contain its own validity periods (FROM and TO columns).
In case the existing record is updated, the ETALON_ID of all rows of the same record must be the same.
A period that completely overlaps (is included in) another one will be merged with a bigger period.