The data did not come in as cleanly as we had hoped, but it’s a good start. We saw that the table we needed exists on page 4, so we select that page and click Transform Data. Let’s use Power Query and the PDF connector to solve the problem.Īs witnessed in the previous example, we are presented with a list of every table and every page in the PDF file. If we attempt to highlight and copy/paste the table into Excel, you have probably already guessed what the results will be. Using the Q2 2020 Financial Summary from Tesla, we discover a table on page 4 that has information we need for an Excel report. If the PDF were to be updated with additional years of statistics, the user needs to merely right-click on the extracted table and select Refresh to receive the updated information. The new table of PDF information can be used to drive other Excel objects, like charts and Pivot Tables.

Select Close & Load to send the extracted data to an Excel Table.

The data will be brought into the Power Query Editor where it can be cleaned and/or modified to fit your output needs.

Select the table or page and click Transform Data.

If you are unsure about which listed table contains the needed information, you can single-click any item in the left-hand list to display a preview of the item’s contents. In most cases, it is best to select the table as it will negate the need to later sanitize the page of unwanted information. If the needed data is in a table, you can select either the table or the page that holds the table. The Navigator window displays a list of every “proper” table in the PDF as well as every page.

Browse to the folder that contains the PDF, select the PDF, and click IMPORT.

Select Data (tab) -> Get & Transform (group) -> Get Data -> From File -> From PDF.Start Excel to create a blank workbook.To use the connector, we perform the following steps ( the example uses Excel 365). Power Query has a built-in connector used to extract information from PDF files.

We could spend time rearranging and fixing the pasted data, but we don’t have that kind of time and it would negate the second requirement of an easily updatable Excel report. We could try to just highlight the relevant data in the PDF, then copy and paste the data into Excel, but it’s likely not to go as well as we had hoped.

Suppose we have a safety report with performance data stored on the second page in a table format.

Data extraction from pdf to excel