Skip to main content
Excel Integration

Learn how to use the Excel integration to import .xls, .xlsx, .xslm, or .csv files and quickly add or update data in Ardoq.

Kristine Marhilevica avatar
Written by Kristine Marhilevica
Updated over 2 months ago

With the Excel integration, you can import your data as components and references in Ardoq using .xls, .xlsx, .xslm, or .csv files.

Pre-requisites

  1. Get familiar with the basic concepts in Ardoq

  2. 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:

  1. Specify source and target components using Custom IDs defined in other worksheets.

  2. Using a fully-qualified path using the :: notation to denote a level. For example, SaaS::Intercom.

Importing Your Data

  1. From the main menu, navigate to Home > Import and integrations > Excel

  2. Click on Create new import in the top-right corner

  3. Upload your Excel or CSV file

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

  1. Set your worksheet to Components

  2. Create a new workspace called New Applications

  3. Configure the Name column as a Component name column type

  4. Configure the Application ID column as a Custom ID

Business Units Tab

  1. Set your worksheet to Components

  2. Create a new workspace called New Business Units

  3. Configure the Name column as a Component name column type

  4. Configure the ID column as a Custom ID and set the field name to BU-ID

Mapping References

BU → Applications Tab

  1. Set your worksheet to References

  2. Set your Source component workspace to the previously-created New Business Units

  3. Set your Destination component workspace to the previously-created New Applications workspace

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

  5. Set the Application column to the Target column type, set the Format to Custom ID, and set the Field name to Application ID

  6. Set the Reference type column to the Type column type

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

Ardoq importing multiples via comma separation

Next, upload your file and follow the steps in the importer or read step-by-step instructions in the Component Mapping section above.

Watch The Excel Integration Demo Video

Did this answer your question?