How to concatenate 3 columns of data into a new column?

Help Activities
, , ,
1

I have a file with 24 columns of data of which I want to concatenate 3 of them into a new column, how can I do this?

Forum Engagement Daily Reports
2

@alexis.mendoza.rpa can you explain little detaily do you want column1+column2+column3 in differnt column like these?

3

Hi @alexis.mendoza.rpa ,

Add a new column and loop through each row in your DataTable using a For Each Row activity. Concatenate the three columns you want and store the result in the new column

Example:
row(“NewColumn”) = row(“Col1”).ToString + " " + row(“Col2”).ToString + " " + row(“Col3”).ToString

I hope this helps. Cheers!!

4

image
image535Ă—174 4.08 KB

5

Could you show me how to do it?

And if there’s a faster way to do it, I should do it with more than 20,000 records.

6

Hi,
In my opinion fastest way would be to use modern excel activities, it’ll reduce the time of reading data into data table. All you have to do is use write cell activity and write an excel formula as a string “=A2+B2+C2”, select the range using select range activity ofc (Upto where you want to apply this formula, you can find the row index of last filled value to make it dynamic) and send hotkey key “ctrl+D”, you’re good to go. Cell indexes will be automatically updated using this method. I hope this helps. Cheers!!

7

can you show me how do it?

8

@alexis.mendoza.rpa here is a shortest way using Linq i have attached the flow for reference
(From row In dt_data.AsEnumerable()
Let concat = row(“Name”).ToString.Trim & row(“Age”).ToString.Trim & row(“ID”).ToString.Trim
Select outputTable.LoadDataRow(row.ItemArray.Take(3).Concat({concat}).ToArray(), False)).CopyToDataTable()
BlankLibrary.zip (3.2 KB)

1 Like
9

hi,
There you go, I hope it helps.
Dummy.zip (2.5 KB)

10

Hello @alexis.mendoza.rpa

You could:

  1. Use a Create Data Column activity to create the new column for storing the data
  2. Then a For Each Row in Data Table to iterate the rows
  3. And then an Assign to join the data from the other columns
Assign CurrentDataRow("new column") = CurrentDataRow("column1")+CurrentDataRow("column2")+CurrentDataRow("column3")

Regards
Soren

1 Like
11
  1. Build clone of original datatable
  2. Use for each row in datatable and use assign activity and join all the values of required columns using '+ ’ operator.
  3. Now build datable or use add column.
  4. You can check Merge/join activities too.
12

It works fine, how can I add a space between the data?

image

13

@alexis.mendoza.rpa Try these
(From row In dt_data.AsEnumerable()
Let concat = row(“Name”).ToString.Trim & " " & row(“Age”).ToString.Trim & " " & row(“ID”).ToString.Trim
Select outputTable.LoadDataRow(row.ItemArray.Take(3).Concat({concat}).ToArray(), False)).CopyToDataTable()

14

@alexis.mendoza.rpa is it working?

15

have a look at the Blog and refer to the option by using the column expression approach

16

No
If I try that way, the following error appears:

image
image541Ă—118 20.6 KB

17

@alexis.mendoza.rpa

use add data column to add new column

then use assign with following which will concatenate all rows

dt.Columns("NewCol").Expression = "[Col1]+[Col2]+[Col3]"

cheers

18

@alexis.mendoza.rpa can you copy paste the code you have written and screen shot of you flow

19

it working god thanks

1 Like
20

@alexis.mendoza.rpa Great to hear! Kindly mark it as the accepted solution. :grinning_face: