Tip: If you are fluent in python, you can alternatively use a community-created python library to import instead: https://github.com/jbaragry/ardoq-archimate

Here is a four-step process to follow if you would like to do an XML import in Ardoq.

Step 1: Open MS Excel and check if you have the developer tab. If not, please start here.

Note: XML to Excel macro script is written at the end of this article.

Step 2: Importing the XML

i. Open the XML using Notepad++ (recommended tool for XML editing) → search ‘http://www.w3.org/2001/XMLSchema-instance’→ and remove ‘-instance’ mentioned at the end of URL ‘https://www.w3.org/2001/XMLSchema’.

Note: If you do not perform the above step, you will get an error message (as shown in the screenshot) due to the XSD mentioned in the XML.

Check the error reason at https://www.w3.org/2001/XMLSchema-instance.

Ardoq importing XML error message example

ii. Save the changes in Notepad++.

iii. Select Developer → Import.

iv. Import XML into MS Excel. It should load without errors. 👍 Save the Excel file.

Step 3: Making the Excel ready to import into Ardoq

Now comes the fun part.

i. Managing the components

Select Developer → Code section → Macros
Type any name (of your choice for eg: main) and select create
Copy the macro code from the code (provided at the end of this article) and paste it into the text editor screen (replacing all text that already exists)
Scroll down to the bottom of the code and append ‘ManageComponents’ to Call, and press Run Sub (Green Play ▶ button placed in the editor under the menu option). This action will create a new worksheet, ‘Components’, and copy all components from sheet 1.
Check your excel sheet; Macro will create a new worksheet, ‘Components’ with all the components

ii. Managing References

Go back to the Macro and replace ‘ManageComponents’ with ‘ManageReferences’ and run it
Check your excel sheet; a new worksheet, ‘References’, should be created with all the ‘References’.
Note: This may contain references that do not correspond to any of the components. The next step will ensure to delete such references.
Go back to the macro sheet, and similar to step I replace ‘ManageReferences’ with ‘RemoveRows’
Delete worksheet ‘sheet1’ (manually), leaving only ‘Components’ and ‘References’ worksheets. Save and exit Excel.

Step 4. Loading the data in Ardoq

i. Open your Sandbox/Ardoq account, Select Import and integrations from the left icon bar.

ii. Select Excel as an option.

iii. Browse and upload the Excel prepared in previous steps.

iv. Select components in the left sidebar and then create a new workspace called ‘One workspace.' We will upload everything in a single workspace and then create separate workspaces as per requirement.

v. Use dropdown options available at the header of each column.

Settings for components : Set Identifier2 as Custom ID, ns3:type as type and ns1:name3 as component (name) and ns1:documentation as description.

Settings for references:

Create references in <the same workspace as components>

to components in <the same workspace as components>

Set source and target columns, and reference type.

vi. Select ‘Test Import’ at the top right.

vii. Select Import Ok if no errors. Now data is in Ardoq.

Step 5. Creating separate workspaces for all the component types

i. In perspective (left sidebar), select any one component type (For eg: Application Component).

ii. Creating a blank workspace: Click on the ‘Home’ Icon on the left and then on top right click on ‘create new’ and select ‘workspace’. Select ‘Blank Workspace’ from the options displayed.

iii. Select all ‘One workspace’ components in the left sidebar (you can perform the bulk selection by selecting the first item in the list, scrolling to the last item in the list, and shift + select the last item). Drag them to Blank Workspace (this creates a copy in the blank workspace)

iv. Open the right sidebar menu (click on 3 horizontal lines icon on the top right) and select ‘Manage Component Types’

v. Delete ‘Create your structure’ (top section in the left part of the screen) by clicking on ‘X next to it.

Ardoq creating separate workspaces for all the component types

vi. Select ‘Edit Workspace properties’ (same menu option: 3 horizontal lines icon on the top right) and name the Workspace (Tip: Name it according to the component type it contains eg: Application components) → Save.

vii. Repeat the process for each component type and create a new workspace.

viii. Delete One workspace and all related references.

Your XML is now in Ardoq in the recommended format. 🎉

Here is the XML to excel macro code:

'****To be used for correction: Delete all Sheets except sheet1*****'
Function DeleteSheets()
Dim xWs As Worksheet
Application.ScreenUpdating = False

Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
If xWs.Name <> "Sheet1" Then
xWs.Delete
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Function

'****Copy all components from sheet1 to new sheet 'components'*****'

Function ManageComponents()

With ThisWorkbook
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Components"
End With

Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name = "Components" Then
ws.UsedRange.Clear
Sheets("Sheet1").UsedRange.Copy Destination:=ws.Range("A1")
With ws.UsedRange
.AutoFilter Field:=6, Criteria1:=(Blanks) 'field = column that contains component types eg. F=6*******'
.Offset(1).EntireRow.Delete

.AutoFilter
End With
End If
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Function

'*****Copy all references from sheet1 to new sheet'references'*****'
Function ManageReferences()

With ThisWorkbook
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "References"
End With

Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name = "References" Then
ws.UsedRange.Clear
Sheets("Sheet1").UsedRange.Copy Destination:=ws.Range("A1")
With ws.UsedRange
.AutoFilter Field:=12, Criteria1:=(Blanks) 'field = column that contains reference source eg. L=12**'
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Function

'****Delete references that do not exist in sheet 'components'******'

Function RemoveRows()
Const source_id As String = "L" '**Column that contains reference source'
Const target_id As String = "M" '**Column that contains reference target'
Dim i As Long
Dim j As Long
Dim iLastRow As Long
Dim jLastRow As Long

'****Deleting references whose source cannot be found in components****'

With Worksheets("References")

iLastRow = .Cells(.Rows.Count, source_id).End(xlUp).Row

For i = iLastRow To 2 Step -1

If IsError(Application.Match(.Cells(i, source_id).Value, Worksheets("Components").Columns(5), 0)) Then

Worksheets("References").Rows(i).Delete

End If

Next i

End With

'****Deleting references whose target cannot be found in components****'

With Worksheets("References")

jLastRow = .Cells(.Rows.Count, target_id).End(xlUp).Row