Synchronize Excel spreadsheet

Background synchronization can be used to transfer data from an Excel spreadsheet to Aeneis and synchronize it regularly, or to export data from Aeneis to an Excel spreadsheet and CSV files.

If an Excel spreadsheet with data is synchronized to Aeneis, then the Excel spreadsheet must be structured in such a way that there is a worksheet in the Excel spreadsheet for each third-party system category, with the data that is transferred to Aeneis.

When exporting data from Aeneis to an Excel spreadsheet and CSV files, one Excel spreadsheet with one worksheet per category and additionally one CSV file for each category will be created per synchronization instance.

Attention: Only xls files can be used via Excel background synchronization. Files with the extension xlsx are not supported.

Types of Excel worksheets:

You can synchronize the following two types of worksheets in Aeneis:

Excel worksheet with records: the worksheet contains complete records of objects of the foreign system category with data as well as relationships using foreign record IDs (foreign keys).
Excel worksheet with relationship data: the worksheet contains relationship records of objects of a foreign system category. Only the unique record ID (primary key) and the attribute label of an object are recorded. The record ID can be used to establish the relationship from other worksheets (Relation)

The following table lists the differences between a worksheet with complete records and a worksheet with relationship data.

Excel worksheet with records of a third-party system category	Excel worksheet with relationship data for a third-party system category
Column names in Aeneis: The columns of a worksheet are synchronized as an attribute in Aeneis. Aeneis uses the worksheet name plus a sequential number separated by an underscore for the attribute names, e.g. Employee_1. The attribute is then linked to an Aeneis attribute, e.g. Label.
Content: The worksheet contains records of a foreign system category with unique record ID (primary key), attribute data and, if necessary, relationship data (foreign key) to other attributes.	Content: The worksheet contains records of a foreign system category with unique record ID (primary key) and the attribute Label.
First cell: The cell A1 remains empty.	First cell: The cell A1 is filled.
Column headers: Column headers can be assigned in the first row because cell A1 is empty.	Column headers: Column headers cannot be assigned because cell A1 must be filled.
Primary key: One of the columns must contain unique record IDs (primary keys) starting from the 2nd cell.	Primary key: One of the columns must contain a unique record ID (primary key) starting from the 1st cell.

Excel worksheet with records of a third-party system category

Excel worksheet with relationship data for a third-party system category

Column names in Aeneis:

The columns of a worksheet are synchronized as an attribute in Aeneis. Aeneis uses the worksheet name plus a sequential number separated by an underscore for the attribute names, e.g. Employee_1. The attribute is then linked to an Aeneis attribute, e.g. Label.

Content:

The worksheet contains records of a foreign system category with unique record ID (primary key), attribute data and, if necessary, relationship data (foreign key) to other attributes.

Content:

The worksheet contains records of a foreign system category with unique record ID (primary key) and the attribute Label.

First cell:

The cell A1 remains empty.

First cell:

The cell A1 is filled.

Column headers:

Column headers can be assigned in the first row because cell A1 is empty.

Column headers:

Column headers cannot be assigned because cell A1 must be filled.

Primary key:

One of the columns must contain unique record IDs (primary keys) starting from the 2nd cell.

Primary key:

One of the columns must contain a unique record ID (primary key) starting from the 1st cell.