How to insert values to a data table based on unique other coloum

[Tharusha_Fernando (Fernando ✔️)]

Hi Team,

I m doing a table automation And i m getting the Results “Pass” or “Fail”
if i m having the “ID” from ID coloum what is the best what to insert the Value to pass or Fail for that Row based on the ID value

Thank you

[pravin_calvin (pravin kumar)]

Hi @Tharusha_Fernando

Created work flow based On your scenario!

Please refer to the xaml below.

test_1.xaml (7.9 KB)

Regards

[Tharusha_Fernando (Fernando ✔️)]

Hi @pravin_calvin

i Dont use for each row . i m referring for some thing like this for excel sheet

UPDATE datatable
SET Status= 'Pass"
WHERE CustomerID = 122;

Thank you

[pravin_calvin (pravin kumar)]

Hi @Tharusha_Fernando

How about Using Linq expression

(From d In readDt.AsEnumerable
Let u = If(d(0).toString.Trim.Equals(“125”) Or d(0).toString.Trim.Equals(“122”),If(d(0).toString.Trim.Equals(“122”),“Pass”,“Fail”),“”)
Select NewDt.Rows.Add(New Object(){d(0),d(1),d(2),d(3),u})).CopyToDataTable

Refer to the xaml below!
test_1.xaml (6.3 KB)

Regards

[Tharusha_Fernando (Fernando ✔️)]

hi @pravin_calvin

Thanks for the Reply it should be like this
find the row with the ID then update the row column name status “pass” or “fail”

Thank you

[pravin_calvin (pravin kumar)]

Hi @Tharusha_Fernando

If possible can you Share me your input File and Expected OutPut ScreenShot!

Regards

[Tharusha_Fernando (Fernando ✔️)]

Hi @pravin_calvin
This is the Input file
bot will go and Search the ID from a website their will be a string call pass or fail i needed to mark that value IN front of the same ID row on status column in this excel sheet

Thank you

Input.xlsx (9.1 KB)

[Palaniyappan (Forum Leader)]

@Tharusha_Fernando

UPDATE datatable
SET Status= 'Pass"
WHERE CustomerID = 122

Hi

Check this thread for how to use excel as a database and pass Sql query to update the rows

How to use Excel as datasource and execute SQL Query?

Hi Team, I am trying to connect Excel as datasource, but not able to connect. I did some research and found following post. But still it is not helpful getting same error as mentioned. I have attached the excel file. I am looking for correct connection string for xlsx or xls and SQL query. Source.xlsx (8.6 KB) Kindly assist.

Cheers @Tharusha_Fernando

[pravin_calvin (pravin kumar)]

Hi @Tharusha_Fernando

read range the excel

For Each row readDt

Type Into Currentrow(“ID”).ToString And enter to View the result pass Or Fail

Get Text the Status From website based on Current transaction.

Make sure that Selectors are dynamic!

Assingn CurrentRow(“Status”)= gettextOut.

Outside the For Each Write range as same readrange path and Dt.

Regards