Automate extract all PDF tables separately into different Excel sheets with column names using PAD

hello everyone today i will be showing you how to extract all the tables in a pdf file into excel so the pdf files can look something like this i have a sample here with two tables um the first table goes to the second page and then there's another table in the third page with a different column name you can see here segment two country two product two and the first table will only have a segment without the number two i will extract each table into two tables right this is the first table extracted with two page of data and this one is the second table in a separate sheet at the same time i am going to show you how to read these extracted pdf tables in more detail my name is hao zhung i'm here to help you to use office work tools such as excel and power automate if you think my videos are helpful please help me by subscribing to my channel thank you [Music] okay let's start the first step is extract tables from pdf so this is the pdf file link pages to extract all in advance you have to enable merge tables that cross page margins and first line contains column names then you launch excel with a blank document make instance visible the rest you can leave it as default um focus window i like to do this because i want to see what uh how power automate is doing with accept the excel file the find window mode by title and or class window like title book one dash excel set window state by title and class book 1 excel maximize then i am going to set variable um my variable name is table number value is zero the reason i'm doing this is because like what i have said earlier i'm going to show you um how to read these extract pdf tables so i'm going to go to the flow variables here and i'm going to click on the more actions the triple dot and i'm going to will right over here you can see that i have two tables in the pdf that i will be extracting so click on more right you can see um each each item has its property dot data table and then it's saying that i have 91 rows and six columns then if i click on more again you can actually see all the data in on in the first table so this is very very interesting and then you can see its name as extracted pdf tables 0 dot data table so this 0 is actually it's related to the index 0 index of the list of pdf table info so this 0 right i will make it a dynamic that's why i i name it as a table number so so so for the first variable i'll put table number and the value is 0 because this extracted pdf table index 0 is the first table right this will never change so index 0 is always the first table then i use for each value to iterate extracted pdf tables store into current item so this will this will run for each item in these extracted pdf tables all right then i add a new worksheet in the excel instance new worksheet name will be the table number add worksheet as last worksheet right then you write to excel worksheet um excel instance um is excel instance then value variable value to right okay i will probably copy this into the description so it's n percent extracted pdf tables right which is this extracted media table and then open bracket table number close bracket which is the table number index which on the first run will take s0 then dot data table right why i dot data table is because over here if you remember i click on more right the properties dot data table which will show you rows 91 rows and six columns right but this data table doesn't have a column either so that's why i put dot column header row then and percent on right mode is on currently active cell right next um this is just to extract the column header so next i next i will ask data i will ask power automate to get first free row on column from excel worksheet so that i can paste the data table under the column name so excel instance is excel instance column is 1 variables produces first free row on column right then next is i'm going to write the table now write to excel worksheet excel instance so n percent extracted pdf tables and open bracket table number close bracket dot data table um see i don't i don't have the column hidden anymore so this will just just extract the table only right mode on specific cell column one right and then next available uh row is this first free row on column which we have extracted just now in step number nine right then this is a loop so um i will increase the table number by one so that it will extract the second table the column header and the data table into um sheet number two sheet number one so which is the next um table so so you will loop right then table you will add new worksheet table number will be one so it will be a new worksheet and you you you will extract the second table so um after the end however this i put a label display message right you can skip this step but this is this is cleaner um then then i use this this play message window um i will probably put all right this is just to show that the process has been completely completed successfully essentially first button keep message box always on top um yeah so i save this so i'm going to close this pdf file as well so now i'm going to run it hopefully it works so it will launch excel yep so you will focus on the book one maximize and then it will go to [Music] uh it will copy the second table and a first table and second number and then it says finish i hope this video has helped you if you have any questions or need help please comment below and i will try my best to get another video up to help you if you like this video please give it a like and subscribe and if you want to tip me or buy me a coffee you can use the paper link i hope this video helps you i'll see you in the next video bye

Channel: Power Automator

Views: 10,546

Keywords: power automate, powerautomate, power automate desktop, powerautomatedesktop, pad, february 2022, feb 2022, update, version, extract, tables, pdf, excel, Sheet, Sheets, Separately

Id: y5pJKDgQsg4

Length: 10min 57sec (657 seconds)

Published: Wed Feb 23 2022