Add cols from datatables

Help Studio
1

I have two datatables ,want to merge all columns in third datatable. Both datatables have max 6 cols. Resultant one should have all.. how to achieve this in simple

Forum Engagement Daily Reports
2

Hi @Shahi,
You can try this with Merge Dt

Detailed flow:
Steps:

Steps Using Activities

  1. Create a new DataTable

Use Build DataTable activity → name it dtResult.
Add all columns that are in dt1 and dt2 (you can add manually or dynamically later).

  1. Add missing columns from dt2 (if any)

Use For Each activity → iterate through dt2.Columns.
Inside the loop, use If activity:

Not dtResult.Columns.Contains(col.ColumnName)

If True → Add Data Column activity → name = col.ColumnName.

  1. Add rows from dt1

Use For Each Row activity → iterate dt1.
Inside → Add Data Row activity → set:
ArrayRow = row.ItemArray
DataTable = dtResult

  1. Add rows from dt2

Use For Each Row activity → iterate dt2.
Inside → Add Data Row activity → set:
ArrayRow = row.ItemArray
DataTable = dtResult

After this, dtResult contains all columns and all rows from dt1 and dt2.

Thanks

3

Use either Merge Data Table or Join Data Table. Depends on your needs but you didn’t give any detail so we can’t tell what you’re trying to do.

4

Both of them have a project column. If project is same, need to put this row in third datatable.

5

@Shahi

Use this code in Invoke Code activity with dt1 and dt2 as datatable variable of In argument direction and dtResult as Out direction


' Create result DataTable
dtResult = New System.Data.DataTable()

' Add columns from dt1
For Each col As System.Data.DataColumn In dt1.Columns
    dtResult.Columns.Add(col.ColumnName, col.DataType)
Next

' Add columns from dt2, resolve duplicates with suffix "_2"
For Each col As System.Data.DataColumn In dt2.Columns
    Dim newName As String = col.ColumnName
    If dtResult.Columns.Contains(newName) Then
        newName = newName & "_2"
    End If
    dtResult.Columns.Add(newName, col.DataType)
Next

If you want to copy the data as well, use this code in another Invoke code or below the column merge code provided above.


Dim maxRows As Integer = Math.Max(dt1.Rows.Count, dt2.Rows.Count)

For i As Integer = 0 To maxRows - 1
    Dim r As System.Data.DataRow = dtResult.NewRow()

    ' Fill from dt1
    If i < dt1.Rows.Count Then
        For Each col As System.Data.DataColumn In dt1.Columns
            r(col.ColumnName) = If(IsDBNull(dt1.Rows(i)(col.ColumnName)), DBNull.Value, dt1.Rows(i)(col.ColumnName))
        Next
    End If

    ' Fill from dt2 (use suffix if duplicate column name)
    If i < dt2.Rows.Count Then
        For Each col As System.Data.DataColumn In dt2.Columns
            Dim targetName As String = col.ColumnName
            If dtResult.Columns.Contains(targetName) AndAlso dt1.Columns.Contains(targetName) Then
                targetName = targetName & "_2"
            End If
            r(targetName) = If(IsDBNull(dt2.Rows(i)(col.ColumnName)), DBNull.Value, dt2.Rows(i)(col.ColumnName))
        Next
    End If

    dtResult.Rows.Add(r)
Next
6

Simply use the Join Data Table activity, set it to left join. Set the conditions to “Project” equals “Project”.

2 Likes
7

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