Importing data from Excel


Preparation: Matching excel columns with the Matrix items fields

As the final goal is to create some Matrix items based on the excel content, the first thing to do is to create the right structure of your category which will be filled in.

Corresponding fields need to be created to make sure that each column needed for the import has a target field for the import. It can be any field such as: text field, check box, dropdown,... More info here: Input fields.

Step 1: Enable feature

In order to import data from Excel, you first have to enable this feature in the admin client:

Project Settings → Advanced features:

Step 2: Prepare Excel file

Each row of the excel will be imported as one item.

The different columns will be mapped to the different fields (e.g. description)

Make sure Excel is a basic excel file with no merged cells, macros, fonts (even hidden ones) etc.

Step 3: Importing the items

When your Excel file is ready, go to the folder in which you want to import your data and click on "Import Excel File".

Then either drag-and-drop or select the file you wish to import.

Next, select which worksheet in the selected Excel you would like to import

Once you selected the worksheet, the data in this worksheet will be converted to a table which allows you to select which rows need to be converted into items. Note that you don't want to include the ones with a title, but only the ones with actual content.

Afterwards, you can assign a field, that is present in the item type to which this data will be converted, to a column. For example, for a REQ item, this can be a title or a description. For a RISK it can be a cause, effect, probability, severity, etc. 

When clicking on next, the selected rows will be converted into items and the content of the assigned columns will be added to the selected fields.

Filling checkboxes in Matrix

If you have checkboxes in Matrix, the Excel need to contain the words true or false to fill in checked or unchecked data in Matrix

Handling Folders

If you want to create folders while importing items you have two possibilities:

  • creating a flat list of folders with a couple of items inside
  • creating a more complex hierarchy of folders

Flat folder lists

To create the below with two foldersyou need to prepare you Excel like this

Add an extra column for the folder name

Create an extra line for each folder

Once done you need to map the columns as follows: map the column with the folder names to FOLDER

Items in hierarchy

To create the below with two foldersyou need to prepare you Excel like this

Create a extra column for the hierarchy

Enter a | separated list of folders for each item

Once done you need to map the columns as follows: map the column with the folder names to HIERARCHY

Handling Test Cases

The best for test cases is to have an excel where each tab is one test case with the rows containing the test steps,see here: Importing tests from Excel

If your excel has one line per test case, the test case steps (from the test table) need to be in a specific format:

  • for each test step:  step |  action | expected result (step is a the row number, starting with 1, afterwards separated by | are the other columns of the test table). 
  • between each row, use a |# as a separator

Here an example of one cell in excel with a complete test table