I have a variable where the datatable dt_1 from the Table Extraction activity where the data is stored in the datatable variable in 40 columns. I need to create a new datatable dt_2 where each new row of dt_2 is the data from the next 8 columns of dt_1. I’m having trouble figuring out how to do this. Can someone help me?
@Robert_Martin So, if we have 40 columns it means you want 5 rows ?
Yes. and the first 8 columns would be the row headers. Fun issue
It could be great if you could show the use case with some sample data.
@Robert_Martin try below
Dim dt_2 As DataTable = New DataTable()
' Add first 8 columns as headers
For i As Integer = 0 To 7
dt_2.Columns.Add(dt_1.Columns(i).ColumnName)
Next
' Convert every next 8 columns into rows
For Each row As DataRow In dt_1.Rows
For i As Integer = 8 To dt_1.Columns.Count - 1 Step 8
Dim newRow As DataRow = dt_2.NewRow()
For j As Integer = 0 To 7
If i + j < dt_1.Columns.Count Then
newRow(j) = row(i + j)
End If
Next
dt_2.Rows.Add(newRow)
Next
I used Copilot to help convert this into activities
- Activity:
For Each Row(TypeArgument:DataRow)- Values:
dt_1.Rows - Body:
- Activity:
For Each(TypeArgument:Int32)- Values:
Enumerable.Range(8, dt_1.Columns.Count - 8).Where(Function(i) i Mod 8 = 0) - Body:
- Activity:
Assign- To:
newRow - Value:
dt_2.NewRow()
- To:
- Activity:
For Each(TypeArgument:Int32)- Values:
Enumerable.Range(0, 8) - Body:
- Activity:
If- Condition:
CurrentItem + i < dt_1.Columns.Count - Then:
- Activity:
Assign- To:
newRow(CurrentItem) - Value:
row(CurrentItem + i)
- To:
- Activity:
- Condition:
- Activity:
- Values:
- Activity:
Add Data Row- Properties:
- DataTable:
dt_2 - ArrayRow:
newRow.ItemArray
- DataTable:
- Properties:
- Activity:
- Values:
- Activity:
- Values:
I am getting an ‘Invalid Item Name’ validation on the For Each Row in Datatable block, but none of the child activities are showing any issues. Anny suggestions?
we have multiple for each loops check if they have different variable names for current item. Keep them different from each other
I actually used CurrentNumber on both of them since they were Int32, but both loops are for each CurrentNumber
@Robert_Martin For each is invoked inside for each so it will confuse the compiler. Use different names
you can use CurrentNumberfromRange1 and CurrentNumberFromRange2
I tried that and still getting the validation
@Robert_Martin if possible can you share workflow or screenshot?
@Robert_Martin not able to understand from screenshot as it bit blur can you send me workflow? you can create a test workflow copy paste the for each loop there and add workflow here