Getting your Excel data into Relatics

Exchanging data via Excel files is very popular given its flexibility, and during your project you will most likely obtain data from an external source (e.g. an external application or another party) in an Excel file. All the separate files make for an entangled web of data, where it’s hard to get a good overview of the project, this is where Relatics comes in. To gain full insight into the complexity of the project, the data from the Excel file(s) needs to be transformed and made available in the Relatics workspace. This provides a central location where project data is managed, where project members can view and edit the data extensively. This article provides tools for entering data from an Excel file into Relatics.

Analyze the Excel file

As a first step, you need to analyze the data inside the Excel file. Does the data fit the scope of the project? To what extent is the data already part of the Relatics workspace? Is the data in the Excel file up to date? The answers to these questions help you to validate if the data is suitable to be made part of the Relatics workspace.

After you have validated the data, it is time to check the underlying model of the data in the Excel file. You must find out what the main Systems Engineering (SE) model objects are (e.g. requirements, system objects, activities, verifications, interfaces, etc.). Further, you must understand how the SE model objects are interrelated. Once you have a clear picture of the underlying model, you must map it to the SE model of the Relatics workspace. If the models do not align and you want to make the data available in the Relatics workspace, you must first update the SE model of the Relatics workspace to be able to map the data.

Finally, you need to analyze how the data is structured in the Excel file. You need to optimize the structure of the Excel file if needed. Things you should at least check in the Excel file:

  • Each sheet in the Excel file must present data on a primary SE model object. For example, in a sheet that primarily contains requirements, each row should present data for a single requirement.
  • Each value in the Excel file which can be mapped to a property from Relatics, should be part of a separate cell in the Excel file. For example, for the property ‘Status’ of a requirement, each cell should only contain a single value.

Example of well-structured data in an Excel file that is optimized for copying and pasting into Relatics:

Align the data of the Excel file with the SE model from the Relatics workspace

After you have analyzed the data inside the Excel file, you need to make sure that the quality of the data matches the criteria to be pasted and stored in Relatics. For example, properties in Relatics are assigned a datatype. The datatype has implications for the allowed values and the number of characters that can be stored in Relatics. Further, data from the Excel file needs to be formatted in such a way that it can be pasted into the Relatics workspace.

Properties

Datatype Explanation
Single line text

What is supported?

  • Supports both alphanumerical and numerical values.
  • Supports a maximum of 100 characters.
  • Does not support new lines. 

Tip:

  • In the Excel file, use a formula (e.g. ‘LEN’) to check if values exist that exceed 100 characters.
Text

What is supported?

  • Supports both alphanumerical and numerical values.
  • Supports the following formatting: bold, italic, and underline.
  • Supports bulleted lists and numbered lists.
  • Supports a maximum of 10.000 characters.
  • Supports new lines.

Tip:

  • In the Excel file, use a formula (e.g. ‘LEN’) to check if values exist that exceed 10.000 characters.
List

What is supported?

  • Supports a list of textual values as preconfigured by the power user. No other values are expected than those available in the list.

Tip:

  • Download the available property list values from the Relatics workspace and paste the values into a separate sheet in the Excel file. In the Excel sheet that contains the data, use formulas (e.g. ‘VLOOKUP’ or ‘COUNTIF’) to check if all values correctly match a value from the Relatics property list.
Date

What is supported?

  • Supports a date value that matches the date format as set for the workspace (e.g. ‘yyyy-mm-dd’, ‘dd-mm-yyyy’ and ‘mm-dd-yyyy’).

Tip:

  • If the cell is formatted as ‘Date’, which Excel does automatically once it recognizes a date value, in the background the value is converted into a serial number that is based on the 1900 Date System. This results in a problem when pasting the date values into Relatics as the serial numbers are not recognized as dates. Therefore, in Microsoft Excel, it is advised to first make sure the date values have the correct date format (e.g. ‘yyyy-mm-dd’, ‘dd-mm-yyyy’ and ‘mm-dd-yyyy’). Second, it is advised to paste the values into a column that is preformatted as ‘Text’. This ensures that the values that you see in Excel match one-on-one with the values you paste in Relatics.
Number

What is supported?

  • Supports a numerical value. Based on the configured precision, it has a predefined number of decimal places.
  • Supports a dot (‘.’) as the decimal separator.

Tip:

Note:

  • In Relatics, it is not supported to use Microsoft Excel to paste a value into a calculated property. As a calculated property is the result of a formula based on properties with the datatype ‘Number’, only values can be pasted into properties that are used in the formula.
URL

