Query to concat values from two columns in datatable

[hemasai.06]

Hi,

I have a requirement as below -

I have a datatable where I need to merge 1st column and 2nd column values into 1st column and remove 2nd column accordingly.
When merging there should not be any spaces included and 1st word of each letter should in capital.

Currently I implemented this by using for each however it is taking lot of time to execute when there are more record in excel. Looking for a way to reduce run time by using linq/method query.

Any suggestions would be helpful.
Thanks in Advance

[ashokkarale]

@hemasai.06,

Please show some sample input and expected output.

Dummy data is also fine.

[sudster]

@hemasai.06

I have a datatable where I need to merge 1st column and 2nd column values into 1st column and remove 2nd column accordingly.

How many columns in total does the file have?

@hemasai.06

When merging there should not be any spaces included and 1st word of each letter should in capital.

Would be better to post a sample dataset, as you haven’t specified if the treatment the same for both columns.

@hemasai.06

it is taking lot of time to execute when there are more record in excel.

How many rows are we talking about?

@hemasai.06

reduce run time by using linq/method query

LINQ also would have to enumerate each row.

[hemasai.06]

Hi @ashokkarale ,

Please find sample input -

Expected results

[ashokkarale]

@hemasai.06 ,

Here is LINQ for this.
(From row In dtInput.AsEnumerable() Let insuredName1TitleCase = String.Join(" ", row("Insured Name 1").ToString().Trim().Split(" "c).Select(Function(w) If(w.All(Function(c) Char.IsUpper(c)), w, Char.ToUpper(w(0)) & w.Substring(1).ToLower())).ToArray()) Let insuredName2TitleCase = String.Join(" ", row("Insured Name 2").ToString().Trim().Split(" "c).Select(Function(w) If(w.All(Function(c) Char.IsUpper(c)), w, Char.ToUpper(w(0)) & w.Substring(1).ToLower())).ToArray()) Let mergedData = insuredName1TitleCase & " " & insuredName2TitleCase Select dtOutput.Rows.Add(mergedData)).CopyToDataTable()

Output:

Code:
CSVDemo.zip (165.6 KB)

Thanks,
Ashok

[sudster]

@ashokkarale gave you an excellent solution. I think mine is a little bit simpler, if you like it this way.

dtResult = 	(From row In dtTest.AsEnumerable
			Let Col1 = TextInfo.ToTitleCase(row("Insured Name 1").ToString.Trim) + 
					   " " + TextInfo.ToTitleCase(row("Insured Name 2").ToString.Trim)
			Select dtResult.Rows.Add({Col1})).CopyToDataTable