Copy Entire Column with data from One Table to another

Help Studio
, ,
1

I have two datatables
datatable1
Name Place Age
X Swiz 30
Y USA 25
Z India 28
Data Table2
Animal
Lion
Tiger
Elephant

I would like to copy entire column (“Place”) along with data from Dt1 to Dt2 (dt1 and dt2 are not dependant. Just dump the data to dt2) which should have resultant as dt3
Assumption: dt1 and dt have same no.of rows
DataTable3
Animal Place
Lion Switz
Tiger USA
Elephant India

2

Hi @bassu727

How about the following?

dtResult:
image

image
image743×487 14.3 KB 

dtInput.AsEnumerable().Zip(dt2.AsEnumerable(),Function(r1,r2) dtResult.LoadDataRow({r2("Animal").ToString(),r1("Place").ToString()},False)).CopyToDataTable()

Result:
image

PassColumnHelper.zip (2.9 KB)

Regards!

3

This is called a join.

Use the Join Data Table activity.

4

Hi @bassu727

Try this approach-

  1. Create a new DataTable variable called datatable3 with columns “Animal” and “Place”.
  2. Use a For Each Row activity to loop through each row in datatable1.
  3. Inside the loop, use an Add Data Row activity to add a new row to datatable3. Set the properties of the Add Data Row activity as follows:
  • DataTable: datatable3
  • ArrayRow: {row("Animal").ToString, row("Place").ToString}This will add a new row to datatable3 with the values from the “Animal” and “Place” columns of the current row in datatable1.
  1. After the loop is complete, datatable3 will contain the data you need.

Thanks!!

5

@bassu727

You can try this

1 use add datavolumn activity to add a new column and make sure type is same as the column youw ant to add
2. Then use assign dt2.Columns("NewColumnName").Expression = $"{dt1.TableName}.[Dt1ColumnName]"

Cheers

6

I tried it but I at assign activity I am getting error as
“Character is not valid”

7

Thanks for this input. But I am trying to avoid Loop.

8

Thanks for the solution.

I gave an example data tables. But the actual tables have many columns. The goal is to dump entire column to another datatable (which has a different column name) along with data in it.

10

Hi @bassu727
Does dt1 and dt2 contains similar columns or different columns?
My opinion is that, if it contains dt1 and dt2 contains similar column names then use “Join Data table” Activity to join the Datatable and store it in a new Datatable. If it doesn’t contain similar columns then try using “Merge Data Table” Activity.

Hope it works!!

Regards,

11

Hi @bassu727 ,

Based on the above statement provided, we can simply use Write Range activity with Range starting from B1 with the DT1 as the Datatable, as DT2 is containing only a single column.

We could also dynamically get the Column Letter. But let us know if this works for your case and we can provide the Expression if required.

12

This is the route I am planning to take eventually but since we donot know the column index will try to get it and convert to alphabet.

The main goal is to add the entire column data from one data table to another data table without using loop.
Pre conditions:

  1. Both Datatables have same no.of rows
  2. Source and Destination Column names are different
  3. Both Datatables are Independant
13

@bassu727 ,

For this part we can use the below Expression :

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(dt2.Columns.Count+1)

The above will give the Next Column Letter of the Excel Data, so for your case it will output B, you could then append “1” to it, so that the Range starts from the B1.

1 Like