Power Query Problems

How to Automatically Import PDF Data into an Excel Template with Power Query

Huma Ashraf Ch Oct 10, 2026 869 views

Question details

The user wants to automate the process of extracting tables and information from a PDF file into a custom Excel template to replace time-consuming manual copy-pasting.

How to Automatically Import PDF Data into an Excel Template with Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing structured data from a PDF report into an Excel workbook using Power Query for automated updates.
Observed behavior: Currently manually copying information from a PDF into an Excel template. The goal is to set up a Power Query workflow to automatically import and transform the PDF tables.

Before you start

Ensure you have the source PDF file saved locally on your computer and make a note of its exact file path before opening your spreadsheet.

Solution 1Recommended

Import PDF Tables Using Excel's Built-in Get Data Wizard

Use Excel's native Power Query data extraction tools to visually select, extract, and load tables from a PDF document without writing any code.

This is the most straightforward method for most users. It utilizes Excel's built-in PDF connector to analyze the document, detect tabular structures, and let you preview the data before importing it into your workbook.

Access the Get Data Menu

Open Excel, navigate to the Data tab on the ribbon, click on Get Data, select From File, and then choose From PDF.

Select Your PDF Document

Browse to the folder containing your target PDF file, select it, and click Import to open the Navigator window.

Choose the Correct Tables

In the Navigator window, browse through the detected tables and pages on the left panel. Check the box next to the specific tables you want to import.

Load or Transform the Data

Click Load to insert the data directly into your Excel template, or click Transform Data to open the Power Query Editor if you need to filter rows or split columns first.

Pro Tip: If you anticipate the PDF format remaining consistent, you can reuse this query later. Simply overwrite the old PDF file with the new one and click Refresh All in Excel.

Solution 2

Automate Import using Power Query Advanced Editor (M Code)

Use a custom Power Query script to import, flatten, and transform PDF data automatically, which is ideal for highly complex or custom templates.

WPS Office Solution

Easily Extract PDF Data to Spreadsheets with WPS Office

Instead of setting up complex Power Query connections, you can use WPS Office's built-in PDF to Excel conversion tool. It intelligently extracts tables and text from your PDF directly into a structured spreadsheet with just one click.

1. Open Your PDF in WPS Office: Launch WPS Office and open your source PDF document using the built-in PDF reader.
2. Select PDF to Excel: Navigate to the Tools tab on the top ribbon and click on the PDF to Excel button.
3. Convert the Document: Choose your desired page range and language options, then click Convert to instantly generate a fully formatted Excel spreadsheet containing your data.

One-click PDF to Excel conversion without complex scriptsHighly compatible with Microsoft Excel formats (.xlsx and .xls)Maintains the original table formatting and structure perfectlyFree, lightweight, and incredibly easy to use

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't Power Query recognizing the tables in my PDF?

Power Query's PDF connector relies on text-based PDF structures. If your PDF is a scanned image, Power Query will struggle to identify tables. In such cases, you will need to process the PDF through Optical Character Recognition (OCR) software first.

Can I import multiple PDF files at once into an Excel template?

Yes. Instead of selecting 'From PDF', you can go to Data > Get Data > From File > From Folder. Point it to a directory containing your identically structured PDFs, and Power Query will help you combine them into a single dataset.

Does my Excel template update automatically if the source PDF changes?

It can be updated effortlessly. If you save the new PDF over the old one using the exact same file name and folder path, simply open your Excel file and click 'Refresh All' on the Data tab to load the new data.

Excel & Data Problems