Automate collecting tabular data from web page and insert into excel file - Selenium UI flow (RPA)

hello everybody this is risotto Ronnie Microsoft business applications MVP today I will be doing a step by step walkthrough of creating a web UI flow to fetch data from a web page and post that data to an excel file so I got this request based on my previous videos and that's why I'm going ahead to create the scenario from scratch so first thing I needed a web page for this so I got this LA Fitness schedule that's available on the web that changes Wheatly and maybe I need the web UI flow to go ahead and read this information from the table that you see right here and fetch the data and post the data to an excel file utilizing flow so let's see how we can achieve this scenario so to start with I'm going to go to power automate and I'm gonna go to my flows and UI flows I'm gonna click on new and I'm gonna select the web UI flow option you gotta click on next I'm gonna call this la fitness schedule and my base URL is going to be the URL that I just pasted it right here on the browser so I'm going to close this and I'm gonna launch my recorder and what this is going to do is it's gonna go ahead and launch my recorder now I'm just going to try and squeeze this into this window frame right here okay right here and there's my recorder so the first thing I want selenium IDE to do of course I can start recording and this will open the web browser for me and I can grab things from here and all that good stuff but what I want to do is I want to grab all the pieces of information on this page right I need all the rows and maybe it's a dynamic table you know maybe the the number of rows can change I want to make this dynamic in the state from getting the data from this table so what I'm gonna do is the first step is so as you can see I just opened it I had clicked on recording so it's just recorded a few steps I don't need this I'm just gonna select them and click on the Delete key now what I want to do is the first thing I want is I want to fire a command called open and of course my target is the same URL so I want whenever this web UI flow to run first thing it has to do is use the open command and here's my target now if I play this this will just go ahead and open my target in the browser because that's exactly what I asked the web UI flow to do note I'm not going to use the recording feature everything I'm going to build from scratch manually even the opening of this window now the next thing that I that I want to execute is first thing many times I've noticed with web UI flows when I and I opened up that browser when I open a page sometimes it takes takes a little while for the artifacts on the page to load the dog to load so there is a command that I recommend and this one is called wait for element wait for element visible so it waits for a particular element in this Dom for this page to be visible now by default you see the value is 30,000 that's milliseconds so it's going to wait I believe up to 30 seconds to check to see if this element comes visible on the page if not it's going to timeout an error so well it's gonna wait that's great but wait for what so I'm going to click this option here which says select target in page now before I before I do this so let me not do that let me go back to my let me go back to my schedule and let me click f12 now what this is going to do is it's going to open the eye developer toolbar function of 12 so it's going to open the toolbar for me and I'm gonna pick this table right I want this table let me try and increase the width as well so I want to pick this table right here using the eye developer in fact I haven't combed right now but using developer tools I'm gonna go ahead and let's say I want to pick this table the table is right here now this table has a unique ID in this dome called TBL schedule please note that every webpage is different every dorm is different every table structure is different so you will have to find what's unique so that you can identify that element being present in the table or on that webpage so in this scenario it's a table that has the ID TBL schedule so what I'm going to do is right here I'm going to say wait for an element where I'm going to use the CSS notation so CSS is equal to the table Hach because what's the idea of my table it's TBL schedule very very easy okay table ashe TBL schedule that's exactly what you do in CSS to fetch that element now in order to test and validate this all I need to do again is just click on play and this is going to go ahead and test the first two steps for me and as you can see both of them have turned green both of them have have succeeded in my scenario which is perfect so it was able to wait for that element to load so I've made sure that the Dom has successfully loaded the element that I am looking out for on this on my web page now the next thing what I want to do is I want to know how many rows are available in this table right now how am I going to do that the first thing I am going to do is I'm going to use a command called store text and if you ever want to know what a command does or if you you can go through the list of all the selenium IDE commands always go to reference it will tell you exactly what this command does so this one uses pick an element on the dorm and it will give you the text attribute for that so if I pick this and let's say just randomly I pick this element right here so it's gone ahead and you know created the CSS notation to get grabbed that particular element from the dorm now if you open this it will also go ahead and give you options for using XPath as well so let's say I want to use the relative option here so it says table the ID of the is TBL scheduled Tibor d TR th okay now before we get into this let's once again let's analyze the Dom so if I look at this this table Oh once again so here's my table ID table schedule if you look at the X spot that it is generating it says table ID so and so T body TR th that means under the table body which is exactly the body of the table and just prove it we just expand this again you go to t body is the body of the table in this I have my first row my second row cities all my rows right these are all my rows now I need a count of all the rows in this table right because this table could be dynamic maybe my web page changes so how do I get the count of the number of rows very simple I need T body TR and I just need the count for the number of TRS in T body so if I go back here so if you look at this expert table it's using the XPath expression here ID TBL schedule t body TR now it's pointing to TS because that's the element that I've picked but I only want TR so I'm gonna pick TR and I'm gonna change the store command to something called a store XPath count so it'll give me the count of the number of TR Xindi body and let's say I want to store this I'm going to store this and variable called count rows once again I'm gonna click on play it's going to go ahead and execute this and the first step is to see if it's all succeeded which it has but how do I know the number of what do how do I know what value is in count rows so you can add something called as echo and here you can point to your variable which just got created called count rows and whenever you need to call a variable use the dollar symbol and within the curly braces put the name of the variable it is case sensetive I believe so it has to match the name of the variable that I created once again I'm gonna click on play and it's going to go ahead and replay this and right down here at the bottom as you can see it says echo 14 that means it has found 14 rows in this table and this table exactly has 14 rows so perfect now I know that there are 14 rows on this table dynamic right this increases decreases as long as the dorm stays the same selenium IDE will be able to use these expressions that I defined here and able to fetch the values for me now all I have to do is basically loop through all the rows of this table one by one and start fetching all the column values one by one and then I want to send all this data back to flow from I'm building a UI flow but I want to send this data back as a variable to my flow which in turn will call this UI flow now then I'm sending back this data I can send it back in the form of text or I can also send it back in the form of something called as JSON write again I can I can also store Jason's but this is not a not an API call that I am executing here so I don't have any JSON attributes I have only HTML flow has a parse JSON action but it does not have a parse HTML action there are different ways in which you can do that you can use split in tags and all that stuff but it's it's a lot of work so it wouldn't it be amazing if we can just pass again its HTML here but can I can I build a JSON your even if I build a JSON string in my UI flow and send it back to my flow I can then use the JSON action to create JSON and then just parse my JSON so let's try that out here right here so the first thing I'm going to do is I need a I need to loop through all the rows of this table now I have the count of the number of rows right here so the first thing I'm going to do is I'm going to create another variable so I'm going to use a command called store which can be used to create variables and I'm going to store the value 0 in this and I'm going to call this counter so I'm basically creating a counter variable which has the value 0 once again if you want to test this out just go to echo dollar curly braces and put the name of the variable which is count or play this it's gonna go ahead and execute all the commands right here and if you look at the data at the bottom it says echo 0 that means for this scenario it out put it the value 0 which is exactly my case now what I want to do is I want to create a JSON structure that I can send back to my flow from my UI flow of course it's not gonna be a JSON it's going to be a string I'm going to parse it into a JSON and slow now I am in Visual Studio code and the structure of my JSON that I want to create is going to look something like this so it's going to be an array and each row is going to be an object it's gonna have a column called timing I'm going to put the timing attribute here and it's then gonna have the day's Sunday right Monday and so and so forth you get the you get the idea here now I need to build my JSON in this fashion now if I head back to selenium IDE this is what I need I need the array opening the RI closing and I need each row to be an object which is under curly braces I have my different columns here that I have defined timing sunday-monday this is my timing column this is my Sunday column Monday column and so and so forth okay now what I'm gonna do I'm going to go back to selenium IDE and this time I need to create a variable to store that JSON value so once again I'm going to use store and I am gonna open my array so I'm basically going to start creating my array here and I'm gonna call this JSON value okay that's my that's my array that I'm gonna create or my JSON that I'm going to create I've just put the opening braces of my JSON in here nothing special the next thing what I'm going to do is I need to look through all the items in my rows of this table so there is a while loop there is a do-while loop there are 4 loops there's a lot of things that you can do in selenium IDE in this scenario I'm just going to use a while loop so the command is called while and in the target you need to define your condition so my conditioners while count rows is greater than the counter now what is counter owes count rose is the number of rows that get returned in my scenarios ad my counter initially is zero okay so while the number of rows is greater than the counter keep looping through every item they keep executing the while okay obviously I'm going to increment the counter inside the while loop and that's going to be my next statement so under the while loop click below the while loop right here I want to increment the counter now what I'm going to do is I'm going to use another command called execute script and in the target what I'm going to define is something called as return okay dollar what's my variable that I want to increment is counter plus one so basically what I'm saying right here is return this value which is the value of the counter and add one to this now the counter variable it it considers it as string so you can typecast it to a number so I'm just going to typecast this to a number so I'm going to get the counter value which is zero I'm going to add one to it I'm going to return that value and where I'm going to store this value of this command I want to store it in the counter again simple right that's my while loop now if I want to check to see if this works I'm going to again execute an echo command and I'm going to print the value of the counter and whenever you open whenever you do do-while loops while loops for loops you need to also execute another command at the end of command called end so you close that loop right now at the bottom I'm going to clear my logs I'm going to execute this entire UI flow again so while this is executing notice it's running through the loop and you see the counter is increasing 1 2 3 4 5 6 7 8 so just kept kept going kept going kept going kept going right my counter executed and finally my counter completed successfully right so my condition is very simple right while the number of the count rows is greater than the counter you keep executing this I kept creating a counter simple now the next thing what I want to do is I want to go ahead and create variables for storing each value of my cells now I know I have a lot of cells here I'm just gonna focus on the timing cell the first column the second column and the third column I'm just gonna fetch the first three column information now how am I going to do this first thing again analyze understand the dorm so if I click on this note note that under the table the T body tag we have all the rows for each row the first row is a th right it's a table header and within this as though is an h5 tag which has the value the second column is the second TD tag which is empty in this case the third column is the third TD tag once again this has a value and so and so forth right so this has a th tag and all these subsequent ones have a TD tag associated with it okay now if I head back to my if I head back to my UI flow now within the while loop itself if I right click here and if I say insert new come on I can insert steps right here and what I'm going to do is I'm going to use once again something called a store text okay store tags basically gives me the text of that node now if I want to connect to that node I already know what my ex spot looks like right I'm going to reuse this export code right here and paste this right here now I've gone up to TR right now TR is always going to be the first TR unless and until I specify which TR in this chain I need now my TR is defined in my counter okay now normally ad is begin from position 0 but at but here when you're using export you have to begin from position 1 my counter initially was 0 I increment it at first so and start from 1 1 2 3 4 and so on and so forth so instead of putting TR 1 or TR 2 or tr3 all I'm going to do is I'm going to use dollar curly braces counter so it's going to put the value of the counter here and get me the TR value right this will give me the TR that is the current row for which the while loop is a cuting now within this if I want the first column we've already seen how the Dom looks like if I want the first column at sth second column is TD and so and so forth now because there's only one th I can just do /t h and this is going to give me the th torte this into something called as column timing okay call timing I'm gonna copy this I am going to paste this again because I'm going to reuse this again again I'm going to create another thing called Paul Sunday okay that's going to give me the second column value and my column is right here it's my TD now this is the first TD this is the second TD so Monday is the second TD and so and so forth so what I need to do for call Sunday is replace this th with TD and one right for Sunday it's going to be the first TD once again copy paste this time called Monday and this is going to be TD number two right and now if I want to test to see if the value is right so I know I've printed the counter here let's also print dollar call Sunday value right here and let's just run this to see if everything goes through well and if it does it should go ahead so see for a second is empty third one is cycle zone TBD body works plus that's exactly what it's fetching here from the sunday column so it's working absolutely fine and the same thing it's going to do from one day and so and so forth so perfect I've got the values from the columns dynamically looping through the loop now the next thing what I want to do is I've created a variable called JSON Val which is obviously going to be a string right now but I want to create a JSON that looks something like this now I already have the opening array tag now for each row I need to add this into my row so that means for each for loop what I need to do is once again I'm going to insert a command and this one is going to be called execute script okay now what's the script that I want to execute right here mice is going to be once again returned because I want to return a value from the code that I'm defining first thing is dollar JSON well this is the value stored in this variable right plus now because this is a new row in quotes I am going to open the curly brace and this curly braces my JSON structure right here now the next type what I want to do is I want to add the I want to add the piece that defines timing right I need to have the double quotes now one thing that I've observed as I was not able to whatever I tried before this I was not able to put double quotes in here and executed successfully apparently it doesn't escape double quotes I'm sure there's some escape condition but unfortunately I was not able to figure that piece out if anybody does please do let me know so instead of putting the double quotes because anyways my data is being returned as a string I'm going to use any delimiter of my choice I'm just going I'm just picking 3 - 3 - 0 nothing special about this so instead of passing double quotes I'm passing three hyphens and then in flow what I'm going to do is I'm going to replace all the hyphens with double quotes purely because I was not able to do this in selenium ID because if I put a double quote here and if I if I execute this so when this runs what I have observed is it goes ahead and it it it does not it does not play well with with the double quotes as you can see so what I'm going to do right now is I'm going to go back here and right here I have my timing column let's just show you how it fail so I'm going to have my timing column your timing column in double quotes of course and then I have colon and so that's in string I'm going to do a plus so basically I have just built this part right now and now once again under double quotes I need so again I'm gonna open double quotes in these double quotes I need the value of my timing so-called timing is what I am going to store here and once again I'm gonna do plus and my double quotes now if I store this in the same variable called JSON Val and if I execute this you'll note that when this runs I did observe that it fails that's because it it has an issue analyzing or parsing that double code token so once again let's clear the log let's go back here and this time instead of the double quotes I'm just going to put a token that I will replace later on so I'm just going to put three dashes right here so I'm just gonna put three dashes right here I've put the call timing column once again three dashes right here okay so I have what I'm doing right here is very simple the previous value of the JSON plus a hard-coded string that I am defining which basically looks like well it's not going to be the right JSON but it looks something like this and then this is going to be the value of whatever I fetch from from my Dom so if I head back here oh I have this token defined I have my brackets opening I have my timing call them defined after my type timing column or value is there I'm going to put a comma and then I am going to define the second column which is going to be Sunday and again I need double quotes around Sunday so I'm just going to do something like this there's going to be a colon defined after that once again I need double quotes I'm going to put three dashes plus dollar now it's gonna look a little stupid but it's all going to work called sunday plus once again single quotes double quotes is my three quotations and let's say I just do Sunday for now I don't want to get into Monday and so and so forth and once I'm done with this I am going to close my tag okay so all I've done so far is I have defined up until this point now I need to close it and then I need to close the as well so what I'm going to do right now is instead of closing it right here in this step what I'm going to do is I'm going to add a condition right here someone and sort another compound I'm going to add an if condition and my condition is that if the count rows equals the counter that means this is the last row if this is the last row this tells me if this is the last row not now within this I want to add a command and my command is going to be execute script and once again return return what dollar J so on well so whatever I have in my JSON plus single quotes I'm going to close the row and I'm going to close the array why because this is the last row in my loop okay next thing what I'm going to do is I'm going to insert another command and I'm going to add else okay so I'm going to put the else condition right here you can copy this and I can paste this here move this under else right and once again because it's if and else you have to add an end your - so I'm going to add an end right here so it is very simple if loop begins if my condition matches this is what's going to happen all these steps else all these steps are going to execute end ok now in this scenario I don't want to close the array I just want to close the row so that's all what I'm executing right here and finally when all of this ends just so that I'm because I'm testing this right now I wanna make sure my value is correct I'm just gonna print the JSON value right here okay and whenever you're executing script and you're returning a value we need to also store it I'm going to store it in the same JSON variable now I'm going to go ahead and play this and it's going to go ahead and it's going to execute it for all the rows in my table as you can see and if everything goes through fine once it loops through all the rows you see its own row for row 5 it's executing through all the rows of this table and once it completes it should give me the JSON so let's wait and see if this thing works fine still going still going alright there you go that's the final tag that it has put out now if I copy this and if I put this right here right or let's say I go to go to the web browser again I use this side Lord Jesus lint calm if I just paste it right here of course if I validate this JSON it's going to fail because it's not understanding what these what these tags are right - - - now as you can see for some reason my dashes didn't come through correctly so if I go back to selenium IDE let's look at why has so three dashes three dashes must be something that I have put off three dashes to be - there's three - yes it looks fine right here so let me go ahead and copy this again and let me paste this into a notepad I am going to show you all the new paddles looks like so here it is and I'm gonna go to replace I'm gonna replace the three dashes and this is what I will do in flow as well but double quotes replace all now I get my JSON I'm gonna copy this I'm gonna go to Jace GS Lind JSON lint calm and I'm going to validate my JSON to make sure it's working fine as you can see it's showing all the data but I forgot to put the comma so if I head back to my UI flow the mistake that I have made is when I ended the tag I forgot to put the comma right here for every row because the next one is to begin after a comma comma and now if I execute this again this will go ahead and put that comma in there and this is going through now once again and executing through all the rows in that table and it should get me the data now this video is going to be a long one because there is there's a lot of work that needs to grande work that needs to be done first to get the exact json string value from a web page so this is executing all the rows again and once this completes I should have my string that I'm going to copy again I'm going to paste it into the notepad I am going to use the replace action replace I'm gonna copy this go to JSON lint comm and once again just paste it here and validate the JSON now you see my JSON looks correct and it has all the values from the timing the first column and all the values from my second column which is the which is the value of the the schedule for Sunday ok now let's say I want to put all of this in an excel file now I have both my flow this is what my flow returns I'm gonna click on see if please don't forget to click on save and once I do that you see yours the flow that just got my UI flow that got saved if I go to edit again it will come back here and I can replay this now when you from a flow when you call a UI flow again this is still in preview what I observed is if you have these echo commands it it errors out so I'm just gonna go and remove all the echo tags because these were only meant for testing purpose so I've just removed all the tags related to echo and once I'm completed with this I'm going to click on save okay so here's my flow my UI flow now in order for me to call this flow I'm going to go to my flows and I'm gonna create an instant flow for now you can create a scheduled flow that runs every week goes picks up the schedule and posts to an excel file this is just an example so I'm going to manually trigger this flow and right here the first thing I want to do is I'm going to search for UI flows and I'm going to run a UI flow for the web in this I am going to pick my UI flow that I just created and let's say I'm going to execute this in Chrome and in my next step I am going to add a compose action and in this compose action I am going to now notice all the variables all the stuff that I created in my UI flow are all available here I just want that JSON well so I'm gonna pick JSON well now this JSON Val is going to give me the results but remember I wanted to replace those three my delimiter which was those three hyphens with double quotes so if you go to three ellipses and if you go to peak cord you can actually copy the cord so I'm just going to copy this code right here gonna hit done once again notice I went to peak code and I'm copying I don't copy the add symbol this is the expression lines and accents lo I am going to remove this and this time I'm going to go to the expression syntax now an expression that is a function called a replace I'm gonna call this function replace expects the first parameter to be the domain string so that's my body tag and I copied in peak cord comma right now what is the old string that you want to replace so in single quotes I'm going to put three hyphens because that's the delimiter that I defined in my web UI flow and the next one is going to be the double quotes so what it's going to do is going to replace the three quotes with double quotes and it's going to give me the result now that result I know is going to be an actual JSON so what I can also do is I can use the JSON function and give it a string value which is right here so this will transform it or give me a JSON object I'm going to click on OK if all of this works fine this should give me a JSON object and I'm gonna really rename this to JSON value okay that's my action now if I head back to my I go back to JSON learned this is the JSON response that I that I got right I kind of calculated now I can run this flow and get it but I already have it so what I'm going to do is I'm going to use something called as parse JSON okay now before I execute parse JSON let me just go ahead and save this flow and let's just run this flow and see what happens okay so I'm going to save this flow test save and test continue run now this flow is executing and while this flow is executing what it is going to do is in the background it's actually doing it I have a second monitor so it's doing it on the second monitor it is opening up the web page and of course there's a lot of calculations happening all the core that all the logic that we wrote is currently being executed and once all that logic completes it's going to close the web browser and then it's going to go ahead and complete this action called run a UI flow for web and it's going to return me to JSON value now while this is happening because I want to post this data to an excel file I am in my onedrive I'm gonna go ahead and quickly create an excel file in onedrive while the UI flow is running in the background and in Excel I'm going to create quickly create a column called timing a column called Sunday please note they do not have to match the exact values that I was reading in my UI so these are just column headers just like select both of these go to insert table I'm gonna say yes my table has headers because these are the headers you have to define tables in Excel so that power automate and power apps can leverage it click on OK so it's created a table and let's rename this to LA Fitness schedule okay so here's my flow I'm sorry here's my excel file in my onedrive I'm gonna close this and here's my file LA Fitness schedule that I created in my onedrive which is empty now if I head back to power automate you see it executed and let's look at the JSON value boom see this let's copy this let's go back to the JSON value it and post it right here see I got these values from my UI flow perfect next what I want to do is I want to post this to that excel file so what I'm going to do is I'm going to copy this output control a control C and use an action called parse JSON I'll use the parse JSON action my content is going to be the JSON value right and the schema I'm going to say generate from sample and I'm gonna paste that JSON that I copied hit done and the next step is I'm going to go to new action I'm going to use the Excel online for business and I am going to use to add a row into a table action because I want to add rows to that excel file so the first step is where's the location of my excel file it's my onedrive the document library is going to be of course onedrive and which folder in my onedrive I have created a folder recall the U I flows I'm going to pick this folder and whether this folder is my excel file right here now that it has connected to it look for any tables we have we created a table and in this table there are two columns timing and Sunday now because I passed my JSON and my JSON object this output action and flow will now contain the values for Sunday and timing because these are the values that are being returned as part of my JSON that I just passed if I add additional columns they would all show up there so for timing I'm going to pick timing and notice when I do that it puts a for loop because it could have multiple rows in my JSON it's an array and the next thing I'm going to pick is from the parse JSON action is Sunday done I'm gonna test rerun this entire flow again and continue run and manually running this flow now what's going to happen is once again the UI flow it's executing in another browser window of mine I'll just show you it's right here see the selenium IDE called the flow the web UI flow you utilize selenium IDE to call my web page and now the first step in our web UI flow was to open the web page and then those all those calculation logic that we that we wrote which was calculating the number of rows on this table and then looping through each row and for each row we are fetching the value from the first column and the second column so it's going to go ahead and do all of that now note the schedule is from 5:45 a.m. I'm going to try and see if I can say 1 5 4 okay it closed because the selenium IDE completed its task and it's now moving ahead but just to show you what the values look like I'm going to post the webpage on the web browser again so as you can see 5:45 a.m. all the way through 8:00 p.m. there are a few classes that are happening on Sunday it's only happening from 8:15 a.m. to 11:00 a.m. right there's no class of 9 if I head back to power automate it should go ahead and now it's looping through 14 rows as you can see because we have 14 rows and 2 columns that we fetch we could have fetched all the columns as well the code would have been longer that's it and once all of this completes which it has if I now go back to my UI flow and if I look at this excel file and if I open this excel file let's meet and watch one and if I expand these columns there you go right here see this brilliant so it fetched all the data from my web page using UI flow and based on all the formulas that we defined so it's very important to know the selenium IDE commands it's very important to count the number of rows it's very important to loop through each row it's very important to fetch the column values created in a string format like I did and then just pass it back to the flow converted to a JSON make it make it like a JSON structure as you saw and then use the parse JSON action just loop through it and put it in the excel file I hope this was useful thank you so much for watching

Channel: Reza Dorrani

Views: 30,406

Keywords: rpa web to excel, UI flow for web, web ui flow exercises, excel web scraping tutorial, power automate tutorial, power automate ui, power automate ui flow tutorial, robotic process automation (rpa), ui flow, ui flow rpa, ui flows excel, ui flows power automate, web ui flows, power automate desktop, power automate desktop ui flows, power automate desktop rpa, power automate desktop web, power automate web ui flow, power automate rpa examples, power automate ui flow excel

Id: EdhV5Q_aXdk

Length: 39min 25sec (2365 seconds)

Published: Mon Jan 27 2020