Linq Query with Dt

image
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

Hi,

Hope the following helps you.

img20210624-1

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

Regards,

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

Hi,

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.

Regards,

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

Regards,
Nived N
Happy Automation

1 Like


Getting compile error

thanks, I have more than 7 columns @Yoichi

Hi,

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.

Regards,

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

2 Likes

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