Excel with Microsoft 365 has a PDF connector which enables us to extract data from PDF files. The beauty of this method is that the extracted data will automatically update for the changes in the source data.
Let’s see how to extract the Tables in a PDF file using Power Query in Excel. Following is a 3 page PDF file which contains a fair amount of literature and 3 Tables containing data.
To extract the data tables from the PDF file shown above,
Create a New Excel Workbook > go to the Data tab of the Excel Ribbon > Get data > From file > From PDF
Import Data dialog is activated. Use this dialog to select the PDF file.
Another dialog called Navigator is activated with the list of ‘Tables’ and ‘Pages’ in it. Click on an object for its preview.
Here, I have selected Table no. 2 on the second page of the PDF file and on the right side we have its preview.
At the bottom portion of the Navigator dialog, there are two options called Load and Transform Data.
‘Load’ will load the selected items into different Excel Worksheets. To enable multiple selection, mark the checkbox called ‘Select multiple items‘.
But if you want to make changes in the data before loading it into a worksheet, click on ‘Transform Data’.
Here, I have selected 3 Tables and will go for the Transform Data option.
All 3 Tables are loaded into the Power Query Editor of Excel.
Before loading these Tables into the Excel Worksheets, I will make some changes in the second table.
1. Will remove the column containing Year
2. Convert the name of the directors into Upper Case
Right-click on the column header of the column to be removed (Year), select Remove.
Right-click on the column header of the column called Director > Transform > UPPERCASE
Now, to load these 3 tables into Worksheets, go to the Home tab of the Power Query Editor > click on Close & Load
Tables are loaded into 3 different worksheets. On the task pane called Queries & Connections you can see the number of records in each Table.
As I mentioned earlier, the extracted data will automatically update for the changes in the source data.
In the PDF file used here, the first table has 23 records, second table has 8 records and the third table has 24 records.
I will now replace this PDF file with an updated version, which contains tables with more records.
Table 1 – 25 records, Table 2 – 10 records and Table 3 – 30 records
While replacing, make sure that the files are having the same name.
After replacing the file, go to the Data tab of the Excel ribbon and click on Refresh All.
The output tables in the worksheets will be updated accordingly. In the current worksheet, you can see that 2 more rows have been added.
Details on all tables are displayed on the Queries & Connections task pane.