Need help reading a website data table

Hello and thank you,

I am using data scraping to read a data table from a website and that works fine. The issue is with how I handle that data. I am using a For Each Row activity to read the table and it’s not working as I need it to work. I need it to read the entire data table from the website and then act on what is found. Currently the For Each reads each row and then acts on that data. For example, in the attached image the first row’s Status is Closed.In the second row, the Status is Work In Progress


If all rows say Closed then I want to take one action. If any say Work In Progress then I want to take another action. The issue is that if the For Each Row reads each row and then takes an action based only on that row. I need it to read the entire table and then take the action if it says Work In Progress and otherwise to take another action if all rows say Closed. How do I do that?

Thank you in advance

Is there any action you need to take on a per-row basis, or are your decisions based on one of the conditions below being true:

1.) If all rows have a status of “Closed”, take action 1
2.) If any row says “Work In Progress”, take action 2
3.) If anything else happens, throw an exception

I made up the 3rd one, in case something happens to the table you’re not expecting.

@Anthony_Humphries
Thank you, I am not taking an action on all rows. What you’ve written above is correct

1.) If all rows have a status of “Closed”, take action 1
2.) If any row says “Work In Progress”, take action 2
3.) If anything else happens, throw an exception

How do I make this happen? Thank you again

Hey @mworth123,

Give this logic a try :slight_smile:

   If dt.AsEnumerable.Any(function(x) Not (x("Status").ToString.Trim.Equals("Closed") Or x("Status").ToString.Trim.Equals("Work In Progress")) ) 
         ''' throw exception '''
     Else If dt.AsEnumerable.All(function(x) x("Status").ToString.Trim.Equals("Closed"))
        ''' action 1 '''
     Else 
       ''' action 2 '''
2 Likes

@alin.c.mihalea

Thank you for this. I will apply it tomorrow. I know that I need to replace dt in If dt.AsEnumerable wherever it occurs.

Can you translate that into plain English? I’m not a programmer

Thanks again for your help

Basically trying to keep it simple and not using programming words:

DataTable is a form of store data and only that. You might have heard about “methods” and DataTable as its “raw form” does not have the methods that you need.

When you use “AsEnumerable”, basically you are “enabling” your DataTable to access others methods, such as Any and All. This what we call “interface” in programming.

Please, if i’m wrong someone corrects me! =)

Hey @mworth123

Since you are not from a programming background, using activities would be more easy to understand.
I’ve recreated the data in the image you’ve shared to Excel for developing this workflow attached here.

FilterClosedAndWorkInProgress.zip (20.8 KB)

I’m mainly relying on ‘FIlter Data Table’ activity to check the below conditions,

1.) If all rows have a status of “Closed”, take action 1
2.) If any row says “Work In Progress”, take action 2
3.) If anything else happens, throw an exception

I’ve tested this and it’s working as expected.
Let me know if you have any questions.

@mworth123,

The syntax used in the solution I provided is called Linq, a query language (with certain similarities to SQL) which comes handy when working with tabular data.
You can find more info about all the functions I used here: https://www.tutorialsteacher.com/linq/linq-tutorials
https://www.dotnetperls.com/linq

@nandish47, unfortunately a case where column Status has values “Closed”, “Work in Progress” and “Unknown” would go to “Take action 2”, according to your workflow. The logic should be rearranged a bit in order to work for every possible case.