Description of XLSX File Format¶
General Info¶
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/reference set. Main sheet.
N - complex attribute (from "nested").
The number of sheets of complex attributes depends on the number of nested objects used in the entity. For example, if the entity has two complex attributes "c1" and "c2", which use the same nested object "nested1", then the xlsx file will contain two sheets "N<nested1>1" and "N<nested1>2".
R - relation.
C - classification.
The sheet name specifies the system and display name of the object (not more than 31 characters). The sheet name of a complex attribute specifies the name of the attribute itself and the name of the nested object.
Note
In the Excel file, all timestamps are specified in the UTC+0 time zone, and may differ from the time in the user interface. For example, the interface uses the Moscow time zone UTC+3, but when uploading records to Excel, the time is displayed in the UTC+0 zone.
Tip
It is recommended to explore the Excel file using a ready-made example. For example, perform a batch export operation for any entity/reference set, or, if no data is available, download an import template
Features of Import¶
When importing, the main sheet must be present in the file, even if there are no records in it.
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.
If only relations are imported, then the conditions for the ends of the relations must be met.
Left end of relation:
ETALON_ID - the record should already be in MDM. The ETALON_ID of the existing relation is specified;
EXTERNAL_ID - the record pointed to by EXTERNAL_ID must be imported in the same file, or already be in MDM, and in this case have the same source system;
ID - the record pointed to by EXTERNAL_ID must be imported in the same file.
Right end of relation:
ETALON_ID - the record should already be in MDM, or in the case of "Contains" relation type - should be empty;
EXTERNAL_ID - you need to fulfill one of the conditions. The record should already be in MDM, having the same source system, or imported in the same file in case of "Reference" and "Many-to-many" relation types that refer to the same entity. In case of "Contains" relation type, the EXTERNAL_ID can be empty and then it will be generated automatically.
Main Entity / Reference Set 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/reference set 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 reference sets - 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 reference set |
||||
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 reference set |
||||
PARENT_ID |
Parent node identifier |
Optional. Can be empty - in this case, the imported record is attached to the root node |
Unloaded when exporting |
|
PARENT_CODE |
The value of the main code attribute of the parent record | Optional. Can be empty - in this case, the imported record is attached to the root node |
Unloaded when exporting |
||
The following columns contain the main attributes of the entity/reference set. The identifier of each attribute has the form "Entity/Reference_set_name". For example, COUNTRY.NAME for the COUNTRY reference set and the NAME attribute. A hint is also specified. For example, Country List >> Name.
List of Complex Attributes¶
In the sheet header, the complex system name of the object is displayed as
complex_attr_name>nested_name >> Complex attribute: Name of the complex attribute > Name of nested entity. In the display names, the separator is " > " with spaces.The system names in the header are used to correctly identify the nested object and the complex attribute to which it belongs. In the imported file, the headers must be composed in the same way, otherwise the nested object will be skipped during import and will not be updated (the old value of the complex attribute will be in the record) or will not be inserted (it will be empty in the record).
Excel files created in the MDM Universe version 6.8 and below are not supported. To load them, you need to correct the header of the complex attributes sheet in accordance with the new format.
Complex attributes of several levels 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/reference set 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/reference set 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 |
List of Classification¶
The classification is described line by line for each attribute of the node. For example, to describe a node with 3 attributes, you will need three lines. The columns EXTERNAL_ID, FROM, TO, CLASSIFIER_VERSION_ID, CLASSIFIER_IS_ACTIVE, CLASSIFIER_NODE_NAME will be the same; only ATTRIBUTE_NAME and ATTRIBUTE_VALUE will differ.
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 |
Not unloaded during export |
ETALON_ID |
The system ID of the reference record to which the classifier node is linked |
Filled in the same way as for the complex attribute |
Unloaded during export |
EXTERNAL_ID |
The foreign key of the record (the ID of the record in the source system) to which the classifier node is linked |
Filled in the same way as for the complex attribute |
Unloaded during export |
FROM |
Date/time of the start of the period of relevance (the record to which the classifier node is added) |
Optional. It can be empty (interpreted as "-∞"). The date is specified in the format DD.MM.YYYY, the date/time in the format DD.MM.YYYY hh:mm:ss. Complex attributes are imported when ID, FROM and TO match with similar fields in the registry/directory entry on the main sheet |
Not unloaded during export |
TO |
Date/time of the end of the validity period (the record to which the classifier node is added) |
Optional. It can be empty (interpreted as "+∞"). The date is specified in the format DD.MM.YYYY, the date/time in the format DD.MM.YYYY hh:mm:ss. Complex attributes are imported when ID, FROM and TO match with similar fields in the registry/directory entry on the main sheet |
Not unloaded during export |
CLASSIFIER_VERSION_ID |
The logical name of the classifier |
Must be filled in |
Unloaded during export |
CLASSIFIER_NODE_NAME |
Classifier node name (logical name of the classifier version node) |
Must be filled in |
Unloaded during export |
CLASSIFIER_IS_ACTIVE |
Determines whether the classifier node is active (not to be confused with the Active Version flag in the Record Export wizard) |
Must be filled in. When importing with the value false, the node that previously existed in the record will be marked for deletion. (For editing records by importing). If a node has several attributes, that is, several lines, to delete a node when importing a file, it is enough to put false in only one of them. |
Unloaded during export. When exporting an existing record with a classification, use the true value. When exporting a logically deleted record with a classification, use the false value. Classifier nodes removed from an existing record during editing are not exported. |
ATTRIBUTE_NAME |
The attribute logical name of the classifier node |
Must be filled in if the attribute exists |
Unloaded during export if it exists |
ATTRIBUTE_VALUE |
The value of the classifier node attribute |
It is necessary to fill in at least the attributes that have the "Required" property |
Unloaded during export if it exists |
Additional Info¶
Notes:
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/reference set 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.
Features of Importing Reference Sets (using PARENT_ID and PARENT_CODE)
Both steps are run in a single transaction. If an exception is thrown, e.g. due to unresolved parent code, the whole batch operation gets rolled back, index changes get cleaned. This behaviour differs from traditional batch failure processing because a single request is not isolated from others: record children upsert success depends on result of the parent upsert.
Both code-based and id-based hierarchy resolution are supported in a single xlsx import. If only one of these fields is filled, it is used. If both are filled, and they point to different records, an exception is thrown. If none, the record is attached to the root.
Primary code value does not identify a record, i.e. if etalon_id is empty, it is treated as a new record and will raise an exception, should a record with the same code exist.