EA Excel import-export

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

EA Excel import-export
Rated 4.75 out of 5
€0,00 Exl. VAT Add to cart

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.

14 thoughts on “EA Excel import-export”

Hi, I wonder of the function to ownerField as this does not have any function when importing your exampel, when resorted. Then each Attribute parent (owner) is the class above.

Geert Bellekens says:

07/04/2021 at 08:39

Hi Steen,

The ownerfield is only used to properly order the classes and attributes when exporting. It is indeed not used when importing.

Geert

Reply

Hi, I would like to ask: What does it mean Datatype Package within Import process?

Geert Bellekens says:

08/09/2021 at 06:53

If you select a datatype package, the importer will try to match an existing datatype as the type of your attribute (instead of simply having the name of the type registered in EA)

Reply

I have problem with Import into EA (export to excel from EA is OK). During import proces Classes are imported correctly (it looks like OK) but attributes are not imported. Also during import process the message window appears (it looks like for every attribute, so If there are 10 attributes I have to click for OK button 10 times). The message contains following information: “Microsoft OLE DB provider for ODBC Drivers [0x80004005] [MySQL] [ODBC 5.3(w) Driver] [mysql-5.7.34] Field ‘Name’ doesn’t have default value”

Is there any solution for this proble?

Geert Bellekens says:

08/09/2021 at 14:20

Looks like you are trying to import attributes or classes without specifying a name.

Reply
1. Slavomír Kinik says:
  
  08/09/2021 at 14:50
  
  Problem is probably different, I have done some investigation and when I import classes and attributes into model on my local physical disk in the PC, all is OK. When I am importing classes and attributes into project on the cloud (I do not know now how it is managed within company, server and similar matters) that time there is above mentioned problem.
  
  If anybody had such problem in the past, maybe could advise.
  
  Reply

On “Configuration” sheet in the Export Query part there is one mistake which should be corrected. In Class part within tagged value lines there is for second TAG bad expression “tv2.Property = ‘Tag1′” should be “tv2.Property = ‘Tag2′”.

Hello Geert,

I am trying to add a new property to the Export_Import sheet. I need to include the property “Not Null”. I have added a new column with boolean values (true/false, 0/1). When I run Import I get the following error:

Run-time error ‘438’:
Object doesn’t support this property or method

The error occurs on method: Private Function importItem()
Line: eaConn.importAttribute properties, owner, datatypes, pos

Can you show me how to add the Not Null column into the import and export?

Thank you.

Geert Bellekens says:

24/03/2022 at 08:52

Not Null is stored in the field AllowDuplicates

Reply

Just want to say Thank you.
I’m not a dev, more a superuser with excel and EA and I managed thanks to your tool to export my elements and all the tagged values with the Owner GUID. Excel can load both sheets on its own datamodel to easily create reports. This is great for my usecase !

First off, thank you for this body of work! I have a tagged value of “Element Type”. I updated the export query properly. It exports perfectly but when I try to import the data never makes it in the EA repo and everything else work fine. I get no errors. I suspect because of the space in my tagged value name since I do have another tagged value called “Label” that works perfectly.

Slavomir Kinik says:

29/06/2022 at 16:24

See above my post from 01/10/2021 at 14:40, maybe this could be solution of the problem

