Get data from HTML tables in Power Automate

hey this is Jay good from in codian so again we're going to continue our series on our utility actions and today we're looking at how to pass a HTML table um going to work through a bit of a specific scenario here so what I'm going to try and do is I'm going to take uh the Premier League table uh which is the uh football league uh within the UK um we're going to take the the league table and all the information contained within it so the teams uh how they've played and I'm going to I know underneath the hood that this is actually is a table structure so if I look at the HTML it's a table collection TDS TRS so on and so forth I want to take that data and I'm going to pause it and I'm going to add it in to this SharePoint list here so I've got the teams played one drawn last so on and so forth so what I'm going to do I've got a flow here I'm just going to manually trigger the flow obviously I could have another trigger but I'm just going to manually trigger it for now the first thing I'm going to need to do is actually get hold of this this data so um to do that I'm going to use the HTTP connector and I'm going to issue a web request so we'll take HTTP and I need to do a get request uh and I need to pass in the URL to the web page holding that uh that table that we want to grab the data from the next thing we're going to do is add the encod pars HTML table action so there we go that and your no here what we can do is I can pass it a file or I can pass it the data so all I actually need to do is pass it the data that's passed back from this HTTP action which in essence is basically the entire web page um and all the HTML contained within it so there is an interesting point so I just click C more and pass in the body the way that this action works it will look for by default the first table it finds in the HTML that you provide so you don't just need to pass it the HTML table and that's it you can pass it a complete HTML file representation of a web page it will simply look for the first HTML table that it finds or alternatively you can actually specify an ID or an index so I could say you know go and find me the third table contained for example or the ID of the HTML table so if the HTML table that you're targeting has got an ID attribute with a specific name you can use that to Target it as well so I'm I don't need to set any of those Advanced features that's all fine um what I do need to do before I do anything further I'm going to need to to run this action and that I'll show you why what what this action does is it Returns the data as Json so to use the data that comes back from the action I'm going to need to pause that Json and to pause that Json I need to generate a schema but I can't generate the schema because I don't have any sample data to actually do that with so what I'm going to do I'm going to get rid of that and I'm just going to do test and manually run this quickly and that's going to run through for me so it's going to retrieve the data from the h from the endpoint that I've specified which is website URL here and if I look in the outputs what we should see is the is that body there is the HTML data of that web page and somewhere in there there going to be the data table that I'm interested in so if I go to the uh utility pass HTML table we can see the inputs and also we can see the outputs so what I'm interested in is this data here because this is the data that effectively I need to PA so let me just do contr a contrl C I'm going to pop this into notepad so we can see it so here is the data that's been scraped from that web page and what we'll do is we'll just do format the Json so we can now see that that's the data that's been pulled back from the first table that it's found which is exactly what we want so what we'll do I can use this to generate the schema so if I click edit and I'm going to go pass Json Json even and the content we'll might as well set this up also here so the pass Json we're going we're going to pass is the results coming from the pass HTML table that's fine it's ask me for the schema and they generate from sample click done now I know there's a bit of a problem with this schema cuz I I have tested this flow before I'm showing you how to build it um namely so again let me pop this into into Power sorry into notepad so we can have a look at what's been defined so the scheme that gets generated is quite interesting it always does this where it says these columns are required well actually I can take that off because I'm not I don't need to enforce those columns always got data the other thing um that I need to watch out for is by default power automat will say oh the typ is of type string if it's if it's found data that's always present now I know that there's potential in this particular table for some of those values not to be present so it could be a null value now if I send in a null value then effectively this schema will break because it's saying it can't be null it has to be a string so it's a really simple fix um what I'm going to do is I just bring this back all of these entries here which have got type string I'm just going to change them oh sorry I'm just going to change the string to an array so it could be string or null so I can just pop through here and just correct all of these so this just means that if if if there is a null value that's present it's not going to break the flow and it will still continue to work likewise I'm not saying that the that the actual data themselves is uh is mandatory if you would I'm just saying if the properties there let me use it right so let's just control like that control there we go so we've got our scheme now which means we can start to build our flow what we want to do is we want to create a list item in this SharePoint table here so what we'll do next is we'll do create item and we'll see the next ISM and I've got again I got a little bit of a cheater around for it so I'm just going to select a demo SharePoint site that we've got here called PDF demo and the list name I'm after is called Premier League table so um you can see here that it's now showing me all of the the bits I'm interested in now you'll notice when I select title now behind the scenes I know that title field is the um is actually been renamed so it's the default but I've called it team but behind the scenes it's the the shame old ISM of SharePoint it's called title here um if I look at the PJ on action um oh bear with me a moment so okay so that's reminded me I've just had a quick look to remind myself what's gone wrong there um you'll notice I can't see any of the properties coming from the past J on accident and that's because I've done things in the wrong order here what I'm going to do I'm going to restore the schema that was generated from the sample so that's with the type set to string now the reason for that is so that I can do this now I've changed that scheme I will be able to select these columns now there is another slight ISM here that I sort of alluded to before um where you've got column one column two column I know these SharePoint columns are of typee string um so I can select team there for example and that's going to pop into an apply to each because of course it's an array of items being returned back no when I say select played or one I can't actually see um and that because even if I do in and try and then select that Dynamic data I still can't see it particularly well um and that's because the the the data behind this list that's of a type uh a SharePoint column of type number number number and it's a string so it won't show it to me the easiest thing I think really to do this is I could just do this I can just do played one drawn lost four and points and I can literally control copy and I just do that because it's nice and quick and I don't need to worry about it and I know that'll work and I can get rid of those and I can do the same there for played um oh sorry I got rid of the wrong one there tonight let's do that again so on and so forth so you get the idea a bit of a hack but it just means messing with data types is a bit easier this way there's probably a cleaner way to do it but this is my work around if that's valid um okay quickit lost one more to do in terms of the points okay last one now what what I'm going to do now is I'm going to return the actual schema that I've had before so or be this might look a bit hcky it's not it's just the way that the UI works in the power automate I can restore that string um sorry that scheme that now allows the string and N values um it just means that I won't be able to pick up those properties in Dynamic data but they will be they will work absolutely fine because of course the data is contained in in the the actual data that's been sent backwards and forwards between the actions so now when I run this flow if I click test uh I just manually test this again kick this off we should get a run all the way through so we'll get the HML table passed from the uh data that's been returned by HTTP action um we're passing the action it's going to Loop through now and we're going to get entries into that table and I should if um that's me from earlier so let me just remove that one out but we should see this will refresh the page now there we go there's those um entries that have been added so we can see there's the leag table and all the data that's been scraped from the web page so um I appreciate some of those steps are a little bit um I guess disjointed uh the way that I've manipulated the schema uh and the way that I've been able to select the data but that's more to do with the way that the power automate UI Works more so than anything but hopefully gives you a good example of how you can take you know any web page that contains a table you can point uh HTTP connector add it you can get the data back out and then you can pause it and do something with it with power automate um if you have any questions regarding how to use this action um or any other encoding action you can email us at support encoding.com or you can of course check out the documentation on support portal which is support. encoding.com

Channel: Encodian

Views: 2,176

Keywords:

Id: EXUi7V40iUU

Length: 10min 48sec (648 seconds)

Published: Tue Oct 10 2023