Import relationships from Excel via a matrix

[Guillaume]

Hi,

I created a relationships matrix of requirements versus processes.  I need to create each relationship with an overlay (e.g. CRUD) using data that's been provided in Excel.

It would be nice if a relationship matrix could be exported and completed in Excel e.g. via a CSV file, before being updated back into EA.

Is there anything that could achieve this out there?

Thanks

[Geert Bellekens]

Have you checked out the excel import/export features of eaDocX? I don't know if they do exactly what you need, but it may be a step in the right direction.

Geert

[Guillaume]

Hi Geert,

I extensively use eaDocX Excel module, however it doesn't support importing relationships from Excel into EA (you can export them).
I did write scripts and used Tagged Value alongside eadocx to import classes & associations, but it won't apply to the matrix update scenario here.

I will continue to investigate...

thanks

[Sam Courtney]

We have a platform to export/import content with relationships into EA. You can see details here:
http://www.aprocessgroup.com/products/apg-modelflow/
Contact us or send me a PM and I'll help you further!

[Ian Mitchell]

The eaDocX elves tell me that we have some code somewhere will allows for importing of relationships using Excel - I will try to find it.

[Ian Mitchell]

Apologies to the eaDocX elves: this feature is already built and delivered - someone forgot to update the help (me).
Available from v3.6 onwards.
In the spreadsheet, just add extra rows, one for each Connector, like:
Relation | ElementA name | Element B name | Relationship Type | (optional) relationship name | (optional) rel stereotype.
Restriction is that ALL the elements referenced MUST be present in the spreadsheet, including any existing elements. This gets over the problem of identifying existing elements: just add them temporarily to the target package.
Enjoy!

[Robert Sheridan]

I do exactly this using VBA.  You can write a utlity that goes along each row and for each cell checks for existing connectors and either creates or deletes depending on the contents of the cell.  You can use overlays in this process.
You can export as well as import the matrix which makes it very useful for bulk updates.
I can share the code if it would be useful

[Ceri Davies]

Yes please, that would be very useful!

[Benoitguis]

I am also interested in this solution. Can you provide me the macro?

[Arshad]

Hi All

Did you try Sparx MDG Integration for Microsoft Office ?
https://sparxsystems.com.au/products/mdg/int/office/office-integration.html

You can directly import relationships from word \ excel into EA.


HTH
Arshad

[Guillaume]

I'm re-opening this thread as I'm looking for a way to update an existing relationships matrix via Excel.
Arshad, I looked at the MDG for Office but I cannot find any proper way to render a relationships matrix and update the values in Excel.

Robert, I'm interested in the tool you had if you still have it!

This would be a nice new feature to have in EA and/or Prolaborate web solution.

[rupertkiwi]

Hi Guillaume,

I use the code below to create relationships in Excel. There are a number of further properties that can be changed. You will need to set up your worksheet in the following format:

SourcePackage, SourceType,SourceName,SourceID,TargetPackage,TargetType,TargetName,TargetID,RelationshipType,RelationshipName

Note that the 'RealtionshipType' filed is a drop down with the following options:

Association
Aggregation
Composition
Dependency
Generalization
Realization
Trace
Extension


Sub relationshipcreate(outputws As Worksheet)
    Dim x As Integer
    x = 3
    Do Until IsEmpty(outputws.Cells(x, 4))
        If IsEmpty(outputws.Cells(x, 4)) Or IsEmpty(outputws.Cells(x, ) Or IsEmpty(outputws.Cells(x, 9)) Then
            MsgBox "The relationships have not been populated correctly in row " & x & vbCrLf & vbCrLf & "Please check the " & outputws & " tab" & _
            vbCrLf & vbCrLf & x - 3 & " relationships have been created in EA", vbOKOnly
            Exit Sub
        Else
            Call addConnector(outputws.Cells(x, 4), outputws.Cells(x, , outputws.Cells(x, 9), outputws.Cells(x, 10))
            x = x + 1
        End If
    Loop
    MsgBox x - 3 & " relationships have been created in EA", vbOKOnly
    outputws.Select
    outputws.Cells.EntireColumn.AutoFit
    outputws.Cells(3, 1).Select
End Sub


Function addConnector(SourceElementID, TargetElementID, ConnectorType, connectorname)
Dim SourceElement As EA.element
Dim newconnector As EA.connector
    Dim repository As EA.repository
    Set eaapp = GetObject(, "EA.App")
Set repository = eaapp.repository
Set SourceElement = repository.GetElementByID(SourceElementID)

Set newconnector = SourceElement.connectors.AddNew(" ", ConnectorType)

newconnector.SupplierID = TargetElementID
newconnector.Name = connectorname
newconnector.Update
Set addConnector = newconnector
End Function

Hope that helps,
Rupert