- [Cameron] Hey, I'm Cameron McKenzie
@cameronmcnz on Twitter.
And I wanna talk to you about how you can extract data
from a PDF file and save that data to Excel.
Now I just wanna let you know
before we get into this tutorial,
I'm looking at specific data on a PDF page,
so there's specific fields that I'm going to extract here.
I've got another tutorial that does data scraping,
and the data scraping I pull out tabular data.
So if you've got a table of data
and you wanna pull that data out and put it into an Excel,
I'd suggest you take a look at that tutorial.
If you wanna pull out specific fields,
in this case, I'm gonna take a look at the invoice date,
I'm gonna take a look at the invoice total,
I'm gonna take a look at the invoice number,
pull those fields out,
and then create new rows inside of Excel based on that data.
That's specifically what I'm doing in this tutorial.
And if you need to do that,
I think you'll really be impressed
by how easy it is to do that with UiPath.
A good place to start is always creating a new project.
So I'm gonna create a new project
called ExtractSpecificPDFData.
And as that's creating,
I'm gonna show you the PDF files I'm gonna work with.
I've got these three files here
all in the folder name C orders.
I'm gonna copy that
just 'cause I'm gonna need that in a second.
And as you can see, it's got a number of line items here,
and then each order has got some details I wanna scrape out.
Now I actually did a scraping tutorial
that showed you how to get all of the line items
and get tabular data out of a Excel file.
In this example, I'm gonna be focusing on specific data.
So in this example, I wanna get the invoice number,
I wanna get the invoice date, and I wanna get the total,
and I wanna write those three fields
for each of these different PDF files
into an Excel spreadsheet.
Of course this is three files
but you could do it with 3,000 files
once you've got it automated.
And as you can see, each of these files
all follows the same sort of pattern
although each of these files is completely different.
So how do we do that? Well, the first thing you need to do
is just open up your main workflow
and add in a for each loop. And we need the for each loop
just because we wanna loop through
all of the different files in that folder.
So workflow command for each, drag that over there.
Now we have to use that directory object
that's always available to us,
and call the get files method
and pass into the get files method,
oh boy,
pass into the get files method
the folder in which all of those PDF files are located.
And I always like to call this file.
For each file in that folder,
let's go through them and process them.
And, jeez, I just said process
so I guess that's the next step that we have to do.
We have to actually have UiPath
process each of those files.
So there's an option called a Start Process.
And where is that down here?
System, Application, Start Process is where you'd find it.
I'm gonna drag that onto the page,
and it's gonna say what process do you wanna start?
And I wanna start a process
associated with each of those files.
So I say File.ToString in here.
Now that just kinda loads the file.
I need to let UiPath know
that I wanna do some manipulation of this file
using Adobe Acrobat Reader,
and that means attaching Adobe Acrobat Reader window
to this process as well.
Now this is danger-fraught,
okay, so pay attention here.
I'm gonna drag that on.
It's gonna say which window do you want?
I kinda have to move my entire environment around here
so that I've got both Adobe visible
and I've also got the Attach Window visible as well.
I click the Indicate window on screen,
and then I go over and I click on Adobe Acrobat Reader.
And now it knows to open that file
in this particular program.
Now there's a problem here.
The problem is if you look at the selector,
and again you click on the hamburger, look at the selector,
the selector specifies the actual file
that was in Adobe Reader when I clicked on it.
You don't want that.
If you leave that in, what's gonna happen
is you're gonna try and loop over
multiple files with different names,
and it's gonna say uh-uh, I'm only looking at this file,
and it's gonna run into problems.
So make sure you've got that deselected,
make sure it says omit for the title inside of there.
Again that's the Attach Window process,
you click on the hamburger, it's Edit selector,
and make sure that title is deselected.
The goal here is to get the invoice number,
invoice date, and the invoice total into my program,
and then eventually write that to an Excel file.
So that means I'm gonna need
three variables at the very least,
each of these variables is gonna be put into sequence scope.
The first variable is gonna be invoice name to type string.
The second one is gonna be.
Not invoice name, invoice number.
The second variable is gonna be invoice date.
And then the third variable is gonna be invoice total.
So those are the three different variables
I'm gonna be using in my application.
I need to extract them from this PDF file.
And the way to do that is to add the,
well, I guess I need to do an anchor,
add the Anchor Base activity,
UI Automation, Element, Find, Anchor Base.
I'm gonna add this on here.
And the Anchor Base is a two-step process.
What you do is you say you know what?
There's a field there that's always gonna be the same.
Invoice number is the same on every single form.
The actual invoice number changes, right?
But the text string,
invoice pound invoice number above it never does.
So we can't get this element based on its name
because the name is always changing,
but we know that invoice number is always the same,
so we can tell UiPath, look,
go find that string in the file,
and then look for the piece of text underneath it.
So in order to get the invoice number,
what we do is we say, hey, I wanna find an element.
And so there's the Find Element option,
UI Automation, Element, Find, Find Element.
That's gonna be my anchor.
And it says which element are you looking for?
So I need to, again, kinda move these files
so that I could see both the Anchor Base
Find Element and the invoice.
And I'll click Indicate element inside window.
And I wanna make sure that, oh, this isn't a,
this isn't holding properly.
Hold on.
I'm gonna try that selector again, indicate on screen.
And there we go.
I just had to resize the file.
Sometimes if the file is not resized,
it kind of doesn't understand where all the selectors are.
Okay, so that is the Find Element.
I wanna find an element based on that invoice string
and what do I wanna do?
Well, I wanna get the text that's underneath it.
So now I have to find the get text method.
There it is.
UI Automation, Element, Control, Get Text.
Right, drag that next to it.
It says indicate what text you want.
And there is that text right underneath,
or sorry, I clicked the wrong one.
It's not that text.
It is the text underneath the invoice number.
And there we go.
That should give me the invoice number.
Now the Anchor Base,
it's pretty good at guessing what text to get
but they can go to the left, right, top, or bottom.
And you should specify with Anchor Base,
so there's this anchor position option,
and with the anchor position option,
I can specify, you know what?
That anchor is actually on top
of the value that I'm looking for, right?
Invoice is on top of the value.
And so you specify the anchor position to top.
Now the other thing you might wanna do,
just look at the selector,
and make sure the selector hasn't specified the PDF file.
If it's specified an individual PDF file,
it's not gonna be able to find it
as you loop through multiple ones.
So I'm okay here but sometimes if you actually put this
Anchor Base utility outside of the Attach Window,
you'll end up getting a little bit of a problem there.
Now when we get this text,
I wanna initialize that invoice number variable.
And so on the get text there,
that number is initialized to invoice number.
So that completes that feature.
Now I'm gonna see, I'm gonna take that Anchor Base.
I'm gonna copy it and then I'm gonna paste it right here,
and I'm gonna paste it one more time.
So now I've got three copy elements.
Now the second time that I do the paste,
I actually wanna find,
oh, what is it I wanna find in my PDF file?
I wanna find the date.
So I'm actually gonna change this on the second one.
And again I gotta have my PDF file visible,
so I gotta kinda move these around a little bit.
And so the first one is the invoice date.
The second one I'm gonna indicate on,
or the first one is the invoice number,
the second one is the invoice date.
And again I'll indicate on screen for the date.
And so now I've got one Anchor Base that's looking for,
as you can see from the value, the invoice number.
The second one now goes for the invoice date.
I click on Get Text here,
and now I don't want invoice number, right?
I want invoice date, that variable there.
And then for the third one, I want the total.
And so under Get Text, it's not the invoice number,
but it's the invoice total.
Okay, so that looks good there.
Now again I have to make sure that it's pointing
to the right object on the page.
So I'm gonna have to kinda move this around a little bit.
Things are getting busy.
But for this one here, it's not invoice number.
This one is gonna be the total.
So indicate on screen.
There's total.
And then right here,
what is the field that we wanna get?
Indicate on screen.
It's the value underneath it.
And there we go.
At this point in time,
we've now scraped those fields off of each page.
And so we've looped through all the files in the directory.
We scraped the specific data off of each page.
And now what I'd like to do
is I'd like to save this to an Excel file.
Now how can we do that?
Well, the first thing we need is a data table.
And so we've got three fields here.
What I need to do is build a data table.
So I'm gonna drag this Build Data Table function over here.
I'm gonna create a new variable,
and I'm gonna put this variable
in scope of the entire sequence.
I'm gonna call it MyDataTable,
and it will be of type data table, System.Data.DataTable,
and I'll make it in scope for the entire sequence.
So this will create a data table.
Now I can click on this data table option here.
I'm gonna get rid of that first row
but I'm gonna add a row.
Okay, so the third row here
is going to be the invoice total.
The second row, I need to edit it
because it's not gonna be an int.
It also is gonna be a string
but it'll be the invoice date.
And then the first column is going to be the invoice number.
Okay, now this will create a data table.
I then need to take this MyDataTable variable here
and set it so that the MyDataTable gets,
that gets created, gets updated to that variable.
So this creates the data table.
This data table will then initialize
this MyDataTable object,
so that should work properly.
And then the next step
is just to append this data to the data table.
And so I can go in here, take a look at the,
and it's not Append, it's Add, Add Data Row.
That's gonna ask me what data table
do I wanna add the row to?
So I have to specify here MyDataTable.
And what is the row that I'm gonna add?
Well, I'll just go in here
and I'll specify that the data table that I'm adding is,
and you just need this notation here,
and it's gonna be InvoiceNumber,
comma, InvoiceDate, comma, InvoiceTotal.
And that adds the data to the data row.
And always make sure you spell things correctly.
And I think InvoiceTotal there,
I think that's spelled correctly.
Okay, and this will add the data to that data row.
And now when we're done, we can add an Excel activity.
So after adding this data,
I'm gonna add an Excel Application Scope.
This allows me to open up an Excel document and work on it.
Now this project is called ExtractSpecificPDFData,
so I'm actually gonna go and find this folder Extract,
what is it called?
ExtractSpecificPDFData.
And I'm gonna create a brand new Excel spreadsheet in here,
and not a Publisher document,
a brand new Microsoft Excel worksheet.
And I'm gonna call this my-data,
and I'm gonna copy that name.
So now I've got an Excel spreadsheet in the projects folder.
And on this page where I add the Excel spreadsheet activity,
I can just print in the name of the file
because I don't have to link to a relative folder.
And what do I wanna do here?
Well, all I wanna do is I would just want to append
the data that's in that current data table
to my Excel spreadsheet.
And so in order to do that, I say Append Range.
Drag this over here.
It says, okay, we'll add it
into the Excel spreadsheet on sheet one.
What data table are you interested in adding?
And of course the data table is called MyDataTable.
And it will then append that data table
to the end of the Excel spreadsheet.
And so there you go.
That's the basics here.
What we do is we get our files from the directory.
We then open a process with them.
We then attach them to Adobe Acrobat Reader.
And then one at a time we get first the invoice number,
then we get the invoice date, then we get the invoice total.
We then build a data table,
add a single row to that data table
that has all of those values in it,
the number, the date, and the total.
And then we create an Excel Application Scope activity.
And in that scope, we open up the my-data.xlsx file,
and then we append the range to it.
And that's basically it.
So I'm gonna run this file.
It's gonna loop through all of these different PDF files,
loops through it again,
loops through it a third time.
And then finally when it's done,
the process will be sent back to the UiPath Studio.
And if I come in and take a look at this file
that we created earlier, my-data.xlsx,
you notice that it's got all the information
from those three files in it.
It's got the invoice number, the date of those invoices,
and finally the total of those invoices as well,
proving that I've successfully pulled
from these three PDF files and saved into Excel.
And there you go, that's how easy it is
to extract specific data from multiple PDF files,
and then take that data
and save it into individual rows in an Excel file.
If you enjoyed this tutorial,
please head over to theserverside.com.
I've got lots of great tutorials over there
on enterprise software development,
UiPath, devops automation, you name it.
And if you're interested in my personal antics,
follow me on Twitter @cameronmcnz.
Oh, and subscribe on YouTube.