Configure Excel Sync

If data is to be synchronized to Aeneis via an Excel spreadsheet or data is to be exported from Aeneis to an Excel spreadsheet and CSV files, a synchronization instance must be created and configured accordingly. For each synchronization instance, either synchronization or export can be configured.

Requirements: For synchronization of data from an Excel spreadsheet, the file with the data must be available. For each category that is to be synchronized, a worksheet must be created in the Excel spreadsheet. For each attribute of the corresponding category that is to be synchronized, a column must be created in the Excel spreadsheet in the corresponding worksheet.

Requirements: To export data to an Excel spreadsheet and CSV files, a template of an Excel spreadsheet with the structure must be available. A worksheet must be created in the Excel spreadsheet for each category that is to be exported. For each attribute of the corresponding category to be exported, a column must be created in the Excel spreadsheet in the corresponding worksheet.

Configuration:

Once you have created a synchronization instance, as described in Create synchronization instance, you can further configure it in the properties.

Property	Description
Configuration
Trigger	In this attribute you can store a trigger that controls when the synchronization is executed and the data is transferred from the Excel spreadsheet to Aeneis or exported from Aeneis.
Group	This property is for internal use to bundle triggers and jobs and should not be changed!
Cron expression	Here you can enter a time pattern that controls when synchronization is performed automatically. See also: Time pattern (Cron)
Enabled	By default, synchronization instances are disabled after you created them. Via this property you can enable the synchronization instance.
Successor job	Here you can link jobs together. Once the execution of the job has been completed, the successor job referenced here is executed immediately. Note: If the successor job is deactivated, it is skipped and the next active successor job in the chain is executed. Note: Successor jobs do not require their own trigger or cron expression.
Excel Table
Filename	The path to the file that will be synchronized and where the files will be stored during export, if the Export property is enabled, is entered here.
Export	This property allows you to control whether the data from Aeneis should be exported to an Excel spreadsheet and CSV files. Exporting will export an Excel spreadsheet with all data units in worksheets (as defined in the template) and data from Aeneis and additionally for each data unit a CSV file with the data from Aeneis.
Export column headings	This property allows you to control whether the column headings of the Excel template are also exported when exporting.
Separator columns (CSV)	Here you can define a different separator for the columns for the export of the CSV files. The default separator is semicolon (;).
Separator values (CSV)	Here you can define a different separator between the values of a column/attribute for the CSV export files. The default separator is comma (,).
Encoding (CSV)	Here you can define a different encoding for the export of the CSV files. The default encoding is UTF-8.
Date format	Here you can define another date format
Other attributes
Log	As soon as a synchronization has been executed, the log entry of the synchronization appears in the Log property. The log entry shows whether the synchronization was successful and how many objects were created, updated, mapped, removed, moved and deleted.
Worksheet name	Reference here a category from Aeneis with which the data unit is to be synchronized (e.g. the category Employees, if employees are to be synchronized) or whose objects are to be exported.
Key column	Here you can select a column in the external system that uniquely identifies the record (primary key). The field label is composed of the name of the data unit and the column number (starting with 0) (e.g. Employee_0). If no key column has been selected here, the first column in the third-party system is interpreted as a key column. Note: The key column in the third-party system data unit should be of type String. The key column must uniquely identify the data set. Key fields must consist of a single column and must not be composed of multiple keys.
Default ACL for new objects	Define here the access control list that will be set automatically for newly created objects in Aeneis.
ACL field (optional)	This property allows you to control whether the ACL is set via the synchronization with the third-party system. If there are IDs in a column in the third-party system that set the ACLs, you can assign the corresponding column in this property. During synchronization, the ACL is filled using the values of the stored column.
Delete removed objects	This property allows you to control that objects deleted between two synchronizations in the third-party system are also deleted in Aeneis. If this property is not enabled, the objects that were deleted in the third-party system are only marked as removed in Aeneis.
Create new objects	This property allows you to control whether new objects are created in Aeneis when synchronizing with the third-party system.
Create version on delete	This property allows you to control that if objects have been deleted in the third-party system between two synchronizations, a new version is created in Aeneis before the synchronization.
Create version on changes	This property allows you to control that if objects have been changed in the third-party system between two synchronizations, a new version is created in Aeneis after the synchronization.
Add unreferenced objects to	Here you can reference an object to which objects are assigned that are not assigned to any other object. Only objects under which objects of the corresponding category may be created can be referenced here.
Separator columns (CSV)	Only relevant for web service synchronizations.
Name of the column (the numbering of the columns in Aeneis starts at 0)	In the properties with the names of the columns, reference a matching attribute of the category that was referenced for the Excel worksheet. During synchronization, the values of the column are transferred from the third-party system to the attribute in Aeneis. When exporting, the values of the attribute are written to the column in the Excel spreadsheet and the CSV file. Note: You cannot reference an attribute here until you have referenced a category and already saved it.
Refers to	For columns with a foreign key you can select a data unit to which the column refers in the foreign system (relation). The column is always linked to the key column of the selected data unit.
Value when marked as deleted	Here you can store a value that will be written to the attribute if it is marked as deleted during a synchronization. This property can be used to fill a status field or to set the name of an object after deletion.
Fill empty attributes only	You can use this property to set that only attributes that are still empty are filled during synchronization.
Allow only references	This property allows you to set that the attribute is only referenced in the objects of the category and not assigned as the main attribute. The main assignment of the attribute then takes place in the object of the category referenced in the Add unreferenced objects to property.
Object renderer	Here you can reference an object renderer that determines the output of the reference (e.g. first name last name or last name, first name)