Import Your Data via Excel

Importing your data via Excel provides a shortcut to adding or editing bulk information within your workspace. This article shows you how to import data to create, edit or delete information or archive entire Fact Sheets using the Excel Import function.

📘

For Starters: Getting Data into Your Workspace

If you are just starting off with LeanIX and want to get your initial data upload right, please see our guide on Getting Data into Your Workspace

Current Import Limitations

🚧

Note

The current version of LeanIX Excel Import has a few limitations which are important to know about to avoid errors.

1. Limited support for some data categories

Trying to import the following data categories with LeanIX Excel Import will lead to errors:

  • Comments
  • Resources
  • To-Dos
  • Milestones

2. Only one Fact Sheet Type at a time

As of now, only one Fact Sheet Type per Import is supported. Trying several Fact Sheet types will lead to errors.

3. File types

  • Only .xlsx
  • Only up to 10k rows

Step-by-Step Import Guide

In this section, you will find a detailed guide that walks you through the process of importing data. It covers best practices and formatting rules and explains the most common aspects that can lead to data import errors. The guide also provides possible solutions for resolving them.

1. Create an import template

It's a best practice to use the LeanIX Export function to download a spreadsheet in the right format containing the data (column titles, Fact Sheets, Field values) relevant to you before starting to populate your target data (step 2).

2. Enter target data in the upload file

General formatting rules

In this section, you will find a comprehensive explanation of formatting rules, providing a detailed overview of the restrictions and guidelines associated with data Import. Taking the time to familiarize yourself with these guidelines will ensure a smooth and error-free data import process.

Rows & Fact Sheet identification

Fact Sheet ID

  • In the import spreadsheet, it is essential that each row corresponds to a Fact Sheet, whether it is for creation, editing, or archival purposes.
  • Fact Sheets are identified by their id field, and this field alone is used for Fact Sheet identification.
  • If the idfield is left empty for a row, it indicates that a new Fact Sheet will be created based on the data provided in that row.
  • It is important to note that the id field is automatically generated by the system and cannot be manually set or changed by users.

Fact Sheet Type

  • In addition to the Fact Sheet id, the Fact Sheet type column is required for a successful import.
  • The type column should include the exact technical keys that correspond to the desired Fact Sheet types.
  • Additionally, the Fact Sheet type cannot be modified or changed for the existing Fact Sheets through the Excel Import feature. The import functionality is read-only for existing Fact Sheets.
  • Lastly, it's important to note that only a single Fact Sheet type can be imported at a time.

Fact Sheet Name

In addition to the Fact Sheet id and type columns, the Fact Sheet name column is also required for a successful import. It is important to enter the exact name of the Fact Sheet (including capitalization) to identify it during the import process accurately. Entering a wrong name while referring to an existent Fact Sheet (over the id) will change the Fact Sheet name, so be cautious about that.

Column titles & attribute identification

During the Excel Import in LeanIX, column titles play an important role in identifying the desired Fact Sheet Fields. It is important to use the correct LeanIX technical keys (rather than translations) in the import file to ensure the successful mapping of input data to the customer's inventory.

To facilitate this process, it is mandatory for the technical keys to be placed in line 1 of the input file. On the other hand, the translations provided in line 2 are not validated or required for a successful import.

Users can access the relevant technical keys by including the relevant column while exporting the Excel template, or in LeanIX Meta Model Configuration.

Cells & Field Values

Overwrite principle

In general, the LeanIX Excel Import follows an overwrite principle for Fact Sheet Field Values. This means that the existing Inventory data is replaced with the data from the input spreadsheet for existing Fact Sheets. When adding or deleting values in multi-select fields, relations, or tags, users must include the existing values they want to retain in the import file.

Read-Only Fields

Certain fields in LeanIX are designated as read-only and cannot be modified through the Excel Import. These fields are generated by the LeanIX platform and are also not editable in the Meta Model Configuration. The current Read-Only Fields are:

  • updatedAt
  • createdAt
  • id
  • type
  • completion
  • displayName

Including read-only fields in the import will have no effect. Their values will not be updated or modified during the import process. An exception to this is the id field - leaving it empty will create a new Fact Sheet as mentioned before.

Single- and Multi-Select Fields

For single- and multi-select fields, the exact technical keys need to be entered for a successful import. Different values need to be separated by “;” (semicolon), with no spaces before and after the semicolon.

Relations

Use the exact displayName of the target Fact Sheet to create a relation. If multiple relationships are to be created, displayNames need to be separated by “;” (semicolon), with no spaces before and after the semicolon. Please note the overwrite principle applies to relations as well.

Other

  • LeanIX supports UTF-8 character encoding for string fields in LeanIX. However, values in the fields itself can not have "<"(less than) and ";" (semicolon) characters for importing.
  • Dates need to be entered in the following format: yyyy-mm-dd
  • Numbers need to be entered in the following format:
    • Use points (“.”) for floats (e.g., 1.7 = one point seven)
    • Use commas (“.”) for integers (e.g., 1,700 = one thousand and seven hundred)
  • Use separate columns for tag groups, lifecycle phases, or subscription roles.

ReadMe Sheet in the Exported Excel File

Each exported Excel spreadsheet from LeanIX contains two sheets, one with the exported data and one labeled as ReadMe. The ReadMe sheet contains information about the type of data that is mandatory (ID - only if referencing an existing Fact Sheet; Type - the type of Fact Sheet; Name - the name of the Fact Sheet), the kind of relationships that can be defined according to the columns, and the values that can be defined for the attributes, etc.

ReadMe sheet detailing the type of information that can be defined in the Excel spreadsheet

ReadMe sheet detailing the type of information that can be defined in the Excel spreadsheet

🚧

Note

  • When preparing the file for import, please make sure you go through the Readme sheet of the exported Excel file. The Readme sheet contains important information, including forbidden characters, or what can and cannot be imported via Excel, for each of the Fact Sheet types.
  • Make sure to delete the Readme sheet before importing. The import function only reads the first sheet of the workbook.

How to create, edit, and delete data

This guide will walk you through the processes of creating, editing, and deleting data. In the creation phase, we'll cover Fact Sheet creation and data creation for various fields. Then, you will learn about data editing and deletion, which includes Fact Sheet archival and Field Value deletion.

Data creation

Fact Sheet Creation

To create new Fact Sheets, leave the id column empty in the import file. However, ensure that the name and type columns are filled. While you can provide a "displayName" for usability, it will be automatically set by the system for new Fact Sheets and is ignored during the import process.

Creation of Fields, Tag Groups, and Subscription Roles

Before setting new Fields, Tag groups, or Subscription roles via Excel Import, it is necessary to create them first in the Meta Model Configuration. These elements must already exist before the values can be successfully modified through the Excel Import process.

Field Value Creation

In general, field values can be set by entering the desired value in the respective cell during the Excel Import process. It is important to note that for single-select and multi-select values, they must be created in the Meta Model Configuration before they can be assigned via Excel Import. However, Tags are an exception to this rule, as they can be created directly through Excel Import.

Data Editing

When it comes to editing data via Excel Import in LeanIX, in general, all data can be edited as long as the General formatting rules are followed. However, there are certain exceptions to this rule, which are mentioned in the limitations section.

One notable exception is the Fact Sheet Type field. It cannot be directly edited via Excel Import. Instead, first, you need to archive the existing Fact Sheet. Then, you can create a new Fact Sheet with the desired Fact Sheet Type.

Data Deletion

Fact Sheet Archival

The Import feature can be used to archive Fact Sheets. To do so, create a new column action on the import file and enter archive in each row for the Fact Sheets that need to be archived. We recommend making sure that the Fact Sheets have an existing ID and running the import in a test mode first.

Archived Fact Sheets are moved to the trash where they can be recovered within the retention period.

Field Value Deletion

Field values can be deleted as the overwrite principle applies. Please note that deleted values cannot be retrieved.

❗️

Data Deletion Risk with Access Control Entity

In virtual workspaces, the users have access to a limited number of Fact Sheets based on their Access Control Entities (ACE). While creating a template for import, only those Fact Sheets accessible to them are included in the relations columns for any given Fact Sheet. As a result, when they import the data through that template, all other unseen relations get unlinked due to the overwrite principle in the import.

For example, if App1 is related to ITC1, ITC2, and ITC3, but the user only has access to ITC1 and ITC2, when a template is created with App1, only ITC1 and ITC2 are included in the IT components relations column. Upon re-importing, the system deletes the relation between ITC3 and App1.

Import of Fields on Relations

To import fields on relations, users must include the "displayName" column to identify or establish the target relation, along with a column for the Field on Relation they intend to update.

You can update the same Fields on Relation for multiple Relations by including several target Fact Sheets in one cell, separated by semicolons.

In the example below, we establish a relation between the Application Fact Sheet “AC Management” and the Business Capability Fact Sheets “Corporate Services” and “HR/Recruiting”. In addition, we define the “active from” date (Field on Relation) for both relations:

Example Import Fields on Relations

Example Import Fields on Relations

The easiest way to prepare an Import file is to create a template by exporting the relevant columns from the inventory. To learn more about creating import templates, see Template for Import.

Overwrite Principle while Importing Fields on Relation

When Fields on Relation columns are included in the import file, the default overwrite principle changes for the Relation columns:

  • Existing relations not mentioned in the Relation column will not be deleted.
  • Non-existing relations mentioned in the Relation column will still be added.

However, the general overwrite principle still applies to all columns not representing a Field on a Relation:

  • All changes made to regular fields must be applied to every row mentioning the Fact Sheet being edited to avoid overwriting of changes in a previous row.

Dealing with Dependencies

  • Users are required to create a Fact Sheet before they can relate to it (relations, parent-child, or required by). One can create and establish a relation within the same Import as long as the 'create' row precedes the row that defines the relation.
Right: Create New Fact Sheet before relating to it

Right: Create New Fact Sheet before relating to it

Wrong: Relating to a Fact Sheet before creating it

Wrong: Relating to a Fact Sheet before creating it

  • The relation between two Fact Sheets doesn’t need to be defined more than once. For example, for Parent and Child relations, you only need to provide the relevant information in one of the respective columns. In the example below, if the Fleet Management Business Capability has the Corporate Services Business Capability as its parent, it is enough to define this relationship in the row that pertains to the Fleet Management Business Capability. It is not required to add the child Fact Sheets in the row that defines the Corporate ServicesBusiness Capability.
Relations between Fact Sheets only defined one (from one direction)

Relations between Fact Sheets only defined one (from one direction)

  • Use the full display name and precise capitalization when creating relations to Fact Sheets during import. The import process is case-sensitive, so ensure accurate capitalization for successful mapping.
  • The display name for a Fact Sheet is composed of the name of the parent(s) separated with a “/” from the name of the Child Fact Sheet. Please keep in mind that there needs to be a space before and after the “/” separator. If you want to use the “/” in the name of a Fact Sheet that does not represent the Parent / Child relation, then you can use the “/” without a space before and after

3. Upload File

Once your file is ready, go to the Inventory and click on the Import button on the right-side panel of the screen, under the Actions category. On the pop-up that appears, you can click on Import to import the Excel spreadsheet.

Right now, LeanIX Excel Import supports .xlsx files up to 10k rows.

4. Review & Correct Errors

Errors can occur at three steps in the import process and are reported as follows:

File Upload Errors

The selected file doesn't comply with the supported file type or size and can thus not be opened.

Possible errors:

  • File format other than .xlsx
  • Files with more than 10k rows

File Format Errors

The file is not formatted according to “General formatting rules” and can thus not be parsed.

Possible errors:

  • The file is missing the required columns (id, Fact Sheet Type, name)

Inline Errors

The file could be opened and parsed, but there are invalid entries on the row or cell level.

Possible errors:

  • Trying to import unsupported data categories (e.g., Fields on Relations)
  • Usage of translations instead of technical keys for single-select or multi-select values

The Download Report button generates an Excel file that summarizes all changes made and provides crucial details about any errors encountered. The resulting file is comprehensive, offering specific information about the Fact Sheet that caused the error and the data element associated with the error.

Error when the Fact Sheet Audimex cannot be related to any existing Fact Sheets in the Inventory

Error when the Fact Sheet cannot be related to any existing Fact Sheets in the Inventory

Post-Confirmation Errors

In very rare cases, errors can occur after the confirmation button is clicked. You will be notified in the error message about the root cause.

Possible errors:

  • Invalid entries in subscription role columns

Please reach out to our support when you are not able to resolve the error by yourself.

5. Complete Import

During the data import process, the uploaded file undergoes a check before it is actually imported into the environment. This check allows the user to review and confirm the changes before proceeding with the import.

The user is presented with a summary of the proposed changes, and they can proceed with the import by clicking the Import button when satisfied. This step finalizes the import process.

Import History

Changes done via the Excel Import can currently be tracked in multiple ways:

One can download Changelog from the Administration Section but is limited to users with Admin rights.

One can view the changes that have happened in a Fact Sheet from the Last Update tab in the Fact sheet.

If the change involves archiving of Fact Sheets, then the details can be viewed by filtering for archived Fact Sheets in the Inventory.

Feedback

📘

Let us know your input!

How can we improve the Excel Import for you? Please vote on our roadmap & let us know what is most important to you.