Build datatable with predefined no of columns

Help Studio
1

In a proces I create 7 new datatables in which in a loop want to write arrays(string) as new rows.
These arrays wil be 96 fields long. but in the loop can differ from datatable.
The idea was create the datatables and use add datatable row to add and define the number of columns in the datatable. However if I don’t predefine the number of columns, appearanty, I get an error stating there’s not enough columns.
Can I predefine like dt_monday = new datatable (96 columns) (yes syntax is wrong but the drift hopefully right)
or how can I make sure the datatable can handle the input of a new row with 96 columns.
There are no headers.

2

Hi @Luke69

You can handle this by predefining the DataTable structure before adding rows:

Steps:

  • Create a DataTable using Assign: dt = New DataTable
  • Use For Each item In yourArray to add columns dynamically
  • Inside loop: dt.Columns.Add("Col" + i.ToString)
  • Use Add Data Row to add your array

Thanks!

3

@Luke69

If you want to build datatable with predefined columns you can choose from below approaches:

  1. Build DataTable - not recommended as no. of column is large and would take a lot to build or manage
  2. If you want column names to be specific
    2.1 create a excel template, read using read range workbook into a datatable
    2.2 store column names list in config (comma seperated), split in bot, use for each array item, use add data column activity
  3. If columm names are not a problem, then use while loop and loop 96 times and use Add Data Column activity - column names “Column”+indexVar

Also a note: If you want the same structure in all 7 datatables, then you can just create 7 datatable variables, create structure from above step in 1 datatable, then use multiple assign and assign below to all remaing tables to copy structure: Dt1.Clone

To add array of item as a row, use Add Data Row activity.

4

Right now, I think this scenario can be handled in either one of two ways mentioned below:

  1. Invoke Code

Since you have 7 different DataTables, dragging 96 “Add Data Column” activities is inefficient.

Use a single Invoke Code activity to initialize them all at once.

Arguments: Create an argument for each DataTable (e.g., io_dt_Monday, io_dt_Tuesday)
with the Direction set to InOut
and Type set to System.Data.DataTable.

Sample snippet of vb.net code:
’ Create an array of your DataTables to process them in a loop
Dim myTables As DataTable() = {io_dt_Monday, io_dt_Tuesday, io_dt_Wednesday, io_dt_Thursday, io_dt_Friday, io_dt_Saturday, io_dt_Sunday}

For Each dt In myTables
’ Initialize the object
dt = New DataTable()
’ Add 96 columns
For i As Integer = 0 To 95
dt.Columns.Add(“Column” & i.ToString())
Next
Next

  1. The “Dynamic” Way: Adaptive Column Creation

If the number of fields in your array changes inside the loop, you can use a While loop right before your Add Data Row activity to “stretch” the DataTable to the required width.

  • Variable: yourArray (String)
  • Target DataTable: dt_monday

Logic inside your loop:

  1. While: dt_monday.Columns.Count < yourArray.Length
  • Add Data Column:
    • DataTable: dt_monday
    • ColumnName: "Col" + dt_monday.Columns.Count.ToString
  1. Add Data Row:
  • ArrayRow: yourArray
  • DataTable: dt_monday

This ensures that if your array is 96 fields, the table grows to 96. If the next array is 100, it grows to 100.

1 Like
5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.