You’re offline. This is a read only version of the page.
39
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?
Share
ReportDim 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
Hi @erika027 !
You can try this out.
Vb script used:
All responses (
Answers (
' 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()
