LINQ Query to Merge 2 columns into 1 column with a space

[Steven_McKeering]

Hi Community

I have spreadsheet which is too large/slow (15-20k rows) to use a For Each. There are multiple columns in the spreadsheet (not all columns listed below) - sample image below.

Problem: I need to merge the column ‘FirstName’ and ‘LastName’ into a new column called ‘Name’.
(MiddleName doesn’t need to be removed as per the image)

.
…
.

Can anyone help with a LINQ query to merge two columns into a new column with a single space between the values.
Example:
“John Smith”
“Jane Doe”

Bonus points if you can include a trim on each the row values.

I am open to other options. Thanks.

@Yoichi @ppr

Cheers

Steve

[Anil_G (Anil Gorthi)]

@Steven_McKeering

You can acheive this by using expression

Read the data into datatable dt

Use add data column activity and add column ‘Name’

Using assign as below

Dt.Columns("Name").Expression = "[FirstName] + ' ' + [LastName]"

Cheers

[Anil_G (Anil Gorthi)]

@Steven_McKeering

Updated expression to include trim as well

Dt.Columns("Name").Expression = "TRIM([FirstName]) + ' ' + TRIM([LastName])"

Cheers

[Steven_McKeering]

Hey @Anil_G

Thanks that worked on a quick test datatable.

Will try again soon on my large file. Hopefully its faster than a For Each.

Thanks again for your help.

Cheers

Steve

[Sudharsan_Ka]

HI @Steven_McKeering

Try like this

Create a build datatable and add the columns you wanted in above diagram seems like Name and DOB and create a variable DT_BuildOutput

Checkout this expression

(From d In dt_Main.AsEnumerable()
Let f=d("firstName").ToString.Trim+" "+d("LastNAme").ToString.Trim
Select DT_BuildOutput.Rows.Add(f)).CopyToDataTable

Inside Add you can give all the datarow item

Regards
Sudharsan

[fernando_zuluaga]

Hi @Steven_McKeering

Another approach

(From r In dtInput.AsEnumerable
Let name = r("FirstName").ToString.Trim & " " & r("LastName").ToString.Trim
Let dob = r("DOB").tostring
Let ra = New Object(){name,dob}
Select dtResult.rows.add(ra)).copytodatatable

Regards!

[Anil_G (Anil Gorthi)]

@Steven_McKeering

If interested try going through this

Benchmarking Extract Transform Load (ETL) pipelines in UiPath

Target audience: CoE Leads, RPA Solution Architects and RPA Developers Reasons to perform such benchmarks in UiPath The UiPath forum gets a lot of queries regarding the use of Linq queries to mainpulate datatables. I see many developers have fallen for the myth that Linq queries are more performant than the standard Filter Datatable activity. I started of investigating the two aforementioned techniques, but I did want to share my experience with other tools which can outperform any standard…

And yes expressions can be easier try and let us know the findings if you see any bottlenecks

Cheers

[Steven_McKeering]

Performance is and easy to understand syntax.

I will take a look at the above post you shared. Looks interesting so far…

Thanks @Anil_G