How to use DataTables, similar to CustomDataStorage in Sequentum Enterprise?

Data tables represent an organized and structured collection of data, arranged into rows and columns. These can be used in DataList scripting as they are helpful for joining data tables and returning results in the form of tables. To implement this, first, add a Data List command. Under the List tab, select "Script" as the Data Source from the drop-down menu.

In Sequentum Cloud, the following utility functions are available for working with data tables:

Property or Function

Description

LoadCsvFileIntoDataTable(filePath)

Load the CSV file from the specified location into the Data table.

filepath - The path to the CSV file.

DataTableToCsv(newdt)

Converts the contents of a DataTable (newdt) into a CSV-formatted string.

newdt - Name of Data Table.

Example

The following example demonstrates how to read a .CSV file and extract the first two rows and their first two columns into a new DataTable:

CODE

#r System.IO.dll
#r System.Data.Common.dll
using System.IO;
using System.Data;
public string GetData(RunContext context) 
{
    var filePath = context.SharedFiles.GetFile("test.csv");
    DataTable dt = context.ScriptUtilities.LoadCsvFileIntoDataTable(filePath);
    DataTable newdt = new DataTable();
    newdt.Columns.Add("col1", typeof(int));
    newdt.Columns.Add("col2");
    newdt.Rows.Add(dt.Rows[0][0], dt.Rows[0][1]);
    newdt.Rows.Add(dt.Rows[1][0], dt.Rows[1][1]);
    return context.ScriptUtilities.DataTableToCsv(newdt);
}

Input:

Output:

The example below illustrates how to join two tables by reading data from separate CSV files and returning a combined data table from both tables:

CODE

#r System.Text.RegularExpressions.dll
#r System.Data.dll
#r System.Data.Common.dll
#r System.ComponentModel.TypeConverter.dll


using System.IO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.Common;


public string GetData(RunContext context)
{
    // Fetch the filenames from the context GlobalData
    var storeFilename = context.GlobalData.GetString("StoreFilename");
    var categoryFileName = context.GlobalData.GetString("CategoryFileName");


    // Load the CSV files into DataTables
    var storeDT = context.ScriptUtilities.LoadCsvFileIntoDataTable(context.PrivateFiles.GetFile(storeFilename));
    var categoryDT = context.ScriptUtilities.LoadCsvFileIntoDataTable(context.PrivateFiles.GetFile(categoryFileName));


    // Initialize the final DataTable
    DataTable finalDT = new DataTable();
    finalDT.Columns.Add("StoreID");
    finalDT.Columns.Add("StoreName");
    finalDT.Columns.Add("Street");
    finalDT.Columns.Add("City");
    finalDT.Columns.Add("State");
    finalDT.Columns.Add("ZipCode");
    finalDT.Columns.Add("Country");
    finalDT.Columns.Add("Latitude");
    finalDT.Columns.Add("Longitude");
    finalDT.Columns.Add("URL");
    finalDT.Columns.Add("CollectionName"); // Add category-related columns
    finalDT.Columns.Add("CatID_Level2");
    finalDT.Columns.Add("CatID_Level3");


    // Combine the rows from both store and category tables into the final DataTable
    foreach (System.Data.DataRow storeRow in storeDT.Rows)
    {
        foreach (System.Data.DataRow categoryRow in categoryDT.Rows)
        {
            // Add a row to the final DataTable with combined values
            finalDT.Rows.Add(
                storeRow["StoreID"],
                storeRow["StoreName"],
                storeRow["Street"],
                storeRow["City"],
                storeRow["State"],
                storeRow["ZipCode"],
                storeRow["Country"],
                storeRow["Latitude"],
                storeRow["Longitude"],
                storeRow["URL"],
                categoryRow["level1_name"],   // CollectionName
                categoryRow["level2_id"],     // CatID_Level2
                categoryRow["level3_id"]      // CatID_Level3
            );
        }
    }
return context.ScriptUtilities.DataTableToCsv(finalDT);
}

NOTE: context.GlobalData.GetString("StoreFilename")accesses the Global Parameter “StoreFileName“ present in the Input Parameters of the Agent command. Similarly, context.GlobalData.GetString("CategoryFileName")accesses the Global Parameter “CategoryFileName“ present in the Input Parameters of the Agent command.

Input File 1:

Input File 2:

Output: