How to get extracted data from several PDF into different sheets in excel

Help Studio
, , , , ,
1

Hi
I’m trying to make automation of taking some data from PDF tables and putting it into Excel.
I would like to do one of two things, either have the name of the PDF in Column A and then all the data in one sheet or data from each PDF in a different sheet. I have made other data scraping automation where it ends in a different sheet, but that was always from a list in Excel and i don’t know what to write in the Write Range, where it’s currently “Sheet1”. FLEXFLIGHTMHL.xaml (11.9 KB)

Solving the above would be a great help, but i have a follow up question.
Some of my PDF have several data tables, but only on page 1,3,5 and so on, because there is a usage information on page 2,4,6 which isn’t relevant. Is it possible to get a data extraction from all the data tables in a pdf file?

Before hand, thank you for your help.

2

you can put the file name and that will create a sheet with that name and paste the data

Make sure to consider if that pdf are coming in from differently folders than name can be repetitive then the data will be overwritten
For this you can have a check to get the list of sheets first and then check if the current filename Exists, if yes then append a text, may be like “-2” at the end of the current filename in the loop.

This means to get the data extracted from only from 1, 3, 5?

You can do that using keyword.
the remaining sheets to be skipped can be identified on the keyword that is common to them and can differentiate them from others relevant sheets
Also if you can get a keyword to understand which pdf needs to extract the data from Alternative lages then you can even extract data from Alternative pages and proceed.To do so the for loop can have a if condition to only check for odd numbers of sheets

3

Thanks for the answer, I will try with the multiple pages.
However I don’t fully understand the first part, can I put in the directory where the files are? The files will change name every month, but have the same structure

4

Why directory/?

you needed to create a new sheet for each file → simply take the name and put it in the Sheet Name firled in Write Range, This will create a sheet with that file name.

5

But don’t i then have to make a write range for each file

6

Yes for one file you’ll require one write range activity

Are you trying to achieve writing all at once? That can be possible using vba