Library element Data_importNodesfromTable

This article provides information for the code based import of nodes from an Excel or CSV file if the file contains attribute values but not the exact attribute names as defined in the metamodel. This allows for the automatic creation of nodes of one class with a user-defined mapping of property values. The import is done using the Data_importNodesFromTable library function.

The same functionality exists for importing edges, as explained in this article. A general introduction to data import library functions can be found here.

Data structure

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

  • the first column of the data area to be imported does not contain node class names but all nodes are of the same class (=typed, defined as a rule argument),
  • the other columns of the data area contain attribute values of the nodes but the attribute names in the header row do not exactly match the metamodel: values of these columns can be manually mapped to any node attributes, meaning the user can define the attribute mapping in the rule arguments.
unique identifier Name Sales Volume Sales Price Cost Family Product ID
10000 City Move Man 500 875,99 590,90 Street Bikes 10000
10001 City Move Woman 700 899,99 620,90 Street Bikes 10001
10002 Cross Man 400 999,99 772,90 Street Bikes 10002

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

Library function scheme

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

 Data_importNodesFromTable( 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 projects 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 A2 to E4 the denotation would be 2,1,4,4. The row containing the column 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 column of the selected data area is mapped to the attribute id, the value of the third column to the attribute name 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 2 -> … relates to column C, 3 -> … to column D and so on. Please see the example below for more details.

Calling of library function in sequence

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

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

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

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

The mapping 1->”id” maps the values contained in column B to the attribute id of the node class, as B is the first column of the imported data area (blue area).

Was this article helpful?

Related Articles