Run VBScript define datatable and return it to PAD

[erika027]

Hi All,

I would like to write a VBScript which extract data from a system in a data table format and return the result data table to PAD.

For this I need to create a data table variable in VBScript, populate it and at the end give back the data table to the PAD flow. 

Is this possible?

Dim datatable
Dim row

Set dataTable = New DataTable
Set row = datatable.NewRow()

datatable.Columns.Add("Name")
datatable.Columns.Add("Age")

row("Name") = "Jane"
row("Age") = 30
datatable.Rows.Add(row)

WScript.Echo datatable

[NathanAlvares24 · Accepted solution]

Hi @erika027 !

You can try this out.

Vb script used:

' Create and populate a "DataTable" (2D array)
Dim DataTable(2, 2) ' 3 rows, 3 columns
DataTable(0, 0) = "ID"
DataTable(0, 1) = "Name"
DataTable(0, 2) = "Age"
DataTable(1, 0) = "1"
DataTable(1, 1) = "John"
DataTable(1, 2) = "30"
DataTable(2, 0) = "2"
DataTable(2, 1) = "Jane"
DataTable(2, 2) = "25"

' Convert the 2D array to a delimited string to pass back to PAD
Dim Result
Result = ""
For i = 0 To UBound(DataTable, 1)
    For j = 0 To UBound(DataTable, 2)
        Result = Result & DataTable(i, j)
        If j < UBound(DataTable, 2) Then
            Result = Result & ","
        End If
    Next
    If i < UBound(DataTable, 1) Then
        Result = Result & vbCrLf
    End If
Next

' Output the result to PAD
WScript.StdOut.Write(Result)

Code (just copy and paste this into your flow):

@@copilotGeneratedAction: 'False'
Scripting.RunVBScript.RunVBScript VBScriptCode: $'''\' Create and populate a \"DataTable\" (2D array)
Dim DataTable(2, 2) \' 3 rows, 3 columns
DataTable(0, 0) = \"ID\"
DataTable(0, 1) = \"Name\"
DataTable(0, 2) = \"Age\"
DataTable(1, 0) = \"1\"
DataTable(1, 1) = \"John\"
DataTable(1, 2) = \"30\"
DataTable(2, 0) = \"2\"
DataTable(2, 1) = \"Jane\"
DataTable(2, 2) = \"25\"

\' Convert the 2D array to a delimited string to pass back to PAD
Dim Result
Result = \"\"
For i = 0 To UBound(DataTable, 1)
    For j = 0 To UBound(DataTable, 2)
        Result = Result & DataTable(i, j)
        If j < UBound(DataTable, 2) Then
            Result = Result & \",\"
        End If
    Next
    If i < UBound(DataTable, 1) Then
        Result = Result & vbCrLf
    End If
Next

\' Output the result to PAD
WScript.StdOut.Write(Result)
''' ScriptOutput=> VBScriptOutput ScriptError=> ScriptError
Variables.GenerateDataTableFromCSV.ReadCustomSeparator CSVText: VBScriptOutput TrimFields: True FirstLineContainsColumnNames: True ReadFieldsAsText: False CustomColumnsSeparator: $''',''' CSVTable=> CSVTable

Output:

I hope this helps.

EDIT:
Another VB Script to use:

' Define the function to create and return a DataTable
Function GetDataTable()
    Dim objDataTable, objRow, data
    Set objDataTable = CreateObject("Scripting.Dictionary")
    
    ' Define headers
    objDataTable.Add "Headers", Array("ID", "Name", "Age")
    
    ' Add data rows
    data = Array( _
        Array(1, "John Doe", 30), _
        Array(2, "Jane Smith", 25), _
        Array(3, "Michael Johnson", 40) _
    )
    
    For Each objRow In data
        objDataTable.Add CStr(objRow(0)), objRow
    Next
    
    ' Convert DataTable to CSV string
    Dim csvString, row, col
    csvString = Join(objDataTable("Headers"), ",") & vbCrLf
    
    For Each row In objDataTable
        If row <> "Headers" Then
            csvString = csvString & Join(objDataTable(row), ",") & vbCrLf
        End If
    Next
    
    GetDataTable = csvString
End Function

' Call the function and output the result
WScript.Echo GetDataTable()