Import & Export your Data via Excel
Importing and exporting your data often provides a shortcut to adding or editing bulk information within your workspace. This article shows you how to import and export data to create, edit or delete information or archive entire Fact Sheets using the Excel Export/Import function.
It is good practice to start with an Export to have the correct header information. The id, type and name columns need to be there in any case. For all three actions, you have a test mode available which won't change any data but gives you feedback on your input file. Further, as indicated in the screenshot, the opportunity to download the import result exists.
The XLS export allows selecting the columns you want to export explicitly. In the Inventory, use the Button on the top right to switch to the table view.
Select Columns: The column selector on the right-hand side now lets you choose exactly what columns to display.
Export: Once you are satisfied with the columns, you can click "Export" to begin the export process.
Creating Excel File: Click the icon "Export" to export the table view to Excel File.
Download: Once the exporting finish, you can click "Download" to get the exported file into your computer.
Open Excel File: Click the downloaded file and you will see your Table view in Excel.
Currently, Export of fields on relations supports one relation (with multiple fields) to a Fact Sheet at a time. In the Table view, you can choose only one relation to be expanded.
Previous history of exports of all users are available for admins in the Export section of the Administration area.
Important: Read the Readme!
When preparing the file for import, please make sure you go through the Readme tab of the exported Excel file. The Readme tab contains important information, including forbidden characters, or what can and cannot be imported via Excel, for each of the Fact Sheet types.
Once your file is ready, go to the Inventory and choose "Import" on the top right. A dialogue will open to prompt the following actions:
- Update: If the ID of a Fact Sheet is given, all changes in the Excel File are treated as updates
- Create: In case no ID is given, LeanIX will create a new Fact Sheet
- Archive: In case a column action is given with a string "archive" (see below), LeanIX will archive the Fact Sheet
If there is a single error in the imported data, no data is written at all, even for rows where there is no error. Fact Sheet names are case sensitive. Make sure to correct all errors to successfully execute an import.
Not every aspect of an import file is checked when using the "test" run, especially subscription columns are not fully checked which could result in a clear test run but a fail in the actual run.
If this occurs, please check the respective results document for more detailed information.
Create a new Fact Sheet
Archive a Fact Sheet
Only the first Fact Sheet will be archived - all other Fact Sheets will be updated (if there is any change).
Excel Data Import Guide
In this guide, the most common aspects that can cause a data import error are explained. Additionally, common errors that might occur during data import are explained, together with a possible solution. Below is an overview of all the topics covered in this guide.
Import template can be generated from the Inventory
Whether you have data in your LeanIX workspace or you are just starting out, having an Excel template that can be used to import data can always be obtained from the Inventory. To get the template for the import, in the Inventory, switch the setting for viewing the data from As List to As Table (top right side of the screen, next to Sort by: Alphabetically):
After switching to the As Table view, the option to select which attributes and relations (displayed as Select columns) should be shown (top right corner of the screen with an Eye icon).
The available columns to select are based on the type of Fact Sheet that you used as a filter. If no Fact Sheets are used as a filter, then the columns available are based on the attributes that are common to all Fact Sheets. If for example, the Application Fact Sheet is selected, attributes such as Business Criticality, Functional Fit, Business Capabilities, Data Objects, etc. are available.
Once you select the columns you would like to use for importing or editing data, you can click on the Export button on the right side of the screen, under the Actions category. On the pop-up that appears you can click on Export to generate the Excel spreadsheet.
If there is already data in the Inventory for Applications, the selected columns will be partially or fully filled and the ID column will have the ID internally generated by LeanIX. Any data in the columns can be edited and once imported back to LeanIX, it will be updated.
In case there is no existing data in LeanIX, the exported Excel spreadsheet will only contain the names of the columns, as selected in LeanIX. For the rest, all of the rows starting from row 3, will be empty.
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 (e.g.: Business Criticality possible values: administrativeService, businessOperational, businessCritical, missionCritical).
Each row of the Excel spreadsheet needs to have a unique entry for a Fact Sheet
Since each row of the Excel spreadsheet is intended to create a new Fact Sheet in the Inventory of LeanIX, this needs to be unique. The only exception to this rule is when a Fact Sheet is immediately connected to another Parent Fact Sheet.
A Fact Sheet needs to exist in the Inventory before you can relate to it
When you are relating to Fact Sheets in the Excel spreadsheet, you need to make sure that they already exist in the Inventory. What this means, is that the order of importing data into the Inventory is important. For example, if you import first all of your Business Capabilities, when you create your spreadsheet for the Applications, you can already relate them to the existing Business Capabilities. However, if you choose to import your Applications first, you can create the relation to Business Capabilities, when you are importing the Business Capabilities.
Similarly, for Parent and Child relations the order in which the Fact Sheets are defined matters. The main reason for this is that you cannot relate a Fact Sheet to a Parent before the Parent Fact Sheet has been defined. For example, the Innovation Business Capability needs to be defined before the Idea Management Business Capability can have the Parent relation to the Innovation Business Capability.
The relation between two Fact Sheets doesn’t need to be defined more than once
While it is possible to define a relation between two Fact Sheets in the spreadsheets focusing on each Fact Sheet type, this is not necessary. For example, to define the relation between a list of Applications and the Business Capabilities they support, it is sufficient to define this relation only in the spreadsheet where the Applications are defined.
Similarly, for Parent and Child relations, you only need to choose to fill out the information in one of these columns. For example, for the Fleet Management Business Capability, it is sufficient to define that the Parent is the Corporate Services Business Capability. We don’t need to add in the row that defines the Corporate Services Business Capability which Fact Sheets are the Children.
Capitalization of the Fact Sheet names matters when relating to an existing Fact Sheet
When creating a relation to a Fact Sheet, you need to make sure to use the exact capitalization that you used when you created the Fact Sheet, as the import is case sensitive. For example, if you created an Application Fact Sheet called audimex, and when you relate it to a Business Capability you type it as Audimex, you will receive an error saying that the Fact Sheet Audimex could not be found.
The full display name of a Fact Sheet needs to be used when relating it to another Fact Sheet
When relating two Fact Sheets, it is necessary to use the full display name of the Fact Sheet you are relating. The main reason for this is, as shown in the first example, that you can have two Fact Sheets with the same name, but different display names (based on which Parent Fact Sheet they are related to). If you were to only refer to the name of the Fact Sheet, it would not be possible to do an automatic match to the Fact Sheet if there was more than one possibility.
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. In case a Fact Sheet doesn’t have any Parents, the Display name is only the name of the Fact Sheet. An easy way to get the Display name of a Fact Sheet is to export from the Inventory the list of Fact Sheets of the desired type and then copy&paste the Display names to the spreadsheet where you would like to define their relation to another Fact Sheet.
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 (see the example of audimex Application and its Business Capability.
The delimiter “;” should be used to relate multiple Fact Sheets to one Fact Sheet\
Continuing on the previously mentioned rule, if you want to connect multiple Fact Sheets to one Fact Sheet, you always need to use the delimiter “;” and include all the related Fact Sheets in the same cell. After each use of the “;” delimiter, there should be no space before the start of the name of the next Fact Sheet.
Similarly, for attributes and tags of the type MULTIPLE_SELECT, the “;” delimiter should be used to separate the different values, without spaces before and after the delimiter.
How to find the errors in the data Import
When importing data into LeanIX it is important to always do a Test Run first. This option is enabled by default and should only be disabled after a successful first Test Run of the data. In case there are any errors with the imported data, the results of the Test Run detail how many changes were successful (green), how many rows didn’t have any changes compared to the data already available in the Inventory (blue), how many errors were detected (red), and how many rows were not analyzed yet (yellow).
The Download Result button generates an Excel file that details the error that has occurred during the Test Run. The resulting file is relatively detailed providing information about the specific Fact Sheet that created the error and which data the error is related to.
Common errors when uploading data and their possible causes
- Fact Sheet not found
- Usually occurs when there is an issue with the Fact Sheet that you want to relate to
- The name of the Fact Sheet is misspelled or using different capitalization than what you defined in the spreadsheet
- Multiple Fact Sheets are related and the “;” delimiter and has an extra space before or after
- The Fact Sheet you are trying to relate to does not exist in the Inventory yet
- For Parent/Child relations, you are not using the right order to define the Fact Sheets
- You are not using the Display name for the Fact Sheet you are trying to relate to or there is a space extra or a space missing before or after the “/” delimiter
- The combination of display name and Fact Sheet type must be unique in the workspace
- The name of the Fact Sheet you are defining is not unique in the spreadsheet or the Inventory
- If you are trying to create Fact Sheets with the same name but different Parents, then you didn’t connect the previously defined Fact Sheet to a Parent or the currently defined Fact Sheet doesn’t have a Parent relation defined
- Another version of this error can occur when the relation defined is not unique (e.g..: FS_VALIDATION_RELATION_NOT_UNIQUE_OUTGOING/INCOMING). This means that somewhere in the spreadsheet the same relation is already defined.
You can download the templates for the most commonly used columns for Excel Data Import here.
Issues during import
In very few cases the import of data into LeanIX might be slowed down or not possible. In this case please check whether an SSL inspection is activated in your organization's proxy. When (temporarily) disabling the proxy inspection for the importing process, the import runs in a timely manner again.
Errors can occur when an existing Fact Sheet is updated manually in the tool while an upload via Excel containing the same Fact Sheet is happening at the same time (causing a "deadlock").
Solution: Try to upload the Excel file at a time where little traffic is happening in the workspace.
Common caveats and how to address them
Make sure, before you import Roles, that they are existing in LeanIX before.
Only an existing Role, can be imported. If a role is not existing before, you will get the Error:
CREATE Not found: Subscription role XXX could not be found.
In this case, please contact our Admin, so that he creates this role first. Then, start importing again.
Hierarchy cannot be imported: Check if the level of hierarchies is set appropriately (go to Administration - Configuration and edit the Fact Sheet Settings for your Fact Sheet)
Parent cannot be found. When creating hierarchies, you have two options:
- Use separate upload steps per hierarchy level, e.g. one XLS for Level 1, one XLS for Level 2 and so on. By this, you can always use the Test Mode to check the correctness before importing the data
- In case you feel confident, sort the rows by hierarchy level in the same XLS.
Relation cannot be imported
- Check that you use the correct display name of the referenced Fact Sheet
- In case of 1:n or n:m relations, check that the display names are separated by ";"
Tags are not imported
- For Tag Groups, there are separate columns, with one column "Other Tags" for the remaining tags. Make sure that all Tag Group columns are contained in the import, as the results won't be consistent otherwise
- If Tags of a certain tag group are not imported, make sure that the tag group already exists (Administration - Tagging)
Lifecycles are not imported
- For Lifecycles, there are separate columns as well. Make sure that all Lifecycle columns are contained in the import.
We made lifecycle handling more strict in Pathfinder. Two phases cannot start at the same day. Please make sure your import file reflects this.
- Responsibilities are not imported
- For Responsibilies, there are separate columns as well. Make sure that all Responsibilities columns are contained in the import, as an error will occur otherwise.
Mass archival of Data / Archive Fact Sheets
The import can also be used to archive Fact Sheets. To do so, create an import XLS and add a column “Action” on the right next to the other data of the import file. Next, put an “archive” in each row for the Fact Sheets that shall be archived.
We recommend making sure that the Fact Sheets have an existing ID and run the import in a test mode first. That will give you an indication of the number of Fact Sheets that will be archived.
Best Practice Imports
LeanIX offers Best Practice Posters to help you quickly create various types of Fact Sheets. Use the Best Practice Posters for Business Capabilities and Technology Stack to quickly enable the Applications Landscape and IT Component Landscape views.
Best Practice Posters include:
- Best Practices to Define Business Capability Maps
- Best Practices to Define Energy Business Business Capability Maps
- Best Practices to Define Technology Stacks
- Best Practices to Define Data Objects
You can get and download a pre-filled Excel spreadsheet that includes the Business Capabilities from the poster above here.
Updated 8 months ago