Extract tables from PDF - Microsoft Power Automate for Desktop Tutorial

hi guys welcome to ms power automate in today's video we will learn how to extract tables from pdf in power automate desktop so if i search for extract tables from pdf firstly i would like to need to understand what is the output of this extracted pdf tables so first of all let me look for the file here on the advanced option don't need to change anything and click save okay so from here let me open my invoice so it's actually looking from here right but let's take a look what is the output of this variable so let me use a display message and i hover to here it says there is a list of pdf table info so if i expand this it show me count so what i am we are not sure what what is this value right so if i click save and i try to run it let's see what is the output so it returns me table one two three so it's actually telling me that in this pdf i have three table and this three table i have in page one i have one row and two columns so we are not really sure what are those values right so to know to get the values what we can do is we can use the for each loop okay in this extracted pdf table i want to rename this as current table right okay so if i move it inside here and let's take a look what is the current table variable is called pdf table info so if i expand this actually there's more variables here available which is we can get the data table or we can also get the data table the columns and there's also a column header all right so over here there's a lot of information we can get here but let me just take a look at what is the data table output so if i click save so the first one it assumes is extracted as contoso electronics and invoice 1001 all right and then move on it actually extracted this as a table without the column name and continue text shipping and total all right so now we are going to enhance this such that for each of this table we will write into excel so what we can do from here right i want to make this as a fresh start so always i want to terminate a process and i want to terminate excel okay so from here after that i want to launch excel if a blank document make instance as visible and then i click save and of course i will when i launch this excel i want to maximize the window so i would like to use a reset window state and i based on by window instance and i select excel instance and i set it as maximize all right so now i want to for each of this table i want to have their own sheets right so if i launch a new excel it will automatically create for me uh a sheet name called sheet1 right but i need to rename that sheet so let's take a look what do i mean by that let me use a message box here so display message okay so let me open here that's this cheap one right so we are going to enhance this such that if for each of this table right i want to set a variable called table index table index and i set it as 1 okay so what i do here is that i want to use this and call it as a sheet name and i want to declare the sheet name as table followed by the table index all right and of course at the end of the loop i need to increase the variable plus one okay and then from here i would like to check if the table index is equals to 1 then i want to rename the worksheet we name the excel worksheet based on the first index which is zero sorry it should be start at index one okay and then the worksheet name i want to call it as sheet name okay and then else instead of rename the worksheet i will add the worksheet add new worksheet and the worksheet name as sheet name and i will add the worksheet to the last okay so now let's take a look here if i run this flow okay let me open here so table one okay if i click on the message box again table two table three right so now the first step done ready let's go back here and click stop okay so now we are going to make it uh such as we want to write the column name okay so from here what i gonna do is i need to set active excel worksheet and then based on the name i will use as sheet name save so this one done let's minimize this okay so from here what i will do next is i do a loop okay so over here there are two ways i can use for each for each let's try 30 bro no sorry x for each table here columns here i can do this and call it as current column but i want to get the index here so it's a bit that i need to create another variable right so which is a bit time consuming so i'm going to put it such as i'm using loop because loop will provide me the loop index so over here i will use a loop start from one okay n two data table the columns dot count okay increment by one and i want to call it as column index okay so why do i do this because later on uh when i want to get i want to keep on writing here here here here right based on the color name but we are going to use the get first free row and column so to get first free row it first first free column will always give me starting from b c d e f g and so forth right they will not give me a so what i can do here as an alternative i will set a condition here if column index is equals to 1 then i will write the excel worksheet here as value to right here i will use the column name right but from here because i'm using a loop what i can do is from this current table i get this data table and i select this column okay so from here i want to get the index so from here i will use the column index but -1 why because they will this table and the columns the loop will start from zero it will not start from one okay so from here that i would like to write on a specified cell and then the column i want to write at column a and we show one else i will use get first free column okay and then i will use right on excel the value to right is the same thing but the column here we will use first free column okay so now let's save this and run this flow okay so let me open here now i have the column name okay continue item quantity unit cost amount okay so now if i go back here as you can see here you need to understand um not all table will be giving me the exact column name so over here if i take a look here these are some of the things where you need to do additional checks to to to to fit the needs of of your extracting of this table so different pdf you have a different output so you need to cater this accordingly right okay so let me go back here and stop this okay so from here what i want to do here is that let me remove this i want to write the all the tables starting from a2 onwards all right so if i go back to here and i copy this and paste it right above the increased variable i want to write the entire table value into my column a by starting from row 2 right next we will then close the excel and i want to save it as extracted output so if i run this flow table 1 has populated now it's table 2. and table 3 and then check excel all right and then let me click ok and finish so that's all for this exercise um what you can see here is that you can do some enhancements like um you can get a list of files in a folder and then for each of the pdf files you extract tables from pdf and then over here when closing the excel you can put it into a dynamic naming convention right so if you're keen to learn more do remember to subscribe our channel as we have constant updates and tutorial videos on microsoft power ultimate desktop thank you

Channel: MS Power Automate

Views: 21,087

Keywords: microsoft power automate desktop, microsoft power automate, power automate desktop, power automate, power automate desktop tutorial, power automate tutorial, power automate desktop for beginners

Id: GK3IoIvckvY

Length: 14min 34sec (874 seconds)

Published: Mon Mar 07 2022