How to concatenate two columns in datatable without looping and keep concatinated data in new column?

[VasuNaidu (VaSu)]

This is the my case:

dt1
Name Account
AA 123
BB 345
CC 654

Output should like this:
Name Account Formula
AA 123 AA123
BB 345 BB345
CC 654 CC654

This should done without loops

Thanks

[sarathi125 (Parthasarathi)]

@VasuNaidu

dt1

Name Account

AA 123

BB 345

CC 654

Hi, From where this datatable is populated, there itself you can try to populate the third column as well. Sorry if my understanding is not correct here. Let us know where you are getting the datatable dt1.

[VasuNaidu (VaSu)]

DB or from Excel

[sarathi125 (Parthasarathi)]

If it is from DB then we can achieve this using query in the database itself.
If it is from Excel, then append the excel before reading it for the third column then read it.

[VasuNaidu (VaSu)]

Don’t go for excel or DB , I want this manipulation on DataTable only.

[sarathi125 (Parthasarathi)]

Hi,

Check this link, they tried the similar stuff only.

How to Copy entire Column(multiple columns) from Datatable

Hi there, I think I understand you are wanting to store the entire column into string to be used to paste into SAP. String.Join(System.Environment.NewLine,DataTable.AsEnumerable().Select(Function(s) s("Column1”).ToString).ToArray()) Then, you can use Set Clipboard or use the string directly into SAP. To do multiple columns, you can assign the expression again but change the column name or use column index. Here is what I used to test the expression: JoinColumn.xaml (5.5 KB) Hope this helps…

[nitin_g (Nitin Gupta)]

The expected result is single column table with same three rows but two column merged into single column like (A1-b1, a2-b2, a3-b3)

[VasuNaidu (VaSu)]

Here is the solution:
Use Invoke method activity, assume my DT column names are ‘Entity’ and ‘AccountId’.
TargetObject: DT.Columns.Add(“FORMULA”, GetType(String), “[Entity]+[AccountId]”)
MethodName: SetOrdinal (Need to pass the ordinal value using argument)

Where FORMULA- my new column name(Concatenated columns)
SetOrdinal- new column ordinal position

[marci080]

Hi @VasuNaidu Thank you so much for taking the time sharing your solution. I was able to implement this.
P.S. Sorry I deleted my previous entry because I implemented it with a loop, but Invoke Method was so much better(performance wise).

[Divya_Gomathy]

Hi vasu…your solution worked if I build a data table set a variable and pass it…but if I read from excel store it in a data table and pass that…the columns are getting added …for example columnA:12001 columnB:1 my desired output is column C:120011 but IAM getting columnC:12002…please help…

[ValeriiaProtsko]

In case someone is still interested. There is an easier way to achieve this.
Create a new variable “FORMULA” of type “System.Data.DataColumn” and use assign activity.

Formula = DT.Columns.Add(“FORMULA”, GetType(String), “[Entity]+[AccountId]”)

[msan (Michel SAN)]

while we are resurecting posts, I’ll add that if the column already exists you can achieve the same result with:

DT.Columns("Formula").Expression = "`Entity` + `AccountId`"

but the original post asked for a new column

[Boopathi (Boopathi Manogar)]

Hi @VasuNaidu

I used this method and everything is working fine but expression also added in the formula column name which only “Formula” name supposed to display.

Concatenating 2 columns A and B and saving it in Formula but it appears Formula+A+B

How to make sure only the column name “Formula” appears

This is Target Object = dt.columns.add(“Formula”, GetType(String), “[A]+[B]”)

Passing 2 as in argument for SetOrdinal method

[msan (Michel SAN)]

Hi,

Actually, the DataColumn’s ColumnName or Caption are not changed but the write range activity seems to rely on DataColumn’s ToString method to ouput its header and this will take first the DataColumn’s Expression property if defined.

If you use the Output Data Table you’ll see different headers.

Use Log Message with the following, you’ll see:

DT.Columns(“Formula”).ToString
DT.Columns(“Formula”).Caption
DT.Columns(“Formula”).ColumnName

I don’t know if you can override the DataColumn’s ToString method in VB as a workaround. You might have to handle your headers by your own means.

I’m asking for a change in the post below: you should “vote” if this matter to you.

Keep Write Range headers consistent when defining DataColumn.Expression

Hi, When you use UiPath.Excel.Activities.WriteRange activity with AddHeaders checked, the headers are not those in OutputDataTable produced string when you set a DataColumn.Expression propertie. Could you please make the headers produced by WriteRange the same are those produced by OutputDataTable? If I had to chose, I’ll pick DataColumn.Caption property as header. You’ll find an example as attachment. You’ll need UiPath.Excel.Activities to run it. WriteRangeHeader.xaml (7.7 KB) Here is a p…

[Caithlyn_Saturos]

what if the column already exist? The column Formula?

[Caithlyn_Saturos]

How to do this?

[msan (Michel SAN)]

Hello, sorry, I don’t understand your question. What are trying to do?

“How to concatenate two columns in datatable without looping and keep concatinated data in new column?” is the original question. Are you asking to apply the concatenation to an already existing column?

[Caithlyn_Saturos]

hi @msan ,

yes.

[msan (Michel SAN)]

Hi @Caithlyn_Saturos ,

You could use the following:

@msan

DT.Columns("Formula").Expression = " `Entity` + `AccountId`"

With an Assign activity, with DT as your DataTable and "Formula" as your DataColumn’s name. If you don’t have the DataColumn’s name, you might give its index as Integer instead. Entity and AccountId are the concatenated column names.

[Haroon_Patel (HP)]

Hello @ValeriiaProtsko ,

I am trying to understand and implement this.

FORMULA is Column Variable I understand.
what is Formula? is that pointing to same Column Name?
ALso I needed a space in between the Entity and AccountId. How to achieve this?