Excel file

The feature to exchange data with Excel provides the ability to export a data model, or parts of it, to Microsoft Excel so properties could be easily edited in a tabular format, to be re-imported back into the application.  It facilitates productive bulk actions for the maintenance of properties.  It also allows creation of a new model - or additions to an existing model - by team members that might not have access to the application.

 

It includes the following capabilities: 

(i) editing containers, entities, and attributes of an existing model; 

(ii) selecting which properties of an attribute are exported in the Excel file, and hence which ones are filtered out and not exported; and 

(iii) selecting custom attributes to be added in the various data types of the plugin.

 

When doing a roundtrip from Hackolade to Excel and back, it is important to maintain integrity of the model and avoid any corruption.  Microsoft Excel does not allow all the controls and validations of the Hackolade applications.  Additionally, the application supports a large number of different targets with differing terminology, structure, and characteristics.  

 

Important note: let's not misunderstand what is meant with "bulk editing"...  the purpose of this function is to facilitate the enrichment of existing models, thanks to the flexibility and power of Excel, including the ability to xlookup, copy/paste information, etc.   The purpose is NOT to find a creative way to leverage Excel's lack of integrity rules to circumvent the logic and checks built in the application.  We suggest to be extra careful in particular when performing operations that touch the object GUIDs.  Copying or changing GUIDs create risks of corrupting the model upon import.

 

Important note: it is critical that the template used to import is of the same target and plugin version issued by the export process.

 

Important note: deleting a line in Excel will NOT result in a delete operation in the model when reverse-engineering the Excel back into the Hackolade model.  

 

To import CSV files into Hackolade, it is advised to first export an Excel template, then import the CSV file into the Excel template, before importing back into Hackolade.

 

Export model

To start your export for a model, simply go to Tools > Forward-Engineer > Excel file...  In the Object selection dialog, you may deselect objects that you do not wish to export.  

 

 

 

With the Options button, you may also access a dialog to select object properties that you do not wish to export:

 

The properties for each object are listed in the corresponding sections.  If you need to filter out a property, simply uncheck the corresponding box.

 

Instructions for the import of an Excel file can be found in Excel reverse-engineering.

 

Maintain configuration

This configuration can also be maintained in Tools > Options > Forward-Engineering, and there's one configuration per target.  The configuration itself can be exported to a JSON file so it can be distributed in the organization, and imported on other instances of the application.

 

 

 

Note: depending on the purpose of your export, you may want to resolve referenced definitions, or not.  When the purpose is to do bulk editing, then import the Excel back into your model, then in order to maintain integrity, when a reference to a definition is exported Excel, it must NOT  be resolved.  In other words, the sub-structure of the object is not exported.  

If on the other hand, the purpose is to publish a catalog of the objects in the model, it may be desirable to resolve the references.  Choose this option wisely.

 

 

Known limitations

Microsoft Excel (as well as LibreOffice) encounters memory limitations in the handling of validation lists, used to control in Excel boolean checkbox properties and for dropdown properties, and leading to this type of error message:

 

 

If you click Yes, you should be aware that values are no longer validated against validation lists, which could lead to undesired inconsistencies.  It is suggested instead to:

- increase RAM on the workstation,

- reduce the number of validated properties exported (checkbox or dropdown)

- reduce the number of entities (and hence attributes) exported

 

 

Working with relationships

When forward-engineering a data model to Excel, relationships are exported in the same way as other modeling objects.  This function primarily supports documentation, review, and auditing, but also allows bulk updates when needed.

 

While Excel offers flexibility, it does not provide the same structural guidance and safeguards as the Hackolade Studio UI.  As a result, certain combinations of changes that are technically possible in Excel may lead to inconsistent relationship definitions.

 

For this reason, editing relationships in Excel is supported but not the primary recommended workflow.  When users choose to do so, Hackolade Studio applies a limited set of guardrails during the reverse-engineer process to preserve model integrity.

 

In particular:

• if the property Has attributes = true, the relationship cannot be a Foreign Key or Foreign Master.  A relationship that carries attributes is, by definition, an association and cannot be modeled as a FK/FM relationship.
• if the property Has attributes = false, the relationship cannot have relationship attributes defined in the relationship-attributes sheet. In that case, the input is considered inconsistent.

 

If these rules are not respected, the inconsistent relationship is ignored during reverse-engineering and a warning is issued. It prevents silent data loss and ensures that your modeling intent remains explicit.