Linq Query with Dt

Hi Guys, can you pls help how to use Linq,
I have to fill the “QBO” on A(Source) column if “QBO” contains in B(Mail Subject) column, otherwise A col fill with “Email”

Thanks in Advance,
Arun P


Hope the following helps you.


dtResult = dtSrc.AsEnumerable.Select(Function(r) dtSrc.Clone.LoadDataRow({if(r("Mail Subject").ToString.Contains("QBO"),"QBO","Email"),r("Mail Subject")},False)).CopyToDataTable


1 Like

Thanks for your response, It is worked well as i expected but it is deleting my existing data in other columns in that excel


but it is deleting my existing data in other columns in that excel

How about overwrite existing data as the following?

Or we can add other columns to argument of LoadDataRow method, if number of the columns is a few.


1 Like

Hi @Spark_robot
you can try this way

Read your excel and store in dt1 variable

Now use invoke code activitu with argumnet as dt1 as In/Out direction.

Code to be used

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“Source”)= If(row(“Mail Subject”).ToString.Contains(“QBO”),“QBO”,“Email”))

Then use write range to write the dt1 into the excel

Nived N
Happy Automation

1 Like

Getting compile error

thanks, I have more than 7 columns @Yoichi


Can you try the following steps?

First, add BuildDataTable activty as the following.

Then, use the following expression.

 dtResult = dtSrc.AsEnumerable.Select(Function(r) dtResult.Clone.LoadDataRow({if(r("Mail Subject").ToString.Contains("QBO"),"QBO","Email"),r("Mail Subject")},False)).CopyToDataTable

Finally, overwrite dtResult to existing excel sheet.


1 Like

Hi @Spark_robot
i had made the correction, please check that in my previous response

Thanks @NIVED_NAMBIAR and @Yoichi, code is working now


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