hey cameron mckenzie here at cameron mcnz on twitter and i want to talk to you about
how you can do some pdf file processing and specifically i want to show you how you can
take some structured data that's tabular data inside of a pdf file scrape it and then shove
it into excel so i'll kick this off by creating a new process project called scrape into excel
and as i create that i just want to show you the three files i've got i've got a folder here
named orders off the c drive and i've got this invoice here and it's got these two lines here
and then i've got another one it's got these three lines here and then i've got another one
that's got three lines here and what i want to do is i want to scrape these lines so get these
line items for each of these three different files and then write them into excel and so that's where
i'm going in this particular tutorial so in order to start that off i need to open the main window
and loop through all the files in that folder in order to do that and you for each loop
drag that on here and for each file in directory.getfiles and then the name of the
directory which is orders now it's actually invoices in there but i called it orders
and so let's go through each of the files in that orders folder and one at the time what we'll do
is attach them to a process so you then look for the process action over here start process
and the process is going to be this file i like naming it file not process because
that's what we're going through each of these files and i have to say tostring
there just to make sure that it's the string representation that we're going to start
and then as we start this process we want to work through it in adobe acrobat
reader and that means attaching that adobe acrobat reader to this process
and so i look for the attach window i drag it on here and now i actually got to do a visual sort of
click and touch so i click indicate window on screen that i want to attach to this process
and then i just click the adobe pdf viewer and it knows that it's going to be using adobe pdf
viewer in order to work through all of these files now one thing you do have to set is the selector
here when you click on adobe like that notice it sets the name of the individual file there b2b002
is what was open i need to deselect that and then validate again the reason for that is i want to
loop through multiple files if i've got a specific file identified here my screen scraping and my
looping isn't going to work so make sure you've deselected that again that was from the selector
edit selector and then make sure the title is deselected and then validate again
okay so what do we do there what do we do next well i guess the next thing is actually just to
scrape that screen so this is where we go into the data scraping process i click data scraping here
it says what do you want a data scrape i make sure that i've got my pdf on the screen i'm
going to deselect anything that's on there right now click next and then i can just click on any
column in this row here so i just click on that and notice that it pulls out all of the different
rows it knows exactly what i want to pull in here so that looks fantastic i'm real happy with that
now when i go back into uipath notice that this data scraping is outside of the attach window
activity that's not good for me so i'm just going to take the data scraping and drop it right into
the do there that's what i need and also i have a feeling if i edit the selector on the data
scraping notice it's once again identified that individual file i want to deselect that that's no
good for me and i don't need that now because i'm already in this attach window process over here
one problem i do run into is the fact that the data scraper is going to pull
in all of these empty rows here and i want to make sure that that doesn't happen so
one step that you'll want to do is just add a data table filter and so i'll add this data
table filter here go into the filter wizard and i'm going to say any column any any row where
the first column is empty just remove it so filter rows look at column zero if it's empty remove it
so that's going to get rid of any blank rows here and when i've got rid of all my blank rows i can
start putting things into excel and so in order to work with excel you need to have the excel
application scope so let's see if i can find that i'll drag that over right after the filter
and when you work with the excel application scope you actually need an excel file to work
with now this project is called scrape into excel so i'm going to go into all of my projects here
find scrape into excel right there and i'm just going to create a brand new excel file
called scrape into excel.xlsx that's right in the root of the project and so here when it asks for
the for the excel spreadsheet i can just type in the name i don't have to do a relative path
this is also nice because as soon as i you know upload this to
github all of those files will be in there and in fact i'm actually just going to throw
those three orders in there as well just so if anybody actually wants to
download this example over on github they can so let me take those orders take those three
copy them and now they're all pasted in there so they're all together although i'm pulling it from
the c drive not from here okay so we've now got the excel application scope in there
what do i want to do well i guess the next thing i want to do is just append all of that
data that i've got out of those structured files into the excel spreadsheet and that's
simply a matter of using the append range facility so i'll drag the append range facility into my
excel application it'll go into sheet number one and now it's asking me what data table worth of
data do i want to throw in there now when you do an extract structured data table data scraping
you notice that it creates a data table for you it takes all of the data in that extracted data
table and stores it in this variable named extract data table so you find that by clicking on the on
the activity for the data scraping i now highlight that name and i'm going to paste it down in here
under append range and so this is going to have excel take all of the rows that were extracted
from the data extraction from the pdf and append them to sheet1 of the pdf of the excel file okay
and i believe that's it i'm going to just take a look oh look i've got a little error there
so it's saying what data table do i want to filter and yeah that was silly of me i got
ahead of myself don't get ahead don't get out over your skis for this filter data table wizard
you want to make sure that the input table is the extract data table and also the output table so
you want to take in this data table that's got a bunch of empty rows in it
take the rows out and then update that table with no rows in it so the extract data table
is there there and there so if you take a look the data scraping creates the extract data table
the filter data table takes that as the input it also sets it as the output
and then finally right here in the append range we use that extract data table as well
okay now let me just make sure that everything is working properly here and i don't have any
errors everything's tostring and in quotes that all looks good i'm not going to run this file
notice that now goes through each of these individual files
it looks good and if i take a look at this scrape into excel file notice that it's now put
all of the data and the details into these files and this matches exactly what was in all of those
individual files so two four six two four one i can go into the pdf you can see four five one
624 2 and 4 all mapping to the data that's in this file and there you go that's how you can scrape
a pdf file and then bring all of that scrape data into excel and there you go that's how easy it is
to do some screen scraping and put the results into excel if you enjoyed this tutorial head
over to theserverside.com i'm the editor-in-chief over there we got lots of great articles and
tutorials on uipath enterprise software devops you name it anyways if you want to follow my
antics you can follow me on twitter at cameronmcnz and subscribe on youtube