How to Extract Data from Multiple PDF Files to Excel (3 Methods)

Dataset Overview

We are going to use the below PDF file dataset to demonstrate the extraction of data from PDF to Excel sheets.

Method 1 – Using the Power Query Tool to Extract Data from Multiple PDF Files to Excel

To open three PDF files and load all the tables from each file into an Excel worksheet, follow these steps:

• Go to the Data tab and click on Get Data.

• Select From File and then choose From Folder.

• Browse to the folder where your PDF files are saved and click Open.

• In the Power Query window, you’ll see the loaded PDF files listed in the Name column.

• Click on the “Combine” option and choose either Combine & Transform Data or Combine & Load.

• A new query window will open, allowing you to preview and select tables from each file.
• In that window, you can cycle through files by the Sample File window.

• You can choose a table of a particular file for the preview.
• Click OK.

• All of the tables are now loaded into a new query window. The left-most column of the table denotes the source of the data, whether it is from Dataset_1 or Dataset_2, or Dataset_3.

• Once all tables are loaded, go to the Home tab and click Close & Load.

• In the Import Data window, select Existing Worksheet and specify the data location (e.g., $B$5:$F$29).
• Click OK after this.

• The data table will now be loaded into the specified location as an Excel table.
• Convert the table back to a range by going to Table Design and selecting Convert to Range from the Tools group.

• The loaded data is now converted into range.

• Delete the source column (e.g., cells B4:B35) to complete the extraction.

The Excel worksheet now has extracted data from multiple PDF files.

Read More: How to Extract Data from PDF to Excel

Method 2 – Extracting Data From Multiple PDF Files to Excel Using Microsoft Word

We are going to import the below PDF file into Excel.

• Open Microsoft Word and click Open from the file menu.

• Browse to the location of your PDF files and select the desired file.
• Click Open.

• The PDF file will open in Word in an editable form.

• Select the table, right-click, and choose Copy.

• Switch to your Excel worksheet, select cell B4, and right-click.
• Choose the Keep Source Formatting (K) icon to paste the table from the Word file.

• Adjust cell formatting as needed to match the PDF format.

• The below is the final image after tweaking some formatting such as text color, column width, cell color, etc.

• You can repeat the same process for other files.
• Below is the second PDF file’s data that has been extracted into the Excel worksheet.

• Below is the third PDF file’s data that has been extracted into the Excel worksheet.

Read More: How to Export Data from Fillable PDF to Excel

Method 3 – Applying Copy and Paste Tool to Extract Data from Multiple PDFs

• Open the PDF file containing the table data you want to extract.
• Press Ctrl+A to select all the data in the PDF.
• Right-click and choose Copy from the context menu.

• Switch to your Excel worksheet and select cell B4.
• Right-click again and click on the Keep Source Formatting (K) icon from the Paste Options.

• The PDF table will now be pasted into the Excel sheet.
• Note that the table may not look exactly the same as in the PDF due to fixed cell sizes in Excel.
• Apply formatting adjustments (e.g., text color, cell color, cell width) to make it match the original PDF layout.

After applying formatting, the imported table now looks like the PDF one.

Read More: How to Link PDF Form to Excel Database

Download Practice Workbook

You can download the practice workbooks from here:

Related Articles

• How to Convert PDF to Excel without Software
• How to Convert PDF to Excel without Losing Formatting 
• How to Convert PDF to Excel Table
• How to Export PDF Comments into an Excel Spreadsheet
• How to Extract Specific Data from PDF to Excel Using VBA

<< Go Back to Import PDF to Excel | Importing Data in Excel | Learn Excel