Remove column based ont 2 different table

Help Studio
1

I have 2 different datatable

Datatable 1 column List

Column 1, Column 2, Column 3, Column 4, Column 5

daatable 2, column List
Column 1, Column 2, Column 3, Column 4, Column 5, Column 6, Column 7, Column 8

remove the Column 6, Column 7, Column 8 from datatable 2

expected output
Final DT
Column 1, Column 2, Column 3, Column 4, Column 5

2

Hi @Vincent_Nuestro

Use Remove data column activity

image

And then use Merge datatable activity

image
image617×531 19.3 KB

3

@Vincent_Nuestro

Assign activity:

Variable: FinalDT (DataTable)

Value:
(From row In datatable2.AsEnumerable()
Select datatable2.Clone().LoadDataRow({row("Column 1"), row("Column 2"), row("Column 3"), row("Column 4"), row("Column 5")}, False)).CopyToDataTable()
4

@rlgandu can we do it dynamically?

5

Hi @Vincent_Nuestro

Can you try this

(From row1 In dt1.AsEnumerable()
                                   Join row2 In dt2.AsEnumerable()
                                   On row1.Field(Of String)("Column1") Equals row2.Field(Of String)("Column1")
                                   Select dt1.Clone.LoadDataRow({row1("Column1"), row1("Column2"), row1("Column3"), row1("Column4"), row1("Column5")}, False)).CopyToDataTable()

Cheers!!

6

@Vincent_Nuestro

  1. Read Range (Input: DataTable1.xlsx) → dt1

  2. Read Range (Input: DataTable2.xlsx) → dt2

  3. Remove Data Column (Input: dt2, Columns: “Column 6”, “Column 7”, “Column 8”)

  4. Assign (Output: finalDT = New DataTable)
    finalDT = dt1.Clone() ’ Clone the structure of dt1

  5. Add Data Column (Input: finalDT, Column: “Column 1”, “Column 2”, “Column 3”, “Column 4”, “Column 5”)

  6. For Each Row (Input: dt2)

    • Assign (Output: newRow = finalDT.NewRow())
      newRow(“Column 1”) = row(“Column 1”)
      newRow(“Column 2”) = row(“Column 2”)
      newRow(“Column 3”) = row(“Column 3”)
      newRow(“Column 4”) = row(“Column 4”)
      newRow(“Column 5”) = row(“Column 5”)
    • Add Data Row (Input: newRow to finalDT.Rows)

  7. Write Range (Output: FinalDataTable.xlsx, Input: finalDT)

7

@Vincent_Nuestro

Assign activity:

Variable: FinalDT (DataTable)

Value:
(From row In datatable2.AsEnumerable()
 Select datatable2.Clone().LoadDataRow(row.ItemArray.Take(5).ToArray(), False)).CopyToDataTable()
8

image
image625×730 21.2 KB

removeDataCol = in_DB_Columns.Columns.Cast(Of DataColumn)().Select(Function(c) c.ColumnName).ToArray()

in_DB_Columns is my Datatable 1.
dt_outExcel is my datatable 2

9

Columns from Datatable 1 and Datatable to are both dynamic.
They changed everytime,
But i always need to follow whats on datatable 1.
so any columns from datatable 2 that dosent matched from datatable 1 needs to be remove from datatable 2

10

Hey @Vincent_Nuestro ,

You can try using the DefaultView property. It can allow you to filter out unnecessary columns between two datatables.

Assign DataTable2=DataTable2.DefaultView.ToTable(False,(From col In DataTable1.Columns.Cast(Of DataColumn) Select col.ColumnName).ToArray())

This will only keep the columns that are present in DataTable1 in DataTable2. Make sure to replace DataTable1 and DataTable2 with the respective table names.

Regards

11

Hi @Vincent_Nuestro

Assign - ColumnsToKeep = dt1.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).ToArray()
Assign - ColumnsToRemove = dt2.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).Except(ColumnsToKeep).ToArray()

image
image868×1213 64.2 KB

image
image1190×298 15.9 KB

Hope it helps!!

12

@Vincent_Nuestro

FinalDT=(From row In Dt1.AsEnumerable()
 Let newRow = Dt.Clone().LoadDataRow(row.ItemArray.Take(Dt.Columns.Count).ToArray(), False)
 Select newRow).CopyToDataTable()

image
image968×676 27.6 KB