Power Automate Desktop: PDF Extraction and Application Entry

today you and I are building a complete Microsoft power automate desktop project together my name is Anna Jensen I'm an RPA teacher and a Microsoft most valuable professional in today's case our company receives emails in Outlook attached to those emails are PDF invoices we want to save the PDF invoice extract the invoice data from the PDF type it into our application which is the contoso invoicing app and we want to add data into a Reconciliation Excel sheet the techniques we use that is PDF extraction regular Expressions regex UI Automation and selectors Excel work date time manipulation all performed in Microsoft power automate desktop download the course materials from the video description below placing on your desktop it is a ZIP file double-click to move into it we are going to need this project folder so simply just move it to your desktop and close the folder again now double-click the project these are all the important files that we're going to use in this exercise it is four folders We have the emails folders which is six emails we are going to use them a little bit later in the session now move back we also have the input and here we have the PDFs we have six PDFs and let me show you what these are about it is invoices and we want to scrape the data from this invoice we want the date we want the name here we want the email we also want the total we could extract everything but for this exercise we'll take specific parts each inverse is structured the same let me open up number five and that is important that is why we can use regex in case you have unstructured data that means that you have different kinds of invoices click the video in the upper right corner that will teach you how to do it with AI let me close this again so these PDFs in fact these are attached let me go back to our emails here we'll see that a little bit later we also have a links folder here I place two links that we are going to use in the exercise let me go back finally we have an output folder let me open it with a Reconciliation sheet I'll double click to open it in here we want to add our data to once we have extracted it and typed it into our application let's build everything in power automate desktop so I'll close this Excel and I move back first we will just work with the PDFs then we will make it work with emails so go into input PDFs and then just grab one of these PDFs we will make it work with one PDF first so hover your mouse over one of these PDFs shift right click copy as path now we will create a flow in power automate desktop we'll create a new Flow by clicking new flow and I will call it in voice extraction and I'll click create it's very important that you build with me and that way you learn the most so let me maximize it I'll create a region and a region is simply just a container for some actions it's very nice to structure our flows it will get ignored the region itself during run time so here I'll call this station here and I'll click save then you'll find a set variable here and drag it in into the region this is the variable for our project path we will create a variable that will store the path to our folder so I'll say project path like this and then in the value I'll press Ctrl V this is the path to the PDF as you can see here so what I want to do first is to remove the quotation marks like here and I want this path to be only the project path so I have this one here I'll make a control X I repeat control X that will copy it we will cut it because we will use it a little bit later so now our file path is stored here I'll click save now go find another region here and drag it in below this is our next region we will call this one extract invoices and click save find and extract text from PDF here and drag it in here we need a PDF file we will use the project path from up here so I'll click this little x and double click project path then we also need the rest that will be the path to our specific PDF file we have that in the clipboard remember so I can just press Ctrl V here we can see that the variables produced is called extracted PDF text now I will click save and let's go run it here over to the right we can see the extracted PDF text variable if I double click here this is what it is reading me just expand it we will copy all of this so place your mouse inside it Ctrl a control C now we will create a pattern that will extract this so click close here move out to the course materials so it is in our project folder in the links you will go to regexstorm so double click that that will take us to the regex storm you can also Google it or type the address in directly if you prefer in the input we press Ctrl V this is our invoice and be careful you should never put in private data on a public website like this in case you're going to do it with a company invoice make sure you took out all the private info from the document before you paste it here with that said let's try to grab out the date if I scroll a little bit up you can see our date is here it's structured the same on each invoice that means that I can make a regex pattern and the regex pattern is a series of characters that defines a search pattern what I want to say is that look for a date take the next line and take everything on that line that will be our date what this looks like is up here in the pattern we will make a look behind so that will looks like this a parenthesis just right after me a question mark a then lesser than equal to then we'll say date like this and now we said look after the date but since it's a new line we will also make sure to specify that in here so here I'll have it backwards less R backwards less n now we can start to extract the things that we want so move outside the parentheses have a DOT and a plus now we have everything to make sure that we have no unwanted space in the end I'll have a backwards last then I'll have D for digits and I'll say I need four digits that'll be the same what this does it says that we want it to look behind a date and a line break that will start here so this guy up here it doesn't extract with anything it just said look after something then we have the dot the dot is everything the plus is one or more characters of The Dot that means everything and then we says the last part should be four digits that will be our year now go copy this pattern here Ctrl C move to your start menu find Notepad I like to open notepad and save the information here about our patterns because we are going to create four patterns so here I'll say date and put in the pattern for the date we also want the account name so here I'll say account name and if we look closely we can see that we have an account name here then we need to find something that's always close to it that could be the invoice number so the invoice number will always be two lines behind because we'll have the invoice number then the actual invoice number and we will have the account name that means that we can just change this up here a little I'll say just look for characters like this and then we can say look after instead of date I'll make the regex pattern to say invoice number but as you can see here we're getting the invoice number we are not interested in that so then I want to say well I still want to look behind um these characters and then I want a line break more so I'll say R and this is a line break now we have the account name copy your patent move to your notepad and paste it in it's that easy so regex is not something to fear it's straight forward and you should learn it now then we want to search for the email here we will search for this little ad here but we could make other patterns that will match it for example say behind the date what we will do here is that we will delete all of this so we says we say we want to search for some characters then we want an ad and then we want to catch some characters on the same line this is a very simple email pattern we could make it more specific but that's not the case of this exercise just copy this pattern back to your notepad and paste it in under the email then we want to create a total again we will find a total that will be all the way down here we will create it similar to the two dates and account name so because we're lazy let's copy this move it into the pattern what are we going to look behind here that will be total colon and the space first we will say what are we going to look behind we're going to look behind a total a colon a space and I don't want to grab the dollar sign right now so what I do is that I cannot write dollar sign like this I need to escape it because we'll use dollar sign in other ways then I want to say look for digits and a DOT here so let me delete this as well let me make a hard bracket and the way to do it will be digits like this a DOT like this a hard bracket and then a plus will have the amount now grab this pattern and put it into our total I will grab the date let's start with that and move back to Power automate desktop find a pass text and drag it in beneath the extract text from PDF here the text to parse that will be the extracted PDF text so I click click this variable picker here double click the extracted PDF text what do we need to find that will be our regex pattern so simply just Ctrl V paste in your regex pattern then you will say it is a regular expression so tick this the variables produced we have the match that will be the date as we are extracting it we're also having the position that will be the position in the original text we are not going to use it so let's delete disable it to not confuse ourself then we'll click save now try to run your flow over here in match double click it you can see that we have extracted the date and the solution will be to have four pass text 1 4 account name one for email and one for total as well but that will make a very static solution where we will have a lot of past text imagine that we're going to get out a lot of data from the PDF we could make a much more Dynamic solution and it will be straightforward to update it in case we want to extract further things from the PDF later on so up here in initialization we will create a new data table find that here so I'll say create new data table and drag it in after the set variable variables produced we will call this one Rick X so type in regex this is short for regular expression then I'll click edit here I want three columns I'll double click the first one that will be the name I'll double click the next one that will be pattern then I'll click this little plus out here that will add another column double click it again and that will be my match then we will have four rows so I'll do like this each row is one of the things that we want to extract so date account name email and total the patterns we just created those so we have the date here copy it move in here and paste it in and you take the account name like this and you take the email here and you take the total like this then we click save once more now we Loop through each one of these patterns from up here and extract the corresponding PDFs text so what you will find here is a for each drag it in below here to start with we're going to iterate through the regex data table in the regex data table that's just imagine an Excel sheet with columns and rows a data table is structured the same you just sort you will store it into the Q and item that means that for each one of these iterations we can refer to the current row as q and item then we'll click save we will also create a supporting variable so I'll find a set variable I'll drag it in before the for each we will give it the name row number it will start at zero that's because a data table is zero indexed so the first rule will be zero that's a little bit confusing that's the way it is in programming you'll get used to it and that means that the second element will be one the third element will be two and so fourth we also want to add 1 to this row row number for each one of these iteration so drag in another set variable in the for each click the X double click the row number click this x here double click the row number we're going to add one to it and when you want to do these calculations you want to move inside these percentage signs and say plus one and click save now drag in this past text inside the for each it's very important that you do it here in the beginning double click to open it because now we will not take this static pattern but we will use the pattern from the data table so we'll use it for each one of these rows and that means that we will delete this so here we'll say click this x we want to look in the regex data table double click that then we want to say we want to look into the row number that's why we created this supporting variable so have a hard bracket start go to this variable picker scroll down to row number and double click here you need to remove these percentage signs we only need to have percentage signs in the start and in the end of a code block then you can say hard bracket end we also want to specify which column do we want to look in we could do that with a number or the actual header so here I'll do it with the header I'll say hard bracket end I'll say a single quotation marks because this is a text and these patterns we called the column header pattern here and then I'll have a hard bracket end then I'll click save I want to store this match in our data table remember we made a match column so I find another set variable here again it's important that you do it after the pass text and before the other set variable so the order is very important where do I want to store it too well I want to store it into the regex data table and then I move up here I want to store it in the row number now I'm writing it here that will be a little bit easier once you get used to it so here I'll say row number I want to store it in the match column so not the pattern the pattern we read from and we are storing it in the match column the value that will be the match that we just got from this pass text so I will have match double click that and I'll click save now try to click to the left of this Trove that will add a breakpoint this little red that means that our automation will pause once we reach this step so click run here you can see that our match that is the date I'll click run once more here you will see we get the account name over the match we will have the email and we will have the total I can click run automation ends let's disable this breakpoint by clicking it again we also can look in the regex data table here we have stored our information that's how easy regex is so click close here then minimize this we can also close this notepad save it if you're going to need it I will not close the regex tester in the course materials in the link folder here double click it there's this link to RPA in a day go open it that is Microsoft official training material we will use a sample application so scroll a little bit down download this one here AKA Ms RPA in a Day package if this link doesn't work I'll make another Link in the video description up here you can see that we are downloading it we will open up the file what we are going to use is that we're going to go in here go down to the pre-requited lab number one and here you will find the contoso invoicing setup drag it to your desktop then double click it to install the contoso invoicing setup as I said this is only a sample application so let's go install it there you go we have installed it I'll click close let us just close this and minimize this it's placed here on my desktop it's also in the C colon program files if you want to find it there double click the contoso invoicing setup this is the application it mimics perfectly a legacy app what we are going to do is that when we open this we want to move to invoices and then we want to add the data to this one here that is done by clicking new record up here we're putting in our date we extracted that remember the account name the contact the amount we want to set the status to invoiced and then we want to click save after we have done that we will close down the application so let's take one step at a time I'll be closing this and let me show you where you can find the path to this contoso invoicing setup so I'll go to my Windows Explorer then I'll move down to my C drive program files x86 and here you can see contoso Inc contoso invoicing and this is the Legacy invoicing app.x that we're using shift right click on it copy his path we're going to use this path in power to make desktop go back here up here in initialization you drag in another set variable this one will be the contoso path so I'll click say contoso path in the value I'll press Ctrl V I'll remember to remove the quotation marks like this and then I'll click save up here in the initialization we are setting the variables that we use in the flows and we open up our applications we'll find a run application and drag it in here the application path simply just say Ctrl V we just copy it here so I'll delete this quotation mark and this one and in the variables produced we can see that we have an app process ID we will use that to close the application when we are done with it then I can click save now we will create a region below here that region that will be our application work so I'll drag in another region down here below this one here I'll say type into app and then I'll click save let me scroll a little bit down so after we open the contoso invoicing app let us just do that we want to click invoices up here so we are going to find a quick UI element in window and drag it in here as the first item in the region type into app click this drop down add UI element and let me move it over here so here we have the contoso invoices here that is on the document entry just pick this text press control on your keyboard and click with your mouse we have created the UI element and we want to left click it just as we do as human then I can click save I also want to click the new record up here so I'll find another click UI limited window drag it below the first one click this drop down add UI element and now we are actually an invoice so we can go click that and then we want to click this button here we can see we can have an image and a button let's just pick button it won't make a difference so press Ctrl and click with your mouse we have created this UI element now click save best practice is always to rename these U elements you can say this one is called text invoices and this one called button button go over here to the right to this stack of elements here this one here text invoices I'll call this button invoices I can click it here and then I can click F2 on my keyboard this one makes me able to rename it I'll call this button invoices then I click enter similarly down here this is the new record I'll click F2 again this is a little bit confusing button button so I'll say button and then I will say new record like this now we need to after we click this new record we need to fill in date account contact and amount for those you will find a populate text field in window drag it in beneath the click UI element and we will say this drop down add UI element find the date here press control on your keyboard and go select it so what text should we fill in this is this was the date so let me move this we actually have this date remember and let me just click save here this will give us an arrow that is fine I'll just minimize this go to your variables go to regex so our date that we want to fill in that is actually here in the match so I can say reg X then I can say that's the name of the data table I can say the first row that is index 0 and then I can say fill in whatever's in the match column here or simply just the third column that is index 0 then I can save a little bit of characters if I want that let's go try that so open up the public text fill in window go up here click this x that's the variable pickup double click the regex so I wanted to say look in the first row and the third column that will be two like this then I can click save now let's go try run it again and let me just close the contoso invoicing app just to see that it works everything we did so far so we extracting it and here because this contoso app is a little bit slow it will take a few seconds there you go we have now fill in this date and you can see that it's easily getting converted to the correct format similarly we will fill in the account contact amount and set the status so I go here another probably text field in window I will add a new UI element that will be the account so control and select the account field again we will look in the regex this time it is the second row that is index 1 and still it is in the third column that is index two so this one is where we store the actual account name let's create one more fine the contact here this field control on your keyboard click with your mouse we have it so again we go to this variable picker reg X and move in here say we want a third row now that is where we have this one here and then it's still the third column like this then we can click save now we just need the last one so we have this populate text field in window once more we are going to find the amount here and again we will have the regex this time it is 3 and then two and we will click save let's also fill in the last ones before we test it for the drop down combo box we will have a set drop down and now my power to mate is a little bit slow go drag it in here below let's go find the drop down so I add UI element that will be this combo box control on your keyboard click with your mouse and here we will not clear the selected options we will select option by name what name do we want to select that will be invoiced so go to your power automate desktop flow and type in voiced then we'll click save we also need to click save after we done this this will be another click UI element in window so find a click UI element in window down here add UI element find this save button up here control on your keyboard and click it that's it then we can click save now try to run and inspect your flow I'll click run here so we are extracting the data now we will click invoices in a few seconds here we are creating the date the account the contact the amount and invoiced and we'll click save so far so good we have extracted the data with regex and we have typed it into our application minimize this contoso so right here we're again using four populate text field in Windows and we could create a much more Dynamic solution so we are creating a dynamic selector solution this is another important concept so we move over here to the UI elements here we can see we have edit text box two three and four that is these four that we just created that equals to date account contact and amount we're going to use the selectors for these so again we will open up a another notepad this one will be the selectors so and let us just see what they corresponds to first I will just write in date here move down a few lines I will say account here I'll say contact and then I will say amount so right now we're going to grab the selectors for each of these fours here we are going to grab them in here so the first one that will be the date double click the edit text box down here is our selector you can simply just Mark everything Ctrl a control Z or mark it with your mouse you can click cancel go to your notepad paste in the selector up here this is how our selector looks we're going to do the same for the account so open up the text box too Ctrl a Ctrl Z cancel do like this text box tree go down here Ctrl a control C that is the contact and finally the edit text box form the trick here is that we only want to have one of these selectors imagine again if we have a lot of things to fill in we can just store this these selectors in our data table so now we go up here to the create new data table in the beginning and you can also see the power of regions we have a nice overview of what's going on and I can even collapse these things if I want to do that now I want to move into the create new data table I'll click edit here we will add a new column so I'll click the plus here double click here I'll call the new column selector and I'll paste in the selector for date account name email total so that's why we grabbed this one is for date so I'll simply just go in here Ctrl V and move to the next that will be the account name go copy that and let me just be sure that we're in here like this so now we have that one I'll double click in here and then we will grab the contact here like this and finally we will have the last selector that will be the amount like this so now let me just show you what happened we copied it in each one of the selectors here so we can simply just call them and we will create a dynamic selector so then we'll click save I'll click save once more in the region type into app yeah that is where we want to skip all these fours here or at least three of them after the first two click UI element in window we'll find it for each so drag in for each okay for each simply just again Loops this data table so that's what we're going to do so I will click this x here and I'll double click the regex here I can just pick the current item again we are done with it up here it doesn't matter or click save do you want to network and solve power automate desktop problems with 7 000 RPA developers then you should join the I love automation Discord the link is right up here in the corner now drag in the first populate text fill in window this one is the action that we're going to use and we will use for our Dynamic selector so double click to open it first of all we're just going to look at the current item instead of the regex that means that we can skip this one here since now we are entering to it so I say current item and then I say look in the third column that is where our text is so if I click save here and let me just open up the create data table again to make it show you so here comes our match that is where we want to take out the value and fill it in in the control so invoicing app let me click save here with that in place right now we are filling it into the date field we want to make it Dynamic so let me click save here again then we go over to the UI elements so this is the edit text box that's the one we use here let us go over to UI elements the selector that we're using right now that is the edit text box and this one I want to make dynamic so I right click rename yeah I want to say contoso input field that's just the name let's go fix the selector so double click here this is the current selector for the date field go to text editor we're going to delete all of this since we stored the the selector in our data table up here since we're entering through that exact data table click this X say current item like here and where did we store the selector that was in column four that is index 3. so then I can click save now I'm not going to use these three populate text fill in window so I mark them I can click the first one press shift click the last one press delete I also don't need these three edit text boxes one nice feature of power automate desktop is that I can click these three dots removed unused elements and click delete like this we're also going to rename these two so this combo box combo box we could give it a more nice name that's always also a best practice here I can say that's the status this button button that is to save so I right click rename then I click save now I want to set the Run delay to 1 so it goes a little bit faster and let's go test it so I click run here we are opening up the invoicing app and in a little while we will click invoice here we'll add a new field I have the date count contact amount and invoiced and we click save that's it we have now created a dynamic selector for our reconciliation then we want to grab this ID here so let me go back here before the last click you item and then window we want to grab it so go up here find and extract data from window here and drag it in here so the window we're going to click the drop down add UI element and here we are going to pick this text control on your keyboard click it now we have extracted it we will not extract it into an Excel spreadsheet but a variable this one I will call ID instead of data from window so then I can click save one thing that I want to check is this seems like I don't want to have this 1086 in my selector because that means that it only work when the ID is that so again I go to my UI elements I can first of all I can right click rename and here I want to say text ID first then I can double click it and here it looks like we we haven't included this 1086 here in the selector especially here in the child so that is fine I can click save we are done with grabbing the ID try to run your automation again again we're just doing the same thing we are testing a lot and hopefully in the end we have built a very powerful solution that have sold you a lot so invoiced here and if you move over here to the variables in power automate desktop you can see that if I double click the ID we indeed have the ID grabbed so we can fill in the Excel a little bit later I also wanted to because this contoso app we have opened up quite a few I can just close these ones manually but we don't like manual work so I've opened up the application up here called app process ID that's the variable produced I can use this variable to terminate it in the end so by the end I'll have another region I'll drag it in here I'll call this end and I'll click save in here you will have a terminate process drag it in there so we will specify the process not by the name but by the ID and we will use the app process ID that was the one that was generated up here so I double click it and click save we can try to run it again as you can see we run our automation a lot just to test each step don't worry this is just a demo application so we can do nothing wrong we should always have a developer environment where you can build and see that it works as you can see we terminated the application time for our reconciliation that is we want this data that we type into our application we also want to save it in an Excel sheet maybe we're going to use the data further on and we actually in this if I minimized this if I go to the course data so that one is in the project folder if you look in the output folder we have an Excel sheet here double click to open it here we're going to add as we put in the data we will add it to this Excel sheet close the Excel sheet and a good thing could be to just if this is a name that's really hard to spell so I go in here and I grab everything in case you don't see the ending don't worry we can just add that manually just grab the name here so and that is you Market control a control C so we have it on our clipboard we go to Power automate desktop let's make a new region before the end so up here you'll have a new region that's it and you will write in reconciliation well this is a hard word but I have it in my clipboard so we can also use it here just remember to delete the extension I can click save now let's go open up the Excel book so I'll find a launch Excel I'll drag it in into the reconciliation so I'm launching Excel and I'm opening up the following document well I know I'm in the project path right so and then I can say output then another backwards slash Ctrl V reconciliation in case you don't see the ending that is the dot xlsx simply just add that to reconciliation untick the make instance visible and click save make instance visible and click save and here I for some reason place it in the reconciliation I will argue that we should place it up here initial in the initialization where we open our apps and what you just do here is that you mark this launch Excel press Ctrl X go up here where do we want it well that could be just before the contoso invoicing app so I click this run application and I press Ctrl v We Now open it up here go down to your reconciliation region again the first thing we will do is to set the active Excel worksheet that's always the best practice because we had two sheet names in our Excel book and if another user have opened the other sheet first or we just made some work in it we will work with the data there so make sure that and let me just show you what this is actually about so I open up the Excel book again I want to work in the invoices but there's also a sheet called an assistant org this is by the way some good links if you want to follow my work I share a lot of tips and tricks but we want to make sure that we always read from the invoices and not from any other sheet so it looks like this go to actions I'll find a set active Excel worksheet and I'll drag it in here we're going to look in the Excel instance that was the instance that was created with the launch Excel we want to activate the worksheet with the name in voices then I click save our Excel sheet already have or might have data our hat two rows so we need to figure out where to write the next line of data for that we will use a get first free column row make sure you pick this one and not this one you can see those two looks a lot like each other you should pick the get first free column row right in after the set active Excel worksheet this what this does it just produces two variable the first three column and the first three row we are not going to use the first three column we just want to find the first three row so we untick this again best practice we're not creating variables that we're not going to use now you click save now we can write to our Excel sheet because we have all the data that we want remember we just grab the ID and the rest of the data that will be here from the regex that will be these guys here in the match column so go up here find a right to Excel worksheet like this the value to write now it will get a little bit Advanced don't worry please rewind or pause the video If things goes too fast now the video is designed for that I'll be in the value to write we will create a data role we will do that by having two percentage signs make a space and then we will have curly brackets and in the end make another space like this and we will have a curly bracket the spaces will get ignored by runtime but it makes it easier for you to read while I write it for you so 10 to make our row I'll make another space I'll do it here as well we will have two hard brackets start and in the end so this is how a data row looks like we just need to fill it with data in here I want the structure of this reconciliation sheet and that is I want the ID date account name contact email amount and then the status that should just be invoiced because that's all we do here but in case we want another status we can also have a variable for that so I go back here so I knew that the first one that was the ID that is the ID variable we don't need to put it in quotation mark because it is a variable and we refer to variables without quotation marks then I can press comma then we'll have the next value we will find that in the regex here and the date that will be so that'll be regex a hard bracket that will be the first row and we had it the date in the third column that was where we store our matches so it will look like this and now let me just save it we will give an arrow that's fine just to show you so we are completely sure what we're doing we're grabbing this column that is column two then we'll click close and open up the right to Excel worksheet once more make sure that you are actually going here right after the two and the hard brackets then we'll have another comma since these Expressions will look like each other simply just copy this regex hard brackets 0 2 Ctrl C then you will paste it in here you will have a comma paste it in once more comma paste it in and have space now we will simply just say this zero was the date then we'll have the one two and three that is the row three finally we will have it saying invoiced that was the last column again this was a little bit fast but I hope you understand it in case you don't understand this this is very important so please rewind the video and try to get your head around why do we make this this is just a data row that we can put into our Excel sheet I'll put it into column A that is where we want to start and then the row that was the first free row because that is where I want to put the next row in so click this little X double click the first three row and while I click array you can see we don't have an arrow anymore now we can click save we're writing to the instance but we also need to save it we do that here in the end so go up here and find a close Excel drag it here here we will just save it in to the place where the data is so we will not create a new instance now you can click save try to run your Automation and make sure that of course we know that the first part works but we want to make sure that the last part works that is the Excel reconciliation work and now we are clicking the invoices in a few seconds and then we start our work sometimes with these Legacy apps it will take a little bit longer as you can see it looks like power automate desktop is stalling it's not it's just because this is an old application this mimics perfectly our old applications you know application can storm it can be hard to work in and we can see that in a little while we'll see that power to make desktop fine can do the work here um and no matter how slow this is so I'll just fast forward to when power to make desktop starts working there you go we clicked invoices yes it took a minute or so but that's how whole applications behave that's what we deal with here in our APA development now it looks like everything went smooth so I go here go into the reconciliation here there you go we have now added in a new line we might want to work a little bit with data formatting because you can see here the dates here that one is called this and this is the data that we got out let's go fix the date time so what we'll do is that we'll close this go back here and you can see we have a lot of regions we can collapse them so we can better see what's going on so these are regions we have actions in them right before the type into app will make our date time conversion so find a region move it in here we will call this one date time conversion like this and I'll click save so we will find um our text from the data table right here this this guy here we will convert this to a format that was used in Excel and it's also used in our application but our application handled this we could type this in and then the application converted it but in case our application doesn't convert it and we we will convert it now we will also make sure that it will work in our Excel lock so up here you will find a convert text to date time the reason why we converted to a date time is so that we can work with it then we will convert it back to a text specify the format and save it into the data table that is our strategy so first we will find the convert text to date time here make sure you drag it into the date time conversion the text to convert well we have it in our regex data table like this then we'll say we know it's in the first row and it's in the third column right let me just recap it it's in the third column 0 1 2 like this let me open it again the date is represented in a custom format that is it's what's called sept 21 and the year so we need to specify that here in the custom format this is if you want to know more dot net custom date time you can easily Google it and here you might also know it from Excel that will be the month represented by three letters then we will have the day comma and four y's as the year we will save it into text as date time so I'll click save now we have it in the date time we will convert it back to a text where we specify the format you can have this convert date time to text the date time to convert that will be the text as date time we just created that one the format that will be custom and what format do we want to have out well we wanted to have the months first and we wanted the days and then we wanted the four y's each year let me just open the reconciliation sheet again you can see that that is the format we had in the two first rows so we also want to have it here this is also the format that's used in our application so we go back here now we saved it into a formatted date time we will update our data table so what we'll do here is that we'll find a set sorry we'll find a set variable and up here we will say I want to look in the regex then I want to update the first row the second column hard brackets like this what values should be there that would be the formatted date time so it looks like this I'll click save and we will run it again to see that we have done our date time conversion nicely I'll fast forward over this invoices click we know it's a little bit slow um to start up this app doesn't matter it's it will just run in the background in production let me speed it up and come back when power to my desktop had clicked invoices now we are now we can see we type in the correct date time that is nice click invoice to be saved and in a few seconds we save it let's go to our reconciliation sheet there you go we now have our date in the correct format let us close disk down again so so far we only extracted one PDF that was our sample but now we want to expand the solution so it will work on multiple PDFs and let us just do this again the first one we will do that is to talk where do we want to take the PDFs from we have these emails that is our end goal those ones will be in Outlook and we want to extract um these out of messages and grab the attachments which are the PDFs invoices but let's create one step at a time because in input PDFs we have all these six PDFs that is our input directory we'll solve for that first so up here in the region initialization you will find a get files in folder drag it in here the folder that we're going to use we're going to use the project path then we want to look in the inputs or backward slash backwards once more say PDFs and I only in case we have other files I only want to take them that ends with PDF so head in asterisk then dot PDF variables produced lets us also rename this so this one will be PDF files we will pick save between the region initialization and the region extract invoices we will have a 4H yeah so you will drag this for each in between these two the value to iterate that will be our PDFs PDF files here and then we'll say store into current PDF like this we will click save because we designed this so will it will be very easy to just make a few changes and then it will work on multiple PDFs open up the extract invoices region here scroll a little bit down click the region up here press shift on your keyboard and click the end region now you will move it in here we will also open up the the extract text from PDF because now we are not going to read this static PDF but we will read the current PDF up here so delete this click this variable picker find a qn PDF like this and click save then we can collapse this now go to your daytime conversion open it click the region up here click the end region while you have shift in your keyboard drag it in below here then we'll collapse this as well we also have the type into app that will happen for each one of these invoices so I go this Mark everything and I drag this one in here again I Collapse it so now we just need the reconciliation because the end we've designed that that was just terminate the process and close the Excel that will only happen once so we only need to have this reconciliation in to this and there you go that's how easy it is to expand it because we used best practices to structure our workflow let me just save there's no auto save in power automate desktop just to make sure that in case a breakdown happens you have your flow then we will click run and again I'll fast forward over this step you know the drill now we clicked it and now we will see that we are putting in multiple PDFs that will be all our sixth ones we click save now you see that we put in the next one that will be the same account name and contact email but we will have different amounts and different dates there you go we have now a working solution for multiple PDFs we're still not done with the solution but I will speed this up and then we will solve the rest of the case that's it let's also go to the reconciliation sheet here just to make sure that we are getting in all these data there you go we have now solve it for the next six rows what we want to do now is to solve the entire use case because the case is built around that we get these ones into Outlook these invoices so in the emails you'll find six emails simply just drag and drop them over to your outlook I've done that and make sure you create for example two subfolders to the inbox I call it invoices and processed invoice so what this is let me just open one of them this is just an email with the attachment the attachment is just what we saw before so we are expanding the solution let us go created so we want power to my desktop instead of reading it from the folder we want to grab these links to the folder and then we will read it from the folder let's go to that here first we go to initialization we need to open up a new instance of Outlook and again I really want you to place these emails in your inbox and do these things with me you'll learn the most so here we'll have a launch Outlook somewhere between around the launch Excel and one application the order is not important here you can see that we are opening up a an Outlook instance I'll click save let's also close it again here in the end region so I'll find a close Outlook here and drag it in in the end region this will just close the Outlook instance I'll click save Now we move up because we've before we do all this let me just talk a little bit about where we want these things to be in because these PDFs they come before this extraction so we will have a region up here so here I'll have a region drag it in here this one will be called Outlook work like this so what we will do here is to retrieve the messages from Outlook so I'll find a retrieve email messages from Outlook pick this one and drag it into Outlook work the Outlook account that is your data file account let me show you how you find this go to Outlook go to file go to account settings account settings click data files and this is the name you're looking for so this one is called anas at anasians.org I'll use that in power automate desktop as account but that one was important to have in then I want the folder since I placed mine in a subfolder called invoices I'll have inbox then I'll say in voices sorry that was a lot of s's so I want to say I want to retrieve all email messages and Marcus read that's not really important we will not sometimes you can use that but it's very very unstable since people can go into these inboxes and read them and it doesn't really say anything but what we want to do is to save the attachments like this then we want to say where do we want to store the attachments to if I go back here let us storm in input and I want to create a folder called attachments here so instead of PDFs it will be the same but um attachment like this and I go back here so I want to store them into the project path backwards less input and then attach mint like this the variables produced that will be retrieved emails that's fine I'll click save then we want to have this get files we had it up here in the initialization before move them after the retrieve email messages because then we can open it and here I'll say grab the files not from PDFs but from attachments again we created the solution so it's quite easy to update it now this is done let me click save and we click run so now we will run our automation what we will see is that the Outlook work will work instead and again I'll fast forward to routes the invoices click here we have our click we know this works but we saw that now we're actually getting the emails and attachments from Outlook and we are creating new entries based on that that will be the same data by the way but um it's still nice to see that this works that's it what I want to show you now is that in the attachments we have the attachments from the emails and we close the Outlook so if I want to open it again I'll open it here and we still have the invoices here that's fine we have done nothing with them we want to move them into a processed invoice sub folder so go create that one as well in the attachments I'll just delete them because these ones come from the email will of course also move the processed files to move the processed files then we go up here I want to go into my project again if I go back here let's create a processed folder where I will move all old PDFs too so I'll call this one processed like this then we go back here so in the end of this for each right down here I'll move the files so find a move files like this drag it in below here the files to move that will be the current PDF that was the one we were done I'll have the Q and PDF where do I want to move it to that'll be the project path backward slash process that was the directory that we just created here I'll just overwrite it if it exists we could create a case for that a little bit later where we have Dynamic names so they will not get over written the variables produced that will be moved files I'll click save here now when I run this I will speed this up so see you on the other side but now we will see that our PDFs will get moved that's it and again it's the same things as before but if I moved into processed we have moved the process here and that's mean that means that in the input attachments this is empty now so so far so good but I also wanted to move the processed emails from invoices to processed invoice let's create that before this region end we will have another region so here I'll say region like this now move it in here I will call this region move emails and I'll click ok what I'll use here is a process email messages in Outlook so drag it in into the move emails again the account is unless we could have created a variable for this since we're using it more than once but now we just hard coded again fine for now email messages to processed that was the retrieved emails we got it up here on this list of main messages retrieved email messages what do we want to do we want to move the email messages to an email folder and this one will be the Inbox and here you need to create this subfolder in your outlook so processed invoices us we'll click save now let's go try to run this again I'll fast forward to the end so we can see that we also solved this that's it and if we go to Outlook in and I moved into processed invoiced these emails have got moved from invoices to processed invoices let's just we still need to do one thing so I'll move it back into invoices and minimize it I also want to be sure I don't overwrite the files when they are moved to processed so right before the move files have a rename files we want to add date time to it so it will be unique it looks like this so rename files just here the file to rename that will be the current PDF the rename scheme that will be at date time where do we want to add the date time that will be before the name the separator I want that to be underscore but you can pick whatever you want then we want to specify the format it's fine with years month and days days again this is.net custom date time format then I want the hours minutes and seconds this means that we will not override anything because we we don't processed the files within one second if file exists that do do nothing but this will not happen so that's fine now please save it back to the Q and PDF so we can just use it here in the move files so here we just rename it and we use it here let us save it and again I hope you're still with me because you will learn a lot of things in this exercise especially how to build an entire project now run the solution once more what we're going to see now is that we are creating unique file names and we're not going to overwrite anything and we're not going to have them renamed one two three four or all these things so we will create timestamp assignment then it will be much more easier to find once we want to get back to them as a business user now let's fast forward and check it that's it and if I move what I want to check now is in the processed folder you can see that we now add timestamps to these that means that each file is unique we're never going to confuse ourselves or overwrite anything let me minimize this today's important takeaway is that we break everything down to small bytes and solve the problems one by one we run our solution over and over to see that this little added step works as well now you should Network and solve power automate problems with more than 7000 RPA developers your invitation is right here it's free see you there

Channel: Anders Jensen

Views: 37,570

Keywords: power automate desktop, anders jensen, power automate desktop complete project, pdf extraction in power automate desktop, power automate desktop pdf, pdf in power automate desktop, complete power automate project, full power automate desktop project, power automate desktop project, data entry in power automate desktop, data into applications power automate desktop

Id: BxkfdK8rCRU

Length: 73min 43sec (4423 seconds)

Published: Wed Mar 08 2023