Extract Data From Multiple PDF Files to Excel at Once

Quick Answer: Put the PDFs in one folder, then in Excel choose Data > Get Data > From File > From Folder, filter the file list to .pdf, and combine. Power Query builds one query from a sample file and applies it to every PDF, stacking the results into a single table you can refresh when new files arrive. It works well when every PDF has the same layout; mixed layouts and scans need a dedicated converter.

Excel can extract tables from a whole folder of PDFs in one go, without macros or add-ins, using Power Query. You teach it how to read one file, it repeats that recipe for every file in the folder, and a refresh picks up anything you add later. The catch is that the recipe assumes every PDF is laid out the same way.

Will Power Query work for your PDFs?

Check three things before you build anything:

• The PDFs contain real text. Open one and try to select a word. If you cannot, it is a scanned image, and Power Query's PDF connector has nothing to read. You will need OCR first.
• The layouts match. Monthly invoices from the same supplier, statements from the same bank account, or reports exported from the same system are good candidates. A folder of invoices from twenty different vendors is not.
• The table is detected. Load one file through Excel's PDF connector (it sits under Get Data) and look at the Navigator. Power Query lists the tables and pages it found. If your data shows up as a clean table, the folder approach will work. If it is split across odd fragments, the folder approach will multiply the mess.

Microsoft's PDF connector documentation notes that to import several PDFs at once you should use a multi-file connector such as Folder, which is the method below.

How do you combine many PDFs into one Excel table?

1. Make a dedicated folder. Put only the PDFs you want to combine in it. Microsoft's guide to importing a folder of files warns that every file in the folder and its subfolders is included.
2. Connect to the folder. In Excel choose Data > Get Data > From File > From Folder, browse to the folder and open it. A preview lists every file with columns such as Name, Extension and Content.
3. Choose Transform Data rather than Combine. That opens the Power Query Editor with the file list, so you can filter it before combining.
4. Filter the Extension column to .pdf. This keeps stray files out, a step the combine files overview recommends.
5. Combine. Select the Content column and choose Combine files (on the Home tab, or the icon in the column header). Power Query opens the Combine files dialog using the first file as the example; you can pick a different example from the Example file drop-down and tick Skip files with errors if a few files are unreadable.
6. Pick the table. In the dialog's navigator, select the table (or page) from the example file that holds your data. Power Query will look for the equivalent table in every other file.
7. Clean once, in the sample query. Power Query creates a helper query called Transform Sample file. Promote headers, remove repeated header rows, delete totals and change data types there. Every change flows to all files.
8. Load the result. The combined table lands on a worksheet, with a column showing which file each row came from.

Why the Source.Name column matters

The combined output keeps the file name on every row. Keep it. It lets you trace a strange number back to the PDF it came from, and if your file names hold dates or account numbers you can split them into their own columns.

How do you refresh when new PDFs arrive?

Drop the new PDF into the same folder and refresh the query. Power Query rereads the folder, runs the sample query recipe on every file and rebuilds the table. Nothing has to be redone, which is the real automation here.

Two habits keep refreshes reliable:

• Keep the folder path stable. Moving the folder breaks the source step; if you must move it, edit the first step of the query.
• Name files consistently so that sorting by Source.Name puts them in order.

What goes wrong with batch PDF extraction?

Most failures trace back to the same few causes.

The last row comes from Microsoft's own advice for large files: the connector's Pdf.Tables function accepts StartPage, EndPage and a MultiPageTables option that controls whether similar tables on consecutive pages are joined.

Can you do this for bank statements?

Sometimes. A folder of statements from one account at one bank, all text-based, may combine cleanly. In practice bank statements are hard on generic table detection: transactions wrap onto two lines, a running balance column appears only on some rows, page one has a summary box, and the bank redesigns the layout every few years, so January's recipe fails on December's file. Debits and credits may sit in separate columns or share one with "CR" suffixes.

For statements specifically, these routes are usually less work:

• The bank's own export. Many banks let you download CSV for recent months. CSVs combine through the same From Folder steps with far less cleanup.
• A statement converter with batch upload. QuickBankConvert's Pro and Business plans accept several statement PDFs in one batch and return one row per transaction with date, description, signed amount and balance, which you can then stack in Excel. Lower plans handle files one at a time; see pricing for the limits.
• Merge after conversion. Once each statement is a CSV, combining them is a standard folder import; the guide to merging multiple CSV bank statements covers deduplicating overlapping date ranges.

The statement-specific batch workflow is in batch converting multiple bank statements.

Other tools for batch PDF extraction

• Adobe Acrobat exports a PDF to an Excel workbook, one file at a time from the interface. It is good for one-offs, but you then still have to combine the workbooks.
• Python with a PDF table library suits developers who need fine control and can maintain a script.
• Document AI services from cloud providers extract fields from invoices and forms in bulk, at a per-page cost, and need setup.
• Power Automate can watch a folder or mailbox and pass PDFs to an extraction step, if your organization already uses it.

For a single PDF rather than a folder, the simpler walkthrough is how to import a PDF into Excel.

Related guides

• Statement batches from upload to spreadsheet
• One PDF into Excel with Get Data
• Stacking many CSV statement exports
• Hands-off statement processing