Excel template
Reverse-Engineering of an Excel file pre-supposes that you have first performed forward-engineering of your existing model, as described here. If you plan to import a new model, you at least need to export an empty model for the same target.
WARNINGS:
- Do not change the titles or order of the columns!
- Referential integrity is not enforced by this template. You need to ensure the coherence of the references from one tab to the other!
To import CSV files into Hackolade, it is advised to go first export an Excel template, then link it to the CSV file, before importing back into Hackolade.
To import the content of this Excel, use the Hackolade function Tools > Reverse-Engineering > Excel template
Assuming that the user has not altered the integrity of the exported Excel file (i.e. GUIDs or column names), the following behavior occurs:
- when applied to an open model, and provided that the target and GUID of the model match, the information in the imported Excel file is processed as an update/insert to the existing model. If applied to a new model, the entire imported Excel is processed as an insert;
- based on a given GUID, the property values imported write over the existing ones. This includes writing over empty values, or non-empty values. It also includes deleting values when the value imported has been nulled. Properties not imported leave untouched their existing value;
- an object (model, container, entity, or attribute) with no GUID will be created, ideally inserted in the proper order;
- the creation of complex structures is supported, as long as dot notation is used in the imported Excel file.
- Multiple data types are supported as well, as long as the | separator is used and the data types correspond to the acceptable values. This can be a bit tricky though, e.g. {"id":"c973c0a0-7e84-11e9-9a55-eba2b9bb390e" ,"type":"string" ,"enum":["user" ,"owner" ,"tester"]} | {"id":"d7cd4e50-7e84-11e9-9a55-eba2b9bb390e" ,"type":"null"} | {"id":"cbd7a140-7e84-11e9-9a55-eba2b9bb390e" ,"type":"numeric" ,"minimum":"" ,"exclusiveMinimum":false,"maximum":"", "exclusiveMaximum":false,"multipleOf":"" ,"unit":""}
- a new GUID is generated for each new object, or for an object for which the GUID is not recognized.
So as to not create instability or any kind of model corruption at time of import of the Excel, it is first scanned for the following validations:
- the import is rejected if the target of the Excel file does not match the target of the open or new model;
- there can be only one line (other than the title line) in the model tab;
- for each target, we can only import if the tab names match the acceptable values;
- a column name (property) does not have to be marked for export in the config to be usable in the Excel import, as long as it is a valid “propertyName”;
- an attribute line referencing an entity not present in the entities tab cannot be imported. Similarly, an entity referencing a container not present in the containers tab cannot be imported. And a container referencing a model not present in the model tab cannot be imported;
- unknown property value: we can only accept valid values as previously exported and visible in the last tab "Lists".
If validation is successful, the following message is displayed:
But if things don't validate properly, you may get a message such as this one:
For advanced users
Most users will use this Excel export/import feature for a productive manner to edit bulk data for a model, but without changing the structure of the model itself. The feature allows much more:
1) Add a scalar attribute to an existing entity
This is the simplest use case. In the "entity attributes" tab (which could be named "Collection fields", or "Table columns, etc... depending on the target), simply insert a blank line where you want the attribute to appear in the model. You should note the following:
- the GUID of an attribute is the unique number used internally by Hackolade. For an existing attribute being modified, this MUST NOT change, as it is the key used for matching upon import. For a new attribute, the cell should be left blank.
- the Name column uses "dot notation", starting with the name of the entity (table, collection,...) If you want to add an attribute "firstName" to an entity "users", you would enter users.firstName in the Name cell. Spaces are allowed and do not require quotes or encoding.
- the Entity ID represents the GUID of the entity. It is required to add the attribute to the correct entity. Note that if there is an incoherence between the entity name in the Name cell and the Entity ID, it is the latter that is being used during reverse-engineering.
- make sure to choose the data Type from the dropdown box
2) Add a complex attribute (object) to an existing entity
To add a nested object, itself made of attributes, you can extent the dot notation in the Name cells. For example to add an address object to a user entity, you would create/insert:
- one line for the object, for example user.address with a data type object (or document or map, depending on the target)
- a line per attribute of the object, for example:
while following the guidelines in 1) above. Nesting can go several levels deep, within the limits of each target.
3) Add a complex attribute (array) to an existing entity
For arrays, the same dot notation is supported. For example to add an array of friends to a user entity, you would create/insert:
- one line for the array, for example user.friends with a data type array (or list of set, depending on the target)
- one line for the array item, with the data type of the item Array items don't have names.
Note that you can combine objects and arrays, for example users.addresses.[0].address.street
4) Add an attribute with multiple data types
If this form of polymorphism is allowed by the target, you may do so from the Excel template. The easiest way to understand how to do it might be to first create a multiple type attribute in Hackolade, then export to Excel. Then you can mimic the syntax. The separator between types is a pipe ("|"). The GUID is repeated:
{
"id": "c973c0a0-7e84-11e9-9a55-eba2b9bb390e",
"type": "string",
"enum": ["user", "owner", "tester"]
}
|
{
"id": "d7cd4e50-7e84-11e9-9a55-eba2b9bb390e",
"type": "null"
}
|
{
"id": "cbd7a140-7e84-11e9-9a55-eba2b9bb390e",
"type": "numeric",
"minimum": "",
"exclusiveMaximum": false,
"maximum": "",
"exclusiveMinimum": false,
"mode": "",
"multipleOf": "",
"sample": "",
"unit": ""
}
5) Add a new entity
In the Entities tab (which could be names "Collections", "Tables", etc... depending on the target), simply insert a blank line for the entity. You should note the following:
- the GUID of an entity is the unique number used internally by Hackolade. For an existing entity being modified, this MUST NOT change, as it is the key used for matching upon import. For a new entity, the cell should be left blank.
- the Entity ID represents the GUID of the Container (database/bucket/keyspace/workspace/...) to which this entity belongs. This is required in order to link the entity to the correct container.
6) Add a definition
Only internal and model definitions can be edited in an Excel having been exported. If you need to make modifications to an external definition, it is required to export that model separately.
Internal and model definitions are exported in a separate tab, generally called Definitions (for Cassandra it is User-Defined Types, etc...) attributes can be edited or inserted just like any other attribute (cfr points 1 thru 4 above.)
7) Add a reference to a definition
To create a reference to a definition requires an entry in the $ref cell:
- model reference: #model/definitions/<definitionName>
- internal reference: #/definitions/<defintionName>
- external reference: <drive>:<path>/<modelFileName>.json#/<attributeName>
8) Add a relationship
Relationships go into their own tab. They have their own GUID as well. If adding a new one, leave the GUID cell blank. Each relationship needs the GUID of:
- parent entity
- parent attribute
- child entity
- child attribute
They also have a relationship type: foreign key, or foreign master.