With the Excel integration, you can import your data as components and references in Ardoq using .xls, .xlsx, .xslm, or .csv files.
Pre-requisites
Get familiar with the basic concepts in Ardoq
Download the Excel sample file used in this article
Video Tutorial
You can watch a short guided tutorial on importing an Excel file on our YouTube channel.
Overview
The Excel integration enables you to translate a tabular dataset such as an Excel spreadsheet to a graph representation in Ardoq. This is done by mapping your tabular data so that it fits into your Ardoq metamodel.
Mapping allows you to:
Flexibly generate Ardoq metamodels based on your Excel data
Define the workspaces that data will be imported into
Map columns in Excel to provide additional details like descriptions, tags, fields,and component types
Create references between the components defined in your worksheets
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
The Excel sample file provides an example of how your should structure your Excel spreadsheet for import into Ardoq.
Each worksheet should represent either a set of components or a set of references between components. Ardoq requires each worksheet to be configured separately.
Leaving Sheets and Columns Unmapped
Unmapped sheets and columns will be skipped during the import process and will not be imported into Ardoq.
In our sample Excel file, we have the following structure:
Components:
Applications
Business-units
Capabilities
References:
BU -> Applications
Capabilities -> Applications
Note: The -> symbol is a short-hand to symbolize a reference between components. It has no functional impact on the importing or mapping process.
Workspaces
Workspaces define the repositories where components will live. Each worksheet can have its components imported into a unique workspace or you can use a single workspace to store all your components.
You'll have the option to create or re-use workspaces for each worksheet in your Excel import.
References, unlike components, can connect a source and a target workspace together. This allows you to create references between components in different workspaces. You can also have the source and target workspace be the same workspace and make connections between components in the same place.
Components
Each row in a sheet — excluding the first header row — will be mapped to create a component in Ardoq.
Components are given more details by mapping each column in the sheet to a different type. For example, some common types include:
Component name: The name of the component in Ardoq.
Description: The main content providing text details about a component.
Tags: Additional information to allow you to filter your components in Ardoq
Import ID: 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. These values
Use commas to separate multiple values in Tags and Select Multi lists.
Hierarchy of Components
You can indicate a hierarchy in your import by assigning multiple Component name types to your columns.
For example, if you have a parent application column, setting that column to be of Component name type will automatically give it a hierarchy level of 1. If you have a child application column related to the parent, you can also set it to be of the Component name type, and its hierarchy level will be set to 2, indicating that it is a child component.
When creating this import, Ardoq will create two components from a single row: one component from the parent and one component from the child.
References
References express how components relate to each other and can take many forms. Some common references include:
Business Unit A owns Application B
Alice manages Bob
X licenses Y
When importing references, you need to define a source workspace and a target workspace. The source and target workspace can be the same workspace.
With that defined, you can establish the sort of relationships exist between the components in those workspaces. This is done by specifying columns as Source and Target types, as well as adding a Type column type to specify the relationship name.
You can do this in two ways:
Specify source and target components using Custom IDs defined in other worksheets.
Using a fully-qualified path using the
::notation to denote a level. For example,SaaS::Intercom.
Importing Your Data
From the main menu, navigate to Home > Import and integrations > Excel
Click on Create new import in the top-right corner
Upload your Excel or CSV file
Begin mapping your components and references in the Configure Data step
Configure Data Step
In the next steps, we'll use the sample Excel file as the basis for creating our components and references.
Navigating Tabs during Import
Every step of your configuration process is saved as you perform your mapping. Feel free to navigate between tabs if you need to recall field names or to reconfigure data elsewhere.
Mapping Components
Applications Tab
Set your worksheet to Components
Create a new workspace called New Applications
Configure the Name column as a Component name column type
Configure the Application ID column as a Custom ID
Business Units Tab
Set your worksheet to Components
Create a new workspace called New Business Units
Configure the Name column as a Component name column type
Configure the ID column as a Custom ID and set the field name to BU-ID
Mapping References
BU → Applications Tab
Set your worksheet to References
Set your Source component workspace to the previously-created New Business Units
Set your Destination component workspace to the previously-created New Applications workspace
Set the BU column to the Source column type, set the Format to Custom ID, and set the Field name to the BU-ID field we created earlier
Set the Application column to the Target column type, set the Format to Custom ID, and set the Field name to Application ID
Set the Reference type column to the Type column type
Finally, click the Next: Review import button to proceed
Review Import Step
This step will perform validation on your column mapping and warn you if there are any problems that make the data import impossible.
In our case, our mapping is correct, and we should be able to execute our data import.
Click Next: Import my data to complete importing your Excel data into Ardoq.
Workspace Visualization
You can open the workspaces you created to see your components and the references between them. The view of our example data looks like this:
Saving Your Configuration
You can save your column mapping for use later by clicking the Save configuration button in the top-right corner of the import flow. You can think of saved configurations as templates for your data.
Saved configurations are useful if you're going to be importing new data using Excel sheets with the same structure. Returning to a saved configuration will automatically set the mapping of the Excel import to your last saved mapping.
Considerations And Constraints
Excel Mapping Limitations
Be aware that if you add or rearrange the order of worksheets in your Excel file, Ardoq will not preserve your structure or mapping.
In addition, the first row in your Excel sheet is considered to be a column header and will be ignored by the importer.
Reserved Field Labels
Some field names are reserved for internal systems in Ardoq. If you are trying to map a field using a reserved field name, you'll get a warning and will have to choose a different name for the field.
Workspace Permission Issues
The Excel importer is subject to permissions checks. This means that you cannot import into workspaces 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 that 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.