Reply
1. Zissis says:
  
  30/06/2022 at 19:08
  
  That does not apply to my problem. Here is my complete export query. The second tag does not get imported but the first one is fine:
  
  select ” as Action,o.Object_Type AS CLASSTYPE, o.ea_guid AS CLASSGUID, o.ea_guid as ownerField, -1 as Pos,
  o.Name, o.Stereotype, o.Note AS Notes_formatted,o.Alias, ” as Datatype, o.Multiplicity, o.Scope as Visibility,
  tv1.Value as TAG_Label, tv2.Value as TAG_ElementType
  from ((t_object o
  left join t_objectproperties tv1 on (tv1.Object_ID = o.Object_ID
  and tv1.Property = ‘Label’))
  left join t_objectproperties tv2 on (tv2.Object_ID = o.Object_ID
  and tv2.Property = ‘Element Type’))
  where o.Package_ID in (#Branch#)
  union all
  select ” as Action, ‘Attribute’ as CLASSTYPE, a.ea_guid as CLASSGUID, o.ea_guid as ownerField,a.Pos as Pos,
  a.name, a.Stereotype, a.Notes as Notes_Formatted, a.Style as Alias, a.Type as Datatype, a.LowerBound & ‘..’ & a.UpperBound as Multiplcity, a.Scope as Visibility,
  tv1.Value as TAG_Label, tv2.Value as TAG_ElementType
  from (((t_attribute a
  inner join t_object o on o.Object_ID = a.Object_ID)
  left join t_attributetag tv1 on (tv1.ElementID = a.ID
  and tv1.Property = ‘Label’))
  left join t_attributetag tv2 on (tv2.ElementID = a.ID
  and tv2.Property = ‘Element Type’))
  where o.Package_ID in (#Branch#)
  order by ownerField, Pos, name
  
  Reply

Steen Jensen says:

06/04/2021 at 11:37

Hi, I wonder of the function to ownerField as this does not have any function when importing your exampel, when resorted. Then each Attribute parent (owner) is the class above.

1. Geert Bellekens says:
  
  07/04/2021 at 08:39
  
  Hi Steen,
  
  The ownerfield is only used to properly order the classes and attributes when exporting. It is indeed not used when importing.
  
  Geert
  
Slavomír Kinik says:

07/09/2021 at 22:56

Hi, I would like to ask: What does it mean Datatype Package within Import process?

1. Geert Bellekens says:
  
  08/09/2021 at 06:53
  
  If you select a datatype package, the importer will try to match an existing datatype as the type of your attribute (instead of simply having the name of the type registered in EA)
  
Slavomír Kinik says:

08/09/2021 at 10:38

I have problem with Import into EA (export to excel from EA is OK). During import proces Classes are imported correctly (it looks like OK) but attributes are not imported. Also during import process the message window appears (it looks like for every attribute, so If there are 10 attributes I have to click for OK button 10 times). The message contains following information: “Microsoft OLE DB provider for ODBC Drivers [0x80004005] [MySQL] [ODBC 5.3(w) Driver] [mysql-5.7.34] Field ‘Name’ doesn’t have default value”

Is there any solution for this proble?

1. Geert Bellekens says:
  
  08/09/2021 at 14:20
  
  Looks like you are trying to import attributes or classes without specifying a name.
  
  1. Slavomír Kinik says:
    
    08/09/2021 at 14:50
    
    Problem is probably different, I have done some investigation and when I import classes and attributes into model on my local physical disk in the PC, all is OK. When I am importing classes and attributes into project on the cloud (I do not know now how it is managed within company, server and similar matters) that time there is above mentioned problem.
    
    If anybody had such problem in the past, maybe could advise.
    
Slavomir Kinik says:

01/10/2021 at 14:40

On “Configuration” sheet in the Export Query part there is one mistake which should be corrected. In Class part within tagged value lines there is for second TAG bad expression “tv2.Property = ‘Tag1′” should be “tv2.Property = ‘Tag2′”.

Jeff White says:

01/02/2022 at 14:46

Hello Geert,

I am trying to add a new property to the Export_Import sheet. I need to include the property “Not Null”. I have added a new column with boolean values (true/false, 0/1). When I run Import I get the following error:

Run-time error ‘438’:
Object doesn’t support this property or method

The error occurs on method: Private Function importItem()
Line: eaConn.importAttribute properties, owner, datatypes, pos

Can you show me how to add the Not Null column into the import and export?

Thank you.

1. Geert Bellekens says:
  
  24/03/2022 at 08:52
  
  Not Null is stored in the field AllowDuplicates
  
msespin says:

15/03/2022 at 20:56

Just want to say Thank you.
I’m not a dev, more a superuser with excel and EA and I managed thanks to your tool to export my elements and all the tagged values with the Owner GUID. Excel can load both sheets on its own datamodel to easily create reports. This is great for my usecase !

Zissis says:

29/06/2022 at 12:27

First off, thank you for this body of work! I have a tagged value of “Element Type”. I updated the export query properly. It exports perfectly but when I try to import the data never makes it in the EA repo and everything else work fine. I get no errors. I suspect because of the space in my tagged value name since I do have another tagged value called “Label” that works perfectly.

1. Slavomir Kinik says:
  
  29/06/2022 at 16:24
  
  See above my post from 01/10/2021 at 14:40, maybe this could be solution of the problem
  
  1. Zissis says:
    
    30/06/2022 at 19:08
    
    That does not apply to my problem. Here is my complete export query. The second tag does not get imported but the first one is fine:
    
    select ” as Action,o.Object_Type AS CLASSTYPE, o.ea_guid AS CLASSGUID, o.ea_guid as ownerField, -1 as Pos,
    o.Name, o.Stereotype, o.Note AS Notes_formatted,o.Alias, ” as Datatype, o.Multiplicity, o.Scope as Visibility,
    tv1.Value as TAG_Label, tv2.Value as TAG_ElementType
    from ((t_object o
    left join t_objectproperties tv1 on (tv1.Object_ID = o.Object_ID
    and tv1.Property = ‘Label’))
    left join t_objectproperties tv2 on (tv2.Object_ID = o.Object_ID
    and tv2.Property = ‘Element Type’))
    where o.Package_ID in (#Branch#)
    union all
    select ” as Action, ‘Attribute’ as CLASSTYPE, a.ea_guid as CLASSGUID, o.ea_guid as ownerField,a.Pos as Pos,
    a.name, a.Stereotype, a.Notes as Notes_Formatted, a.Style as Alias, a.Type as Datatype, a.LowerBound & ‘..’ & a.UpperBound as Multiplcity, a.Scope as Visibility,
    tv1.Value as TAG_Label, tv2.Value as TAG_ElementType
    from (((t_attribute a
    inner join t_object o on o.Object_ID = a.Object_ID)
    left join t_attributetag tv1 on (tv1.ElementID = a.ID
    and tv1.Property = ‘Label’))
    left join t_attributetag tv2 on (tv2.ElementID = a.ID
    and tv2.Property = ‘Element Type’))
    where o.Package_ID in (#Branch#)
    order by ownerField, Pos, name

Bellekens

Sparx Systems Enterprise Architect Consultant and Value Added Reseller

EA Excel import-export

Version 5.0

Download