10 tips to import information from Excel into Relatics

In the early phase of a project, a lot of information is often already available in various Excel files containing for example requirements or standard risks. A quick start can be made by importing this information in Relatics. Although importing information into Relatics is faster and less prone to error than entering it manually, it also presents its own challenges. The source file may be incompatible or contain information that is inconsistent for Relatics. How can you best deal with these challenges? And how can you be sure that the result is correct?
This article describes 10 tips that will help you to accurately import information in Relatics. 

Tip 1: Copy the information contained in an Excel file to a new Excel file

Sometimes, an Excel file is incompatible with a Relatics import. In such situations, nothing happens after the import. No information is added or changed. In order to prevent this problem, we advise always copying the information and requirements in the original Excel file and pasting it into a new Excel file. It is recommended to use the option “Paste special … Values”. This ensures that you only copy the information, ignoring such things as formatting and formulas.


Tip 2: Split separate information in 1 cell into different rows

In Excel, information is often managed in a single cell. For example, a cell may contain several issues separated using commas or enters.

The problem is that the Relatics import cannot distinguish between the different issues. The solution is to duplicate the entire row of requirement data and split separate issues across the rows.



Tip 3: Change dates to the right format

Dates are often a challenge when importing. When you import a date (e.g. a deadline for an issue relating to a requirement), you can end up with a strange sequence of numbers in Relatics (for example “43003”). This is because Excel saves every date in the background using its own format. In order to solve this issue, you need to first use formatting or formulas in Excel to change each date to the right format (yyyy-mm-dd). Next, make a new column that you format as “Text”. The last step is to paste the values into the new column using “Paste special … Values”. With this result, you can import each date correctly into Relatics.


Tip 4: Check whether the columns in the Excel file correspond to those in the import definition

You may sometimes find that information in a certain column of the Excel file with requirements has not been imported. The cause often lies in the mapping of the Excel files onto the source columns in the import definition. For instance, you forgot to map a column, or the names of the columns do not completely match. It is advisable to check all column names one last time before importing. Make especially sure on both ends that everything is complete and there are no leading or trailing spaces in the column names. Finally, check whether every source column is actually used in an import node.


Tip 5: First test the import in a clone workspace, only then in the live workspace of a project

It is always possible for information in the Excel file with requirements to be imported differently than you expected. When that happens, it is very annoying and time-consuming to have to correct all the information manually in Relatics. In order to prevent this, it is recommended to always test the import in a clone workspace first.

Give the clone workspace a clear name (e.g. “Test Import – date”) and remove all users in order to prevent confusion among project staff. After the owner of the Excel file has approved the import results, the file can be imported into the live workspace.


Tip 6: Check data in the Excel file that must not be copied in Relatics

The Excel file may contain information that does not match Relatics. For instance, the Excel file may contain a requirement type called External Interfaces, while Relatics uses the name External Interface. Before you import, it is a good idea to scan the Excel file for such inconsistencies and correct them. If Excel refers to information that does not textually exist in Relatics, this will jam the import. If that happens, you will be given a clear signal of inconsistencies in the data that you hadn’t noticed yourself.


Tip 7: Check the import log for comments and warnings

After a successful import, it is advisable to check the import log for errors. These are often caused by inconsistencies in the Excel file’s data or mistakes in the configuration of the import definition. After downloading the import log, it is best to filter the status column by comment or warning.  

In the above screenshot, some rows in the excel file have no issues. These reports help determine whether the Excel file is complete or if the decision was made to purposely not create any issues for the requirements concerned.


Tip 8: Compare the number of requirements in the source file to the number in Relatics

An effective test is to count the number of requirements in the Excel file and compare it to the number of requirements imported into Relatics. Count the number of requirements in Excel in advance. If requirements have been split across multiple rows, create a copy of the Excel file and use the “Remove Duplicates” function  to reduce the information to the unique set of requirements. In Relatics, you can find the total number on the detail page of requirements in the model in Workspace Studio. Before you start importing, check whether there are any requirements in Relatics already, so that you can calculate the number to expect after the import.


Tip 9: Perform random checks on the imported data based on the Excel file

We strongly recommend carrying out a number of random checks after importing to verify whether the requirements have been properly imported into Relatics. Check whether the requirement and all metadata in the first row of the Excel file correspond exactly with the information in Relatics. Do the same with the last line to make sure that the import has processed all rows. Scan through the list of requirements and the lists of related metadata to see if anything stands out, such as duplicates or inconsistencies.

Tip 10: Use Excel templates for imports to prevent errors

If you need to use imports once at the start of each project, it is advisable to not only provide import definitions, but also Excel templates. These are files in which you enter all the column headers used by the import definition. It is also advisable to include some example data, so that an end user knows how to fill out the Excel file. For end users, it is helpful if you include these Excel files and import definitions on a navigation page.


Final result

It pays off to carefully prepare and test the import of an Excel file containing requirements into Relatics. This prevents errors in the Excel file, errors in the mapping to Relatics, and that information is imported differently than expected. The final result is an accurate import of requirements in Relatics.