Library Element Data_importNodesFromTableTransposed

This article provides information for the code based import of nodes from transposed Excel or CSV files. If each column of your data table should be imported as a node and each row represents a property value of the node that should be mapped to an attribute defined in the metamodel, the import is done using the Data_importNodesFromTableTransposed library function.

The function allows for the automatic creation of nodes of one class with a user-defined mapping of property values. A general introduction to data import library functions can be found here.

Data structure

Use the library function Data_importNodesFromTableTransposed to import a list of nodes of one class if your import data table is structured like this:

  • all nodes to be imported are of the same class (=”typed”, defined as a rule argument),
  • the rows of the data area contain attribute values of the nodes, but the attribute names in the first column do not exactly match the metamodel: values of these rows can be manually mapped to any node attributes, meaning the user can define the attribute mapping in the rule arguments.
Unique Identifier 10000 10001 10002
Name City Move Man City Move Woman Cross Man
Sales Volume 500 700 400
Sales Price 875,99 899,99 999,99

Exemplary data table containing attribute values, every column becomes a node of the same class defined in the rule arguments, the attribute names in the first column do not match the metamodel and must be mapped in the rule arguments

Library function scheme

When adding the library function Data_importNodesFromTableTransposed from the Libraries pad to a rule sheet (*.grg file) the created code looks as follows. To execute the function it needs to be included in a sequence as explained later.

  Data_importNodesFromTableTransposed( FILE_NAME, FIRST_ROW, FIRST_COLUMN, LAST_ROW, LAST_COLUMN, TABLE_NAME, SEPARATOR, NODE_CLASS, ATTR_MAP )

Arguments:

  • FILE_NAME – string type argument defining the (relative) path to the Excel or CSV file.
    FILE_NAME path can be relative to the project’s path. If you added your data file DataFile.xlsx to the project folder Data the FILE_NAME will be “..\\..\\Data\\DataFile.xlsx”.
  • FIRST_ROW, FIRST_COLUMN, LAST_ROW, LAST_COLUMN – int type argument defining the data area of the sheet to be imported.
    Rows and columns are denoted as integers. So to import the area from cell B1 to D4 the denotation would be 1,2,4,4. The column containing the row headers MUST NOT be included in the data area! To import the complete sheet set LAST_ROW and LAST_COLUMN to -1,-1. The importer will automatically detect the last row and/or column containing data. Only one data area can be imported per import sequence.
  • TABLE_NAME – string type argument defining the exact name of the worksheet in an Excel file.
    For CSV files this must be an empty string (“”).
  • SEPARATOR – string type argument defining the seperator used in a CSV file (e.g. “;” or “|”).
    For Excel files, this must be an empty string (“”).
  • NODE_CLASS – string type argument defining the class of nodes to be created.
    The string must match a class name defined in the metamodel. All imported nodes will be of this class.
  • ATTR_MAP – map of type int->string allowing the user to define an attribute mapping.
    The mapping follows a specific scheme: 2 -> “id”, 4 -> “cost”, … meaning that the value in the second row of the selected data area is mapped to the attribute id, the value of the fourth row to the attribute cost and so on. IMPORTANT: The mapping is defined in reference to the data area. So if your imported data area starts in cell B2 the mapping 1 -> … relates to row 2, 2 -> … to row 3 and so on. Please see the example below for more details.

Calling of the library function in a sequence

Assume that the node data list to be imported looks like this, either as an Excel or as a CSV table:

A B C D E
1
2 Unique Identifier 10000 10001 10002
3 Name City Move Man City Move Woman Cross Man
4 Sales Volume 500 700 400
5 Sales Price 875,99 899,99 999,99

Here are two examples of how to call the function Data_importNodesFromTableTransposed in a sequence. The sequence names can be freely defined by the user. The Excel and the CSV files are stored in the project´s Data folder. For both files, the data is imported from cell C3 to the last row and column containing data. The import data area (blue area) MUST NOT include the header column. The worksheet of the Excel file is named Product_Sales_Data. The separator in the CSV file is “;” . All imported nodes will be of the class Product.

sequence importNodesFromExcelFile {  
   Data_importNodesFromTableTransposed("..\\..\\Data\\DataFile.xlsx", 2, 3, -1, -1, 
   "Product_Sales_Data", "", "Product", map<int, string>{
       1->"id",
       3->"salesvolume"})
}
sequence importNodesFromCsvFile {   
   Data_importNodesFromTableTransposed("..\\..\\Data\\DataFileNodes.csv", 2, 3, -1, -1, 
   "", ";", "Product", map<int, string>{
       1->"id",
       3->"salesvolume"})
}

The mapping 1->”id” maps the values contained in row 2 to the attribute id of the node class, as B is the first column of the imported data area (blue area). Note that the header of the row is not the attribute name id as defined in the metamodel, but Unique Identifier. If the node class does not have an attribute named id there will be a warning when the sequence is run but it will still continue. Note also that not all rows have to be imported, e.g. we skipped the import of rows 3 and 5.

Was this article helpful?

Related Articles