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:
#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:
#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: