hi i'm cameron mckenzie at cameron mcnz on twitter and i wanted to talk to you about how you can do
some web scraping and move that to excel i did a couple of tutorials that were fairly popular
on doing web scraping itself and in this example i'm going to take that example
one step further and take the results and save it to an excel file
so like always i'll start this off by creating new process and i'll call it best buy screen
scraper because i've got some stuff i want to buy over at best buy i've been looking for a watch i'm
really looking for a discount on a garmin watch really love garmin's and i'm looking
for one that's on sale and specifically it has to be a garmin watch and you can see
this is a list of all the garmin watches on sale over at best buy i like to sort them by
price as well from high to low like to dream of having a phoenix 6s anyways i'd like to automate
kind of scraping this page and gathering all this data so that i don't have to go to the
page every single day that the page will come to me maybe through an email and so the way
you do that is through screen scraping watch this i'm going to copy the url that's up here
and then i'm going to come to uipath open the main workflow window and so you know what i want to do
some data scraping so i click data scraping the tool comes up and it says okay what do you want to
scrape and i want to grab all of the information here about all these garmin watches that are for
sale like to you know maybe run this once a day and then see if there's any new items for sale
and so you highlight well i want to highlight the whole range here that encompasses an entire watch
the price the the link the image and then it says okay well if there's the start one what does the
finish item look like in this list and there's the finished one right there i'll click next
i'll try and highlight the same amount of data for the finished one and it'll say fantastic
we've got these two columns here and i guess i'll call the first column garmin item and the second
column the url and then the other thing i can do is i can extract maybe some extra data and you can
see there the list of the garment items are being pulled out there all the information that's really
important and over on the right hand side it's the url to get to the item but really what i'm
interested in is the price right i want to know if there's been any price drop or any price change so
you can also click this extract co-related data and so you can kind of go dig deeper inside of the
inside of the web page inside of the item and the item that i really want is i want that that
price right i want to keep track of what the price of these items are i want you to pick it
in the first one and then pick the corresponding one and the second one corresponding location
there's the price right there and then it's figured all of that stuff out and then i'll say
that's the sale price i'll click the next button and then you can see that
the the whole data from this page has now been extracted into this data table
so i think i'm finished here i've got the the product i've got all of the information on it
identify the element that navigates the next pages well there's no next page here because sometimes
you might have a pagination right like click this button for more go to page two there's none of
that there that's all the items on the page so i don't need to indicate any pagination there
and then from here this is gonna extract all of that data it's all get put into a data
table called extract data table and if i want to gain access to that information well i could
just write that to an excel file i could send the data in an email i think for right now it
just be nice to loop through that data and take a look at it and so the for each row activity
will allow you to do this don't pick the for each activity very different it won't cast the
object into a row and it'll cause problems here i want to use the for each row activity and say
you know for each row well let's just extract some data for it so i'll do some right line here
and in this case i can get row at zero
dot two string and then maybe just well i've got three rows here so i'll take all three
and of course instead of row zero i'll do row one and i'll do row two and i'm going to run
this application make sure that well first of all i'll make sure that there's no errors yeah
that looks like i'll like it's all good and i will run this application and you will see
it's going to go onto that page do a little bit of scraping
and then finally here in this console you'll see all of the information that i wanted
the name of the product and all the details plus the sale price for the item plus the url all of
that comes nice and handsomely onto the page now if you want to take this example one step further
what you could do is actually write the results to excel so at this point in time when we've scribed
scrape the data we've got all of this data in this exact extract data table object we'd actually
write that to excel fairly easily now this project is called best buy screen scrape i'm going to go
into the best buy screen scrape project folder and i'm going to create a new excel file
i'll call it scrape data i'm going to copy that name because i'm going to need that right now
because at the bottom of this page and i want to make sure that i'm outside of the for loop
i'm going to zoom in just a little bit here just after the for loop completes what i want
to do is i want to add an excel activity and there's the excel application scope activity
just outside you see that blue line there that's the end of the loop and what i want to do in here
is connect to that excel file that i just created and then append all of that data
that's in that data table to that excel file and so all of that data was stored in
a data table named extract data table you can see it declared right there
and if i specify that as the data table to append what will happen now is when
this runs will not only scrape the data print it out to the console but we'll actually have
all of that data written into this excel file and so let's run this program one more time
probably see the excel file flash as they open that up i'll go
back into the project open up the excel file
and look at that all of the information is now in this file we've got all of the data scraped from
the entire element plus then we've got the url and then after that we've got specifically the price
that something is on sale and we wanted to write a little program that you know maybe extracted
phoenix 6x from here and looked at the price you know we could easily have an application that
monitors this file and keeps looking to see what the current price of the phoenix 6x is and there
you go that's how you can actually do the screen scraping and then write the results to excel
and there you go that's how easy it is to web scrape data and save it to excel if you
enjoyed that tutorial why don't you head over to theserverside.com i'm the editor-in-chief over
there we've got lots of great tutorials lots of great articles on enterprise software if you're
interested in my personal antics follow me on twitter at cameronmcnz and subscribe on youtube