The EA Excel import-export tool can be used to export and import between Sparx Enterprise Architect and Excel. Using only an Excel file, and the built in macro’s you can use it to
- Export and Import element and attribute data (including tagged values on elements and attributes)
- Import the Glossary
- Export and Import tagged values
Version 5.0
Version 5.0 adds a complete new import and export feature for elements and attributes.
- Configure exactly what you would like to export using an SQL Query
- Use GUID’s of elements and attributes to update items regardless of their location in the model
- Define whether to create, update, or delete items using the Action column
- Specify a datatypes package and link attribute types with the datatypes in the datatype package
- Support for a lot more fields such as alias, version, status, initial value etc.
- Export and import formatted notes (limited to bold, italic and underline)
- Import and export any tagged values you define on elements and attributes.
Download
Configuration
The sheet with the name Configuration is used to store the SQL query to be used to export the data from Enterprise Architect.
When updating this query keep the following guidelines in mind
- use aliases CLASSTYPE and CLASSGUID just like you would when writing a regular SQL Search of EA. The CLASSGUID is used to be able to find the element to update or delete.
- #Branch# can be used in the same way as in SQL Searches. It will expand to a list of all package ID’s for all the packages underneath the selected pacakge
- When selecting tagged values, make sure you prefix each tagged value name with TAG_ then followed by the name of te tagged value.
- If you want to export and import tagged values you have to make sure that your first have a line for the element, and then for it’s attributes. In the example SQL Query the ownerField is used for this purpose, to order the union of elements and their attributes.
- Fields with formatted notes have to be suffixed with _formatted (example: notes_formatted) If the suffix is not used this field is treated as a regular text field. Please be aware that processing formatted fields takes some additional processing time.
Supported fields
| Allowed fields | Used by |
| TAG_<tagname> | Element/Attribute |
| Name | Element/Attribute |
| Alias | Element/Attribute |
| Notes | Element/Attribute |
| Visibility | Element/Attribute |
| Stereotype | Element/Attribute |
| Multiplicity | Element/Attribute |
| Version | Element |
| Status | Element |
| Pos | Attribute |
| Datatype | Attribute |
| InitialValue | Attribute |
| length | Attribute |
| precision | Attribute |
| scale | Attribute |
Exporting
Before exporting make sure to open your model in Enterprise Architect. Then use the button Export in the Add-ins ribbon
In the following dialog it will show the selected package in the project browser. You can change to another package by clicking on the […] button.
Then press the Export button to actually export he data to Excel. The exported data will end up in the sheet Export_Import
Editing
After exporting you can edit the data to be imported. Using the Action column you can tell the import process what to do
- create
Ignores the classguid column and creates a new item - update
Checks the classguid column and updates the item with this guid. If the item is not found this is treated as a create - delete
Checks the classguid columns and deletes the item with this guid. If the item is not found this row is ignored.
Importing
In order to import the updated data click on the Import button in the Add-ins Ribbon
Then select the datatype package if needed and click Import
Glossary
In the sheet Glossary you can fill in columns A until C with:
- Term
The term name is mandatory. - Type
The type of the term is mandatory as well - Meaning
The meaning of the term is optional.
Importing the glossary the glossary can be done with the button Import Glossary in the Add-ins ribbon
Tagged Values
The Tagged Values sheet can only be used to edit existing tagged values, not for importing new tagged values into EA. It contains the following columns
- GUID
The unique identifier of the tagged value. Used to find the tagged value in the model. – Do not change. - Type
Used to indicate if the tagged value is on an element, attribute, operation or connector. – Do not change - Property
The name of the tagged value. This field can be used to rename tagged values - Value
The value of the tagged value. This field can be edited. - Notes
The notes of the tagged value. This field can be edited. - Owner
The name of the element that owns the tagged value. Used as a context indicator. Changing this field will have no effect.
To export the existing tagged values to Excel use the button Export Tagged Values. Importing tagged values is done with the button Import Tagged values.
Customizing
The code of this little tool is embedded as VBA in the document, and as such can be edited by anyone with basic programming skills.
To open the code editor press Alt-F11.
There are three major parts for the code
- ExcelImporter
The module that contains the importFromExcel macro. This is where you want to make changes. - EAConnector
A facade for EA’s API containing all kinds of useful functions. You probably don’t need to change anything here. - ExcelConnector
A facade for Excel with the code to get the values of the sheet. Again probably no need to change anything here.
Both the Excel file as the VBA code embedded in it is free for use. Just be kind and leave the reference to my name in the code if you use it.