Expert guide

Extract PDF Invoice Data into Excel with AI

Stop retyping supplier invoices: when Power Query is enough, when you need AI, and how to trust the numbers

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: if all invoices are digital PDFs from a few suppliers, use Excel's Get Data → From PDF and save one Power Query per layout. If invoices come from many suppliers or include scans, use an AI document model that returns fixed fields (supplier, number, date, due date, subtotal, tax, total, line items) as JSON, load them into an Excel table, and automatically flag any invoice whose lines do not add up. We build that end to end in AI workflow automation projects.

Accounts payable in a small or mid-sized business usually looks like this: invoices arrive by email as PDFs, someone opens each one, and types the supplier, invoice number, dates and amounts into a spreadsheet or accounting system. It takes three to five minutes per invoice when nothing goes wrong. Typos in invoice numbers cause duplicate payments; a missed due date costs a late fee.

The good news is that this is one of the most mature uses of AI in business. The less-good news is that "upload PDF, get spreadsheet" demos skip the parts that matter in production: inconsistent layouts, scans, multi-page invoices, and the need to prove every number is right.

First, Know Which Kind of PDF You Have

PDF typeHow to tellBest tool
Digital, consistent layoutYou can select text; same supplier template every timePower Query (Get Data → From PDF)
Digital, many layoutsText is selectable but every supplier is differentAI document extraction
Scanned or photographedText cannot be selected; looks like an imageOCR plus AI, or an AI vision model
E-invoice (XML, UBL, PEPPOL)Structured file attached or embeddedImport the XML directly; no extraction needed

Option 1: Power Query for Digital, Repeatable Layouts

  • Data → Get Data → From File → From PDF, and pick one invoice.

  • The Navigator lists detected tables and pages. Choose the line-item table and click Transform Data.

  • Promote headers, remove the totals rows, set data types.

  • Turn the query into a function and point it at a folder so every PDF in the folder is processed on Refresh.

  • Add columns for the file name and the invoice number you extract from the page text.

This works very well for one supplier who sends the same template every month, such as a utility, a freight carrier or a wholesaler. It breaks the moment the supplier changes the layout, and it cannot read scans.

Option 2: AI Extraction for Everything Else

Define the output before you choose a tool

Whatever model or service you use, decide the exact fields first. A fixed schema is what turns AI output into rows you can load, validate and post.

{
  "supplier_name": "string",
  "supplier_tax_id": "string or null",
  "invoice_number": "string",
  "invoice_date": "YYYY-MM-DD",
  "due_date": "YYYY-MM-DD or null",
  "currency": "ISO code, e.g. USD",
  "po_number": "string or null",
  "subtotal": number,
  "tax": number,
  "total": number,
  "lines": [
    { "description": "string", "quantity": number,
      "unit_price": number, "line_total": number }
  ],
  "confidence_notes": "anything unclear or unreadable"
}

The extraction prompt

Extract data from this supplier invoice into the JSON schema provided.
Rules:
- Copy values exactly as printed; do not calculate missing values.
- Dates in YYYY-MM-DD. Amounts as plain numbers without currency symbols.
- If a field is not present, use null. Never guess an invoice number.
- If the invoice spans several pages, include all line items.
- Put anything illegible or ambiguous in confidence_notes.
Return only JSON.

"Do not calculate missing values" is important. You want the model to report what the document says, then let Excel do the arithmetic as a check. A model that quietly fills in a missing tax figure hides exactly the problems you need to see.

The Validation Layer: Never Skip It

Load each invoice header into a Headers table and each line into a Lines table, linked by a generated Invoice ID. Then add check columns to Headers:

Lines sum check:
=ABS(SUMIFS(Lines[line_total], Lines[invoice_id], [@invoice_id]) - [@subtotal]) < 0.02

Total check:
=ABS([@subtotal] + [@tax] - [@total]) < 0.02

Duplicate check:
=COUNTIFS([supplier_name], [@supplier_name], [invoice_number], [@invoice_number]) = 1

Status:
=IF(AND([@[Lines sum check]], [@[Total check]], [@[Duplicate check]],
        [@confidence_notes] = ""), "Ready", "Review")

In practice most invoices pass and go straight to Ready. The Review queue holds the handful that need a person: a faded scan, a credit note, a supplier that shows prices including tax. That is the difference between automation you can trust and a demo.

An End-to-End Workflow

  • 1. Capture

    A shared mailbox rule or Power Automate flow saves every PDF attachment into an Inbox folder.

  • 2. Extract

    Each new file is sent to the AI model; JSON results are saved alongside the PDF.

  • 3. Load

    Power Query or VBA loads the JSON into the Headers and Lines tables.

  • 4. Validate

    Check columns mark each invoice Ready or Review.

  • 5. Approve and post

    Ready invoices are approved and posted to the accounting system or a payment schedule; the PDF moves to a Processed folder named by supplier and invoice number.

For teams that need approvals, payment status and supplier history, the same data often lives better in an Access or SQL database than a workbook. Our Access invoice and payment tracker is built for that.

Tools Compared

FeatureStrengthLimitation
Power Query From PDFFree, in Excel, repeatableDigital PDFs only; breaks on layout changes
General AI model via APIHandles any layout, scans, multiple languagesNeeds schema, validation and key management
Specialised invoice OCR servicesPre-trained on invoices, confidence scores per fieldPer-page pricing; still needs your validation rules
AP automation platformsApprovals, payments, accounting syncMonthly cost; may be more than a small team needs

Data Protection Basics

  • Use business AI services that do not train on your documents.

  • Supplier invoices may include bank details: restrict who can see the extracted data.

  • Log what was sent and when, and keep the original PDF as the source of truth.

  • Never auto-change supplier bank details from an extracted invoice; that is a common fraud route.

Beyond Invoices

The same pattern, a fixed schema plus AI plus validation, works for purchase orders, delivery notes, bank statements, insurance certificates, timesheets and lab reports. If those documents end up in Access rather than Excel, see scanning documents into Access with AI. For the categorisation step after extraction, see categorising transactions with AI.

FAQ

PDF invoice extraction FAQ

Power Query, AI and accuracy questions

For digital PDFs with a consistent layout, Excel's Get Data → From PDF (Power Query) can pull tables directly. For invoices from many suppliers with different layouts, or scanned paper, use an AI document model that reads the invoice and returns fixed fields such as supplier, invoice number, date, totals and line items, then load those results into an Excel table.

Yes. Modern AI models can read PDFs and images and return structured data. For business use, define a strict output schema, validate totals against the line items, and keep a human review step for invoices where the numbers do not reconcile. Do not upload supplier invoices to consumer accounts without checking your data policy.

No. Power Query's PDF connector reads text and tables embedded in digital PDFs. A scanned invoice is an image with no text layer, so Power Query finds nothing. Scans need OCR or an AI vision model.

Header fields such as supplier, invoice number, date and total are extracted very reliably from clear documents. Line items, handwritten notes, faded scans and multi-page invoices are where errors appear. Automatic checks, such as line totals summing to the subtotal and tax plus subtotal equalling the total, catch most of them.

If someone spends more than a few hours a month keying invoices, or if keying errors have caused overpayments or late fees, automation usually pays back quickly. Above a few hundred invoices a month, a dedicated accounts payable tool may be the better fit; below that, an Excel or Access workflow is often simpler and cheaper.