With the Excel integration, you can import your data as components or references in Ardoq using .xls, .xlsx, .xslm, or .csv files.
Before we start:
Get familiar with the basic concepts in Ardoq
Download the Excel sample file used in this article
Table of contents:
How Does Excel Integration Work?
The Excel integration enables you to translate a tabular data set such as an Excel spreadsheet to a graph representation such as Ardoq metamodels. To achieve this, the Excel integration comes with data mapping capabilities that will help you configure your tabular data so that it matches your Ardoq workspace metamodel.
Mapping allows you to:
Flexibly generate Ardoq metamodels based on your Excel data
Define which workspaces a sheet will import data into
Map columns in Excel to either descriptions, component types, fields, or tags in Ardoq
Test your import and verify that your Excel data is consistent with your mappings (i.e. check that the data type is correct with your field mapping)
You can also use the Excel integration to update existing data in Ardoq. This is a great option for doing bulk updates or changes.
Setting Up Your Spreadsheet
Download the Excel sample file to see how you should set up your Excel spreadsheet before uploading it to Ardoq. Or follow the instructions below.
1. Split your component data and reference data into different sheets. For example:
Where:
Applications, Business-units, and Capabilities contain component data
BU->Applications and Capabilities->Applications contain reference data
2. Organize your Excel spreadsheet data the following way:
Components sheet
List all components you wish to import in their own row. A row corresponds to a component in Ardoq.
Parent components should also live in their own row. To indicate hierarchy, assign the ‘Component (name)’ column type to two or more columns when configuring your data in Ardoq. Next, select the level of hierarchy and choose the component type.
Create component properties as columns. For example:
Name: The name of the component
Description: Add the main content or more details about the component
Tags: Add additional information to your components or references with tags that you can use to filter your Ardoq views.
Import ID’s: We recommend adding an ID column for the items you wish to import. Using IDs reduces the likelihood of mixing up components with similar names.
Custom field: Ardoq supports custom fields with types like Date, Number, Lists, Select Multi, Checkboxes and Tags. Use commas to separate multiple values in Tags and Select Multi lists.
References sheet
List all references you want to import in their own row. A row corresponds to a reference in Ardoq.
Specify the source and target components. You can do so in two different ways:
Using Import ID’s
Using a “fully qualified path” with “::” denoting a level. For example, Saas::Intercom
Use columns to indicate component source or target, the reference type or fields on references.
How To Import Your Data
1. Navigate to “Home > "Import & integrations > Excel"
2. Drag and drop the Excel file.
Saved configurations help you speed up the set up and data mapping. When starting from a saved configuration, all column types, workspace to import data into, and type of data (components or references) will be pre-filled for each sheet in the file.
3. Select the worksheet you want to configure from the table in the Overview tab or from the sidebar.
Here, you can also change the configuration you previously applied if you started the import from a saved configuration, or apply one if you started from scratch.
4. From here, you can start mapping your data to your Ardoq’s workspace metamodel. For each worksheet you want to import, you must:
Specify whether rows represent components or references
Choose the workspace(s) to import to
Map the columns of your Excel sheet to Ardoq column types
Component Mapping
To import your data as components in Ardoq:
1. Select “Components” in the first step of the Configure stage.
2. Next, select the workspace where to create the components. To create a new workspace, type a new name and press enter.
3. Map your data and define hierarchy.
4. Click on the “Test configured sheets” button at the bottom of the table to test your mapping. You can also configure all the worksheets you wish to import before testing your mapping.
Note: Changes in hierarchy are possible, but only when components/parent are defined by ID.
Reference Mapping
To create references:
Make sure the components you wish to create a reference for already exist in the source or target workspaces in Ardoq. Otherwise the creation of the reference(s) will fail. Alternatively, if the components do not already exist, list them in a different sheet in your Excel file and import them together with your references.
Select “References” in the first step of the Configure stage.
In the second step, select the source and target workspaces where the components you want to connect live. They can be in the same workspace.
Next, assign the column types “Source” and “Target” to the relevant columns in the table.
Lastly, specify the type of reference.
If you would like to add a display text on references, map the column as a 'field' of type 'text' and name the new field “Display text” or similar. Next, open the relevant workspace and add a value to the new field from the Grid Editor or Sidebar Menu > References > Edit reference properties.
Test And Execute Your Import
After you have defined and mapped the worksheets, you can test the configuration to review your import or check for errors.
1. Click the “Test configured sheets” button located at the bottom of each table in a worksheet to reach the Test summary page. At the top of the page, see a summary of the number of workspaces, components, references, fields and tags that will be created or imported once you execute the import.
2. Scroll down to get more detailed information about your import and any errors.
3. Address any errors and test again. You won’t be able to import your data until all errors are addressed.
4. Once your configuration is error-free, click on the “Import all” button at the top of the Test Summary page.
5. Wait for confirmation that your import was successful.
How To Save Your Configuration
You can save your configuration at any step of the import process by clicking on the “Save” or “Save as” buttons at the top right corner of the Excel importer.
Choose “Save” to save changes to an existing configuration. Select “Save as” to save your configuration as a new one.
When To Reupload Your File Or Restart Your Import
Reupload your configuration when you need to preserve your old configuration when uploading a new Excel file or the same one but slightly modified. For example, after addressing data errors in your spreadsheet.
Restart your import at any time to overwrite old configurations when uploading. This is particularly useful when doing initial imports or doing a round of testing. In this case, it might be more useful and faster to wipe out the configuration than finding and fixing mappings.
Considerations And Constraints
Mapping inconveniences
Be aware that if you add or rearrange the order of worksheets in the file, Ardoq will not be able to preserve your structure or mapping. This is a limitation we intend to address in future iterations.
In addition, the first row in your Excel sheet is considered to be a column header and will be ignored by the importer.
Supported models
The Excel uploader supports flexible models. That said, we recommend keeping the models as simple as possible, in order to ease communication and make them understandable to others.
Reserved Field Labels
Some field names are reserved in Ardoq. If you are trying to map a field using a reserved field name, you'll get an error and will have to choose a different name for the field.
Workspace permission issues
Remember that the Excel importer is subject to permissions checks. That means you cannot import into the workspace where you only have read permissions.
Importing multiple references using commas
Using commas to import several references at once is currently possible for outgoing references from a components sheet only. To create them:
Separate the components in your spreadsheet you wish to make multiple references to by using commas.
Next, upload your file and follow the steps in the importer or read step-by-step instructions in the Component Mapping section above.