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

excel tabs

Free download

Elements and Attributes

Columns A till F are used in the sheet ElementsAndAttributes . Fill in the the columns with your own data.

  • Type
    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.
  • Name
    The name of the element or attribute.
  • Stereotype
    The stereotype of the element or attribute. Use null to specify an empty stereotype.
  • Description
    The description of the element or attribute.
  • Attribute Type
    The type of the attributeThis 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.

Glossary

In the 2nd 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.

Import

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

excel buttons

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.

0 replies
  1. Nick Monrad
    Nick Monrad says:

    Is it possible that you could add a function to add tagged values to elements, not just to attributes?

    Reply
  2. geertbellekens
    geertbellekens says:

    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.

    Geert

    Reply
  3. Kristina Ek
    Kristina Ek says:

    Hi!
    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.
    Best regards,
    Kristina

    Reply
    • Geert Bellekens
      Geert Bellekens says:

      Kristina,

      “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.

      Geert

      Reply
  4. Kristina Ek
    Kristina Ek says:

    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!

    Reply
  5. wilsondebs
    wilsondebs says:

    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

    Reply
  6. Megan
    Megan says:

    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.

    Reply
  7. rodrigocmn
    rodrigocmn says:

    Thanks Geert! I’ve managed to implement it!

    Let me know if you want a copy.

    Cheers,
    Rod

    Reply
  8. Joe Reed
    Joe Reed says:

    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?

    Reply
    • Geert Bellekens
      Geert Bellekens says:

      Joe,

      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.

      Geert

      Reply
  9. Joe Reed
    Joe Reed says:

    Geert,
    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.
    Joe

    Reply
    • Geert Bellekens
      Geert Bellekens says:

      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.

      Reply
  10. Erik Paquet
    Erik Paquet says:

    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.

    Reply
  11. Joe Reed
    Joe Reed says:

    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.

    Reply
  12. Joe Reed
    Joe Reed says:

    Hi Geert,
    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!
    Joe

    Reply
  13. Pavel
    Pavel says:

    Gi Geert,
    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.
    Thanks.
    Pavel.

    Reply
  14. Julian
    Julian says:

    Hi Geert,
    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
    Julian.

    Reply
  15. Kelera
    Kelera says:

    Hey,

    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?

    Reply
    • Geert Bellekens
      Geert Bellekens says:

      Hi Matthew,

      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.

      Reply
      • Matthew Thomas
        Matthew Thomas says:

        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)

        Reply
        • Geert Bellekens
          Geert Bellekens says:

          Hi Matthew,

          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.

          Reply
  16. Rick
    Rick says:

    Geert,
    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?

    Reply

Trackbacks & Pingbacks

  1. […] Version 3 added the feature to import the glossary into EA. […]

  2. […] This tool is a modification of Geert Bellekens’ “Simple VBA Excel to EA importer” published in his website (https://bellekens.com/2013/04/30/simple-vba-excel-to-ea-importer-v3/). […]

  3. […] There is an updated version available. Check out EA Excel Importer V3 […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.