Simple VBA Excel to EA importer v3
There is an updated version available. Check out Excel to EA Importer v4
The Excel to EA importer is a rather simple tool to import stuff into EA.
It has been available on the Sparx Community Site since 2009 and it has always been one of the most popular resources.
Version 2 was a small improvement allowing for different types of Elements.
In version 3, thanks to the kind help of Paulus, I’ve now added a new function in order to import Glossary Terms.
The excel file now has two tabs, one to import elements and attributes, the other to import the glossary terms
Elements and Attributes
Columns A till F are used in the sheet ElementsAndAttributes . Fill in the the columns with your own data.
Fill in the type of the Element you wish to import. This column should contain either Attribute or one of the EA Element types as defined in the documentation of EA.Element.Type. An attribute will be added to the last specified Element.
The name of the element or attribute.
The stereotype of the element or attribute. Use null to specify an empty stereotype.
The description of the element or attribute.
- Attribute Type
The type of the attribute. This column should not be used for Elements. Use null to specify an empty type.
- Attribute Length
This value will be added as a tagged value with the name length on the attribute. Again this is only for attributes.
In the 2nd sheet Glossary you can fill in columns A until C with:
The term name is mandatory.
The type of the term is mandatory as well
The meaning of the term is optional.
Once all you data has been filled in you are ready to import the data into EA.
- Open the sheet you wish to import
- If you are on the ElementsAndAttributes sheet press the button Import, or run the macro (Alt-F8) importFromExcel
- If you are on the Glossary sheet press the button Import Glossary, or run the macro (Alt-F8) importGlossaryFromExcel
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
The module that contains the importFromExcel macro. This is where you want to make changes.
A facade for EA’s API containing all kinds of useful functions. You probably don’t need to change anything here.
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.
Is it possible that you could add a function to add tagged values to elements, not just to attributes?
Should be rather trivial to add such a function.
Just copy EAConnector.addOrUpdateAttributeTag, and rename all Attributes with Element, change the type EA.AttributeTag into EA.TaggedValue and I think it should work.
Then of course you’ll need to define when to call addOrUpdateElementTag from the importFromExcel sub.
I would like to import other element types than the ones defined in the link you refer to above: http://www.sparxsystems.com/enterprise_architect_user_guide/9.3/automation/element2.html.
What I need right now is to import elements in the Data Flow Diagram, specifically the elements “Process” and “Data Store”.
Do you know if there is a solution for this?
In the dream scenario, I would like to import connectors aswell – information flows and/or data flows.
“Process” and “Data Store” are no native EA types. The are probably implemented using another type and a stereotype.
If I’m not mistaken a Process is actually an Activity with stereotype <<process>>
The same principle applies for Data Store.
With the EA API you can create connectors as well, so you could write an extension to the excel to EA importer to import connectors too.
Thanks for the extremely quick answer, which lead me in the right direction! I managed to import processes by putting “Activity” in the Type Column and “DFD_Process” in the Stereotype Column!
This importer is great – so useful for importing large coded domains. I’m trying to import code lists for use with the ArcGIS UML Profile which uses the initial value as an alias. How can I configure VBA code to import a column into the initial value?
I can successfully import values into the alias but there is no option for Initial in the myAttribute.* public Function addOrUpdateAttribute in the EA Connector
Hi!, I tried to import the glossary but I don´t see data in EA. I didn´t receive any error and the macro was executed without problems. I am using eaexcelimporter_v3
Could you please indicate where I should to see? Or what to do…
Your import is great!
Thanks in advance.
Hi! Great Tool!
Is there a way to import class operations?
Currently not, but it should be rather straightforward to extend the existing macro with an operation import function.
I was trying to, but couldn’t find operation in the object model.
Thanks Geert! I’ve managed to implement it!
Let me know if you want a copy.
I’d love a copy, you can send it to firstname.lastname@example.org, then I can put it up as a new version.
Hello, I need to import lot of class objects with “operations” in to the EA and cant find the way how to do it. Does your tool support this?
I’m also interested by a macro to import operations.
If you still have the customized macro, could you please send it to me? email@example.com
Thanks in advance!
Hi Geoffrey and Tomas,
Sorry for being offline for so long!
I’ve finally managed to write a post for the modified version of Geert’s importer:
Hope it helps… and thanks Geert again for the original code!
Hi Rod, thats it, thank you very much for your work and Geert for really good idea.
Hello Geert. Thanks for a very useful import tool. It will save me a lot of time importing information into EA. How could I go about having the tool automatically place each imported element into a new/specified package?
I guess you’ll have to add a new column and somehow indicate the package you want this to go in.
If I were you I would use a qualified name (names of packages separated with dots) to be indicate the package.
I’m afraid I’m not that good at programming, so I need some clarification. Would I add a new column with the target package name in all rows, or just the rows for the new element? Also, what does the statement look like to create the new package? Thanks.
I guess you would only need the package name on the element rows, not the attribute rows.
Creating a package is rather similar to creating an element in EA. You have to get hold of the parent package object and then do a AddNew() on the Packages collection of this parent package just like you do a AddNew() on the Elements collection when you are adding an Element.
THE time saver tool.
In about 5 minutes, I created a class (Entite) with 170 attributes, custom note for each attribute, type, length, Alias, etc.
I discovered that EA add the attributes in alphabetical order not as in my Excel file. Is there a way to force EA to respect my order?
Thank you very much for this tool.
There’s an option in EA somewhere called “allow free sorting”.
Turn that option on and EA will no longer order things alphabetically.
Hi Erik, how did you load the alias, did you change the original excel?
Hi again Geert,
I’m making some progress but I have a question. I created a row in the spreadsheet to be imported that contains the element type (package) and the name of the new/target package. I cloned parts of the Public Function addOrUpdateElement to create a new Public Function addOrUpdatePackage. It needs to reference getPackageByNameDepthFIrst, but I’m not sure what I need to “feed” it. I thought I would just need to pass the name of the parent package and the name of the package I want to check for existence/add, but it looks like something more is needed: aPackages As EA.Collection. What is that?
Thanks for your patience and help.
I got it working. It will now add a new package into which new elements will go if the package doesn’t already exist, similar to adding a new class if necessary. Thanks again for a great tool!
when importing notes to objects, is it possible to preserve linebreaks? We use Simple VBA Excel to EA importer to import Test Cases, but it does remove line breaks on import.
I am new to using SparxEA, and your Excel importer spreadheet is a lifesaver for batch uploading large lists of database tables and columns (and more) into the tool.
FYI – I have found a small problem in your Excel Connector getvalues macro. It is conservative about which columns it loads data from and gives up on a row as soon as it finds an empty cell, so if for example one or more attribute descriptions are empty in the ElementsAndAttributes sheet, it will not load the Attribute Type or Length for that row even if they are populated.
Thanks and regards
Yes indeed, that was my “lazy” way to figure out the range of the dataset to be imported.
That could definitely be improved.
When I tried to import the excel values onto sparx, the length and precision does not get updated. It remains blank. I am creating a new database datatype so I presumed that would be the reason it did not get updated. Would you have any advice on how I can fix this? Or if there is another updated import template available?
Does this support import of relationships between things?
No, I haven’t implemented such a feature yet myself, but I have reports from users that successfully changed the code to import relations.
Depending on your actual needs that could be fairly simple.
Thank you for the reply – this opens up a whole new world …
Actual needs are to (e.g.) import a two-column spreadsheet of databases versus the servers on which they are deployed (this column comma-separated), and map the databases to a <> stereotyped component, the servers to a <> stereotyped node, and create “appropriate” relationships between the databases and the servers on which they are deployed.
The Office MDG should do this but doesn’t work (see “Help with Microsoft Office – Import Excel” post on the EA forum”, which I’ve lodged as a bug with Sparx)
Seems entirely possible.
If you are not afraid of a bit of VBA, and you know your way around the EA API you could write something like that in a couple of hours.
Are there any examples of how to create a spreadsheet using VBScript
I don’t have any, but I’m sure there’s loads of examples on the web.
I’ve just been using your ExcelImporter to import classes and attributes, but is seems that it does not import the datatypes of an attribute, although I’ve put these values in the AttributeType column. Is this correct or is it a known bug?
If you fill in something in the description column it should work.