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

Help Activities
, ,
1

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)

image
image1092×116 5.76 KB

.
…
.

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

2

@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

1 Like
3

@Steven_McKeering

Updated expression to include trim as well

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

Cheers

2 Likes
4

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

1 Like
5

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

image
image1288×460 21.5 KB

Regards
Sudharsan

1 Like
6

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

image
image1124×479 26.4 KB

image

Regards!

1 Like
7

@Steven_McKeering

If interested try going through this

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

Cheers

8

Performance is :+1: and easy to understand syntax.

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

Thanks @Anil_G

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.