What is supported?

  • Supports valid hyperlinks.
Attachment

What is supported?

  • Supports the manual upload of a file.

Note:

  • In Relatics, it is not supported to use Microsoft Excel to upload files. This concerns a manual process.

Relations

Datatype Explanation
Standard relation

What is supported?

  • Supports a value that refers to a property of a to-be-related element in Relatics. Example: Pasting the value ‘Bridge’ for the property ‘Name’ of an element ‘System Object’ to be related to a requirement.
  • Supports only values for properties in Relatics that have one of the following datatypes: ‘Single line text’ and ‘Text’.
  • Supports only values that are unique for that property for all the elements of the corresponding element type. If multiple elements are found with the same value, then it is unclear to which element the relation should be created.
  • Supports semicolon-separated values from the Excel file in case of a multi relation in Relatics. Example: Pasting the value ‘Bridge; Pavement; Asphalt’ for a multi relation between the element ‘Requirement’ and the element ‘System Object’.

Note:

  • When pasting data from an Excel file to Relatics for a related element, please make sure that in Relatics only one property of the related element is shown in the table. Otherwise, pasting is not allowed.

Tip:

  • Typically, external applications provide data regarding multi relations in redundant rows in an Excel file. In that case, the redundant rows must be transformed to semicolon-separated values before the data can be pasted into Relatics. One way to do that in Excel is to use a PivotTable including a measure that concatenates the unique values from multiple rows. Example of a measure in a PivotTable to concatenate unique values:


    As a result, the values of the multi relations will be transformed into a semicolon-separated value (see the values in the column ‘Objects’ below). These values can be copied and pasted into Relatics.

Collection relation

What is supported?

  • Everything that is supported by the standard relation. Additionally, it supports only values that are unique for the presented property for all the elements (of different element types) that are part of the collection relation. If the same value is found in elements of different element types, then it is unclear to which element the relation should be created.

Copy and paste the data from the Excel file into the Relatics workspace

After you have ensured that the quality of the data from the Excel file matches the criteria to be pasted and stored in Relatics, you can perform the action of copying and pasting the data from the Excel file to Relatics. It is advised to use the following steps:

Step 1. For each type element in Relatics, paste the list of elements from the Excel file into Relatics including all the properties.

  • Use a my view or a use case. For each column in the Excel file that you have mapped to the SE model in Relatics, use the model selector to select the properties for the my view or the use case. The result is a table that shows all the properties for the corresponding type element for which you want to paste the data from the Excel file.
  • Rearrange the order of the columns in the Excel file to match the order of the columns in the Relatics table.
  • Copy the data from the Excel file and paste the data into the Relatics table.
  • Repeat these steps until all the elements and properties are stored in Relatics.

Example of pasting elements into Relatics:

Step 2 For each type relation in Relatics, paste the list of relations from the Excel file into Relatics including all the relations.

The step of pasting relations into Relatics is similar to the step of pasting elements with properties into Relatics (see step 1). It is important to perform the step of pasting relations separately at the end, as only values for relations can be pasted into Relatics, of which the elements already exist in Relatics.

Example of pasting relations into Relatics:

Check the result

After you have pasted the data from the Excel file into the Relatics workspace, you must check the quality of the pasted data. Does the Relatics workspace contain all the data from the Excel file? It is wise to perform the following checks:

  • For each type element and for each type relation in the Relatics workspace, look at the number of elements and compare it to the total number of unique values of the corresponding SE model object in the Excel file.
  • Perform random testing of property values in the Relatics workspace against the Excel file to check whether the data is complete.

Relatics is the leading Model-Based Systems Engineering software application for construction projects. It is the comprehensive tool that gives professionals access to all project information and offers insight into the growing number of dependencies between all disciplines in today’s projects.

This article is a common practice and is the result of years of experience gained by our consultants in the field. Should you wish to implement this knowledge in your own projects and require assistance, please do not hesitate to contact us. Our consultants are ready to provide you with the support you need.

In this article
Please enable JavaScript in your browser to complete this form.

Download the whitepaper

Please enable JavaScript in your browser to complete this form.

Download the whitepaper

Request a demo

Fill in the below information and we will contact you to schedule a demo.

Please enable JavaScript in your browser to complete this form.
We will use this information to contact you.
Please enable JavaScript in your browser to complete this form.
Please enable JavaScript in your browser to complete this form.
Addresss

Get in touch

Fill in our form and one of the Relatics members will contact you as soon as possible. Do you prefer contact by phone? Please call us at +31 180 413 047.
Please enable JavaScript in your browser to complete this form.