500 free credits, no card. Try Smalt AI →

Operations

How do I pull data from a folder of invoice pdfs into one Excel spreadsheet

Use Excel's Get Data > From File > From Folder to combine the PDFs with Power Query, which reads the tables in each file into one sheet. Best on text PDFs.

Smalt AI team ·

Put the PDFs in one folder, then in Excel choose Data > Get Data > From File > From Folder and combine the files with Power Query. The PDF connector finds the tables in each invoice, and the combine step stacks them into one table you can load to a sheet. This works when the invoices share a layout and contain real text rather than scanned images.

Combine the folder with Power Query

Sources checked 2026-11-03; Microsoft’s pages change.

Microsoft’s PDF connector documentation says the connector is generally available in Excel, Power BI and Power Query Online, and that it is built on Pdf.Tables, a function that returns the tables it finds in a PDF. For several PDFs, the same page points to the Folder connector, which is the route below.

  1. Copy the invoices into a single folder. Nested subfolders are picked up too.
  2. In Excel, go to Data > Get Data > From File > From Folder, then browse to the folder and select Open. Microsoft’s Folder connector page describes this path and the three buttons that follow.
  3. Choose Transform Data first, not Combine, so you can clean up the file list before anything is combined.
  4. Filter the Extension column to .pdf. Microsoft’s combine files overview recommends this, because a stray Excel file or thumbnail will break the combine.
  5. Select Combine Files on the Content column. Power Query lists the tables it found in the example file; pick the one that holds your line items.
  6. Check Skip files with errors if you want one bad PDF left out instead of stopping the query, then count rows against files, because a skipped file does not announce itself.
  7. Select Close & Load, or Transform Data to keep shaping the result.

The example file defaults to the first file in the list, and you can change it. Microsoft’s overview explains that the combine step creates a Transform Sample file query and a function query under Helper queries. Any edit to the sample query applies to every file. This is where you promote headers, rename columns and drop unneeded rows, once, for the whole folder. Excelguru describes the same workflow, ending in one table that refreshes when new PDFs are added.

The combine step normally adds a source file name column, which you should keep so every row traces back to its invoice.

Tune the PDF connector

The PDF connector has options you can set in the formula bar or advanced editor: StartPage, EndPage, MultiPageTables (on by default), EnforceBorderLines and Implementation. The connector documentation says large PDFs can time out and suggests reading page ranges. It also says wrapped rows may need cleanup with Table.FillDown or Table.Group.

As a rule of thumb, an invoice with a header block above the line items may show the header values as a separate table in the Navigator. In comments on a 2020 Excel Campus post, author Jon Acampora said that if page or table numbers change between files you can adjust the table in the query’s Navigation step, and that complex dashboard-style layouts may parse inconsistently.

The result is line-item level: one row per line on each invoice, not one row per invoice. For one row per invoice, group by the source file name and sum the amounts (Group By or a pivot), or build a second query from the header table and join it to the line items on file name.

When the invoices come from different vendors

The combine step treats every file the same way, based on the example file. Microsoft states that files should share the same type and structure, including the same columns. One vendor’s table with six columns and another’s with eight will not line up.

We’d split invoices into one subfolder per vendor layout, build one query per folder, and append the results. Each query then has a representative example file.

Microsoft’s overview also warns that a Change column type step can cause step-level errors, so convert text amounts in a separate step after checking the data.

Doing it with an AI coworker

If you would rather not build and maintain the queries, upload a .zip of similar invoices to Smalt and get one workbook. Ask for what you want in the sheet, for example one row per invoice with the vendor, invoice number, date and total. It can miss things on scans or mixed layouts, so check the totals against the PDFs. Related pages cover spreadsheet use cases and how operations teams use Smalt.

Because the upload is meant for similar documents, the vendor-layout advice above still applies: zip each layout separately.

What to check before you rely on it

Run five of your own invoices through before you load the whole folder.

ProblemSignWhat to do
Scanned invoicesExcelguru says an empty Navigator points to a scan; in a PDF viewer, the whole page selects as one blockRun OCR first (Excelguru names Adobe Acrobat Pro, Smallpdf and Microsoft Office Lens) and re-import. It calls 300 DPI or higher more reliable
Missing invoicesFewer rows than expected, especially with Skip files with errors onCompare row counts with your invoices and line items, then check three random invoices figure by figure
Totals that don’t matchSummed line items differ from the stated totalAdd a per-invoice sum column. As a rule of thumb, a wrapped row split or a table cut at a page break is the likely cause
No From PDF optionThe command is missing under Get Data > From FileCheck your Excel version. Microsoft’s import guide lists .NET Framework 4.5 or later but doesn’t say which versions include the connector, or whether Excel for Mac has From PDF, so check yours
DuplicatesThe same invoice saved twice under different namesPower Query imports it twice. Group on vendor and invoice number before anyone pays from the sheet

Smalt is an AI coworker that takes jobs like this end to end and hands back the finished file.