Introduction to Importing Data from Excel/CSV files

This article sums up information on how to import data from Excel or CSV files into Soley Studio data graphs. After a general introduction, the article the available options for data import from Excel and CSV tables or lists.

General introduction to creating graph elements (nodes and edges)

To create a data model (graph) with Soley Studio you first need to create a metamodel. After that, instances (elements) of the node and edge classes defined in the metamodel can be created in three different ways:

  1. By importing data from files or databases.
  2. By manual modeling using the Modeling Elements pad.
  3. By creating elements using rules and sequences.

In Soley Studio you have three options to import data from Excel/CSV files. You can use …

  • the Data Center, which allows a simplified import of data from .csv, .xlsx and .slg file types or from various databases without writing a line of code.
  • Import functions for a code-based, and thus automatic and repeatable, import of data. Soley Studio provides ready-made data import library elements for this.
  • (DEPRECATED) the interactive Data Import Dialog (also called Import Wizard), that is also available in Soley Desk. The data import dialog in Soley Studio is deprecated, thus, we recommend using the Data Center.

Data Center

The Data Center is an intuitive, visual and manually repeatable way to import data from one or many files. The basic process of the Data Center import is divided into the following three steps:

  1. Define a data source, i.e. a file or database that you want to import from
  2. Define one or many import steps, i.e. a mapping of the data to a node class and selecting its attributes
Define the import area and attributes in a new import step

3. Import the data directly or define an import sequence to run several import steps in one go.

Import functions (Library Elements)

Soley Studio offers a set of predefined functions for the code-based import of data from lists or databases. Importing data based on code makes the process highly repeatable and allows for full flexibility. The import functions can be found in the libraries pad, as shown below. By default, the libraries pad is located on the left of the interface. If the Libraries pad is not shown right away, you can find it in the top menu under “Tools>>Extra>>Libraries”.

Libraries Pad with the Data category unfolded

In the following subsections, an overview of the import library elements is shown. Click on the links to go to detailed articles describing how to use the selected library function.

Call imports created with the Data Center

The Data Center allows for an interactive creation of a data imports. However, the user needs to start the import manually. To start a data import created with the Data Center code-based, and thus automatically, users can use the library elements Data_import or Data_Excel_import.

Run an import step (*.slis) defined in the Data Center on the data source defined in the import step using Data_import. The import is being carried out exactly as described in the Data Center. The path to the *.slis file has to be provided as input argument.

Run an import step (*.slis) defined in the Data Center on a different data source than defined in the import step using Data_Excel_import. The import is carried out exactly as defined in the import step but the data source can be changed to a different Excel or CSV file. This only works for Excel/CSV and the files need to have the same structure as the original data source for the attribute mapping to work.

Import Nodes from a Table

Importing nodes from files or databases is the most common scenario of data import. By default, each row of a table is imported as one node. To import a table as nodes, the importer has to know:

  1. the filename and path and, if it is an Excel file, the sheet name
  2. the name of the node class (needs to be defined in the metamodel),
  3. (optionally) which column is mapped to which node attribute.

Import a list of nodes of one class with user-defined attribute mapping using Data_importNodesFromTable. Each row of the table defines one element. The node class and the attribute mapping are defined as input arguments meaning that all imported nodes will be of the same class.

Import a transposed list of nodes of one class with user-defined attribute mapping using Data_importNodesFromTableTransposed. Each column of the table defines one element. The node class and the attribute mapping are defined as input arguments meaning that all imported nodes will be of the same class.

Import Edges

You can import edges from files or databases structured as (adjacency) matrix or table/list. Edges always need a source and a target node (which can be the same node). Thus, edges can only be created if nodes already exist in the graph. To import edges, the importer has to know:

  1. a class name of a source node,
  2. an identifying attribute name of a source node,
  3. an identifying attribute value of a source node,
  4. a class name of a target node,
  5. an identifying attribute name of a target node,
  6. an identifying attribute value of a target node,
  7. (optionally) if any values have to be mapped to edge attribute values

The arguments 1-6 are called source/target identifiers and MUST be provided in a specified form. This form differs for an import of tables or matrices, as described in the following.

Import Edges from a matrix

When importing edges from a matrix each matrix cell containing an entry is imported as one edge connecting a source node (row) to a target node (column). The source/target identifiers MUST be provided in the matrix as header column and header row as shown below.

Example of an adjacency matrix that should be imported as edges connecting the rows to the columns
Mandatory parts of an adjacency matrix that should be imported as edges

Note that the edges are always built from a row element to a column element. So in the above example, edges are built from the Product to the Part. If the matrix to be imported represents undirected edges this has no effect. However, if the matrix represents directed edges from the column elements to the row elements then the matrix needs to be transposed before being imported.

Import edges from a matrix with attribute mapping using Data_importEdgesFromMatrix. The values in the connecting matrix cells can be imported as edge attribute values.

Import Edges from a table or list

Importing edges from a table is rather uncommon because the required data is often not present in the required form. Instead of spending time on creating fitting tables it is normally quicker to create the edges with graph transformation rules. When importing edges from a table each row is imported as one edge. The source/target identifiers MUST be provided in the table in the exact order shown below.

Example of a table that should be imported as edges connecting source nodes to target nodes
Mandatory and optional parts of a table that should be imported as edges

The table above shows an example of a table of edge import data. The red area marks the mandatory information which is necessary for every import sequence. Also, the order of the columns in the red area is mandatory. The blue area marks optional information. Both the edge class name and the mapping of the columns to the attributes can be provided as an input argument.

Import a list of edges of one class with fixed source/target node identifiers and user-defined attribute mapping using Data_importEdgesFromTable. The edge class, the source and target node class, the source and target node identifying attributes and the attribute mapping are defined as input arguments. This means that all edges are of the same class, all identifying attributes of the source and target nodes are of the same class and the header row does not need to contain the exact attribute names.

(Outdated) Data Import Dialog – Import Wizard

 This import method works almost the same way as the Data Center. In fact, it is actually outdated since the Data Center offers more features than the Import Wizard. In any case, it will still be available in Soley Studio for a transition time so an overview of how it works is given.
In case Nodes are imported:
  1. Selection of the data file and worksheet (if Excel file)
  2. Select Nodes (Product, Part, Family…)
  3. Map columns to attributes
  4. Define the import data area

If the data to be imported are Edges:

  1. Selection of the data file and worksheet (if Excel file)
  2. Select Edges (Contains, IsFamilyOf, IsSonOf…)
  3. Define source and target node class and identifying property
  4. Map the columns for the Source Identifier and the Target Identifier.

 

Was this article helpful?

Related Articles