Python Doc & Data Automation

Process Invoice PDFs from Email into Excel

Accounts payable wants every supplier invoice that arrives by email listed in invoice-register.xlsx — supplier, invoice number, date, net, VAT and gross — before anyone keys it into the finance system. The attachments already land in inbox/incoming/ thanks to the email intake job. The first extraction script reads them, but the register fills up with problems: the same invoice twice because a supplier re-sent it, dates parsed as the wrong month for US-format suppliers, totals of 1.240,50 read as 1.24, a scanned invoice with every field empty, and a register file that corrupts when the job runs while someone has it open in Excel.

Root Cause

An invoice pipeline joins four steps that each fail independently, and a script that treats them as one hides every failure inside a row of blanks. Extraction depends on each supplier's layout, so one generic pattern mis-reads some suppliers and misses others. Normalisation depends on each supplier's locale — day-first or month-first dates, comma or dot decimals — which cannot be guessed from the numbers alone. Validation is what separates "extracted" from "correct", and without it a wrong total looks exactly like a right one. And the output step writes to a file people also open, where duplicates and concurrent access must be handled deliberately. Structured as separate stages with a quarantine for anything that fails a stage, the job becomes trustworthy: every PDF ends in the register or in a folder with a reason.

Minimal Diagnostic

Profile the incoming PDFs before writing extraction rules: which have a text layer, which supplier sent each, and whether the key labels are present. This tells you how many layout rules you need and which files need OCR.

# pip install pdfplumber "pandas>=2.2"
import re
from pathlib import Path
import pandas as pd
import pdfplumber

INCOMING = Path("inbox/incoming")
LABELS = {"invoice_no": r"invoice\s*(no|number|#)", "date": r"invoice\s*date|date\s*of\s*issue",
          "total": r"(total\s*(due|amount)?|amount\s*due|gross)"}

def profile(folder: Path) -> pd.DataFrame:
    rows = []
    for pdf_path in sorted(folder.glob("*.pdf")):
        try:
            with pdfplumber.open(pdf_path) as pdf:
                text = "\n".join((p.extract_text() or "") for p in pdf.pages[:2])
        except Exception as exc:
            rows.append({"file": pdf_path.name, "error": str(exc)})
            continue
        rows.append({
            "file": pdf_path.name,
            "sender_domain": pdf_path.name.split("_")[1] if "_" in pdf_path.name else "",
            "chars": len(text),
            **{k: bool(re.search(v, text, re.I)) for k, v in LABELS.items()},
        })
    return pd.DataFrame(rows)

if __name__ == "__main__":
    report = profile(INCOMING)
    print(report.groupby("sender_domain")[["chars", *LABELS]].agg(["mean"]).round(2).to_string())

                        chars  invoice_no  date  total
sender_domain           mean      mean     mean  mean
contoso-example       1843.0       1.0      1.0   1.0
fabrikam-example         0.0       0.0      0.0   0.0
northwind-example     2210.5       1.0      0.5   1.0

Fabrikam sends scans with no text layer — they need OCR or manual entry. Northwind uses two layouts, only one with an "Invoice date" label. Contoso is consistent. That is three supplier rules and one OCR route, not one universal regex.

Invoice pipeline stages PDFs from the intake folder are extracted with a supplier-specific rule, normalised using that supplier's date and decimal conventions, validated for required fields, arithmetic and duplicates, and appended to the Excel register. A failure at extraction, normalisation or validation moves the PDF to a quarantine folder with a reason file instead of writing a partial row. Intake folder PDFs from email Extract supplier rule Normalise locale: dates, decimals Validate required, sums, duplicates Append register locked, atomic Quarantine PDF + reason file

Fix: Supplier Rules, Locale-Aware Parsing, Validation and a Safe Register

Define extraction as a small table of per-supplier rules, parse numbers and dates with the supplier's conventions, validate the arithmetic, and append to the register only rows that pass. Changed lines carry comments.

# pip install pdfplumber "pandas>=2.2" openpyxl
import re
import shutil
from dataclasses import dataclass
from datetime import datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path
import pdfplumber

@dataclass
class SupplierRule:
    name: str
    invoice_no: str
    date: str
    net: str
    vat: str
    gross: str
    date_format: str                  # e.g. "%d/%m/%Y" or "%m/%d/%Y"
    decimal_comma: bool = False

RULES = {                                                                    # changed: one rule per supplier
    "contoso-example": SupplierRule("Contoso Ltd", r"Invoice No\.?\s*(\S+)", r"Invoice Date\s*(\d{2}/\d{2}/\d{4})",
                                    r"Net\s*£?([\d,]+\.\d{2})", r"VAT\s*£?([\d,]+\.\d{2})",
                                    r"Total Due\s*£?([\d,]+\.\d{2})", "%d/%m/%Y"),
    "northwind-example": SupplierRule("Northwind GmbH", r"Rechnung(?:snummer)?\s*[:#]?\s*(\S+)",
                                      r"Datum\s*[:]?\s*(\d{2}\.\d{2}\.\d{4})", r"Netto\s*€?\s*([\d.]+,\d{2})",
                                      r"MwSt\.?.*?€?\s*([\d.]+,\d{2})", r"Gesamt\s*€?\s*([\d.]+,\d{2})",
                                      "%d.%m.%Y", decimal_comma=True),
}

def money(text: str, decimal_comma: bool) -> Decimal:
    cleaned = text.replace(".", "").replace(",", ".") if decimal_comma else text.replace(",", "")  # changed
    try:
        return Decimal(cleaned)                                              # changed: exact money
    except InvalidOperation as exc:
        raise ValueError(f"not a number: {text!r}") from exc

def extract(pdf_path: Path) -> dict:
    domain = pdf_path.name.split("_")[1] if pdf_path.name.count("_") >= 2 else ""
    rule = RULES.get(domain)
    if rule is None:
        raise ValueError(f"no extraction rule for sender {domain!r}")
    with pdfplumber.open(pdf_path) as pdf:
        text = "\n".join((page.extract_text() or "") for page in pdf.pages)
    if len(text.strip()) < 50:
        raise ValueError("no text layer: scanned invoice needs OCR")
    def find(pattern: str, label: str) -> str:
        m = re.search(pattern, text, re.I | re.S)
        if not m:
            raise ValueError(f"{label} not found")
        return m.group(1).strip()
    return {
        "supplier": rule.name,
        "invoice_no": find(rule.invoice_no, "invoice number"),
        "invoice_date": datetime.strptime(find(rule.date, "date"), rule.date_format).date(),  # changed
        "net": money(find(rule.net, "net"), rule.decimal_comma),
        "vat": money(find(rule.vat, "VAT"), rule.decimal_comma),
        "gross": money(find(rule.gross, "gross"), rule.decimal_comma),
        "source_file": pdf_path.name,
    }

Decimal keeps totals exact, so the arithmetic check below does not trip over float noise like 1240.5000000001. Date formats are declared per supplier because 03/04/2026 is ambiguous and no parser can resolve it without knowing who wrote it — the same issue at scale is covered in normalize inconsistent date formats in CSV.

Same text, different meaning by supplier locale The date text 03/04/2026 means 3 April 2026 for a UK supplier and 4 March 2026 under a US reading, which a generic parser picks silently. The amount text 1.240,50 means one thousand two hundred forty point five for a German supplier and 1.24 under a naive dot-decimal parser. The amount text 1,240.50 means one thousand two hundred forty point five for a UK supplier and fails under a comma-decimal parser. Declaring the format per supplier removes the ambiguity. Text on invoice Supplier rule Generic parser 03/04/2026 3 Apr 2026 (UK) 4 Mar 2026 1.240,50 1240.50 (DE) 1.24 1,240.50 1240.50 (UK) fails or 1.2405

Validate Before Writing

# pip install "pandas>=2.2" openpyxl
from decimal import Decimal
import pandas as pd

def validate(inv: dict, register: pd.DataFrame, tolerance: Decimal = Decimal("0.02")) -> list[str]:
    problems = []
    if abs(inv["net"] + inv["vat"] - inv["gross"]) > tolerance:
        problems.append(f"net {inv['net']} + VAT {inv['vat']} != gross {inv['gross']}")
    if inv["net"] > 0 and not (Decimal("0") <= inv["vat"] / inv["net"] <= Decimal("0.27")):
        problems.append(f"implausible VAT rate {inv['vat'] / inv['net']:.2%}")
    age = (pd.Timestamp.today().normalize() - pd.Timestamp(inv["invoice_date"])).days
    if not -3 <= age <= 400:
        problems.append(f"invoice date {inv['invoice_date']} outside expected window")
    duplicate = ((register["supplier"] == inv["supplier"]) &
                 (register["invoice_no"].astype(str) == str(inv["invoice_no"]))).any() if len(register) else False
    if duplicate:
        problems.append("duplicate: already in register")
    return problems

A two-cent tolerance allows for per-line rounding that suppliers apply differently; anything larger is a mis-read field, usually the wrong number captured by a loose pattern. Duplicates are keyed on supplier plus invoice number, never on the file hash — a re-sent invoice is often a newly generated PDF with different bytes.

Append to the Register Safely

# pip install "pandas>=2.2" openpyxl
import os
import shutil
from pathlib import Path
import pandas as pd

REGISTER = Path("finance/invoice-register.xlsx")
COLUMNS = ["supplier", "invoice_no", "invoice_date", "net", "vat", "gross", "source_file", "processed_at"]

def load_register() -> pd.DataFrame:
    if not REGISTER.exists():
        return pd.DataFrame(columns=COLUMNS)
    return pd.read_excel(REGISTER, dtype={"invoice_no": "string"})

def append_rows(rows: list[dict]) -> None:
    current = load_register()
    new = pd.DataFrame(rows, columns=COLUMNS)
    for col in ("net", "vat", "gross"):
        new[col] = new[col].astype(float)
    combined = pd.concat([current, new], ignore_index=True)
    tmp = REGISTER.with_name(f".{REGISTER.stem}.tmp.xlsx")
    REGISTER.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(tmp, engine="openpyxl", date_format="yyyy-mm-dd") as writer:
        combined.to_excel(writer, sheet_name="Register", index=False)
    try:
        os.replace(tmp, REGISTER)                            # atomic on the same file system
    except PermissionError as exc:
        raise RuntimeError("register is open in Excel; rows kept for the next run") from exc

Writing a complete new file and renaming it over the old one means the register is never half-written. On Windows the rename fails while someone has the file open, which is the correct outcome: the job keeps the rows and retries on the next run rather than corrupting the file or losing data — the error and its alternatives are covered in fix PermissionError writing Excel file.

Variant Fix: Scanned Invoices

Suppliers who send scans fail the text-layer check. Route them through OCR before extraction, then apply the same supplier rules to the OCR text. OCRmyPDF adds a text layer in place, so the rest of the pipeline stays unchanged:

# pip install ocrmypdf
from pathlib import Path
import ocrmypdf

def ensure_text_layer(pdf_path: Path, work_dir: Path) -> Path:
    work_dir.mkdir(parents=True, exist_ok=True)
    out = work_dir / pdf_path.name
    try:
        ocrmypdf.ocr(pdf_path, out, skip_text=True, language="eng+deu", progress_bar=False)
    except ocrmypdf.exceptions.PriorOcrFoundError:
        return pdf_path
    return out

OCR text contains recognition errors — 0 for O, missing decimal separators — so the validation step matters even more for these suppliers, and their quarantine rate is worth tracking separately. Tuning OCR input quality is covered in make scanned PDFs searchable with OCRmyPDF.

The Run Loop with Quarantine

# pip install pdfplumber "pandas>=2.2" openpyxl
import json
import shutil
from datetime import datetime, timezone
from pathlib import Path

INCOMING, DONE, QUARANTINE = Path("inbox/incoming"), Path("inbox/processed"), Path("inbox/quarantine")

def run() -> dict:
    register = load_register()
    accepted, counts = [], {"accepted": 0, "quarantined": 0}
    for pdf_path in sorted(INCOMING.glob("*.pdf")):
        try:
            inv = extract(pdf_path)
            problems = validate(inv, register)
        except Exception as exc:
            problems = [str(exc)]
        if problems:
            QUARANTINE.mkdir(parents=True, exist_ok=True)
            shutil.move(pdf_path, QUARANTINE / pdf_path.name)
            (QUARANTINE / f"{pdf_path.stem}.reason.json").write_text(json.dumps(problems, indent=2))
            counts["quarantined"] += 1
            continue
        inv["processed_at"] = datetime.now(timezone.utc).replace(tzinfo=None)
        accepted.append((pdf_path, inv))
    if accepted:
        append_rows([inv for _, inv in accepted])
        DONE.mkdir(parents=True, exist_ok=True)
        for pdf_path, _ in accepted:
            shutil.move(pdf_path, DONE / pdf_path.name)       # move only after the register is saved
        counts["accepted"] = len(accepted)
    return counts

PDFs move to processed only after the register write succeeds, so a failure while saving leaves them in incoming for the next run — and the duplicate check stops them being added twice if the save actually completed.

First month of the invoice pipeline In the first month 412 invoices were accepted into the register automatically. 23 were quarantined because they were scans without a text layer before OCR was added. 9 were quarantined because net plus VAT did not equal gross, all traced to two suppliers whose layout changed. 17 re-sent invoices were rejected as duplicates. September 2026, 461 invoice PDFs Accepted to register 412 PDFs Quarantined - scanned 23 PDFs Quarantined - sums mismatch 9 PDFs Rejected - duplicate 17 PDFs

Verification

Reconcile the run: every PDF that entered incoming must be in processed or quarantine, the register must have gained exactly as many rows as were accepted, and totals in the register must equal the sum of the accepted invoices.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd

def verify_run(before_files: set[str], rows_before: int, counts: dict) -> None:
    processed = {p.name for p in Path("inbox/processed").glob("*.pdf")}
    quarantined = {p.name for p in Path("inbox/quarantine").glob("*.pdf")}
    unaccounted = before_files - processed - quarantined
    assert not unaccounted, f"PDFs with no outcome: {sorted(unaccounted)}"
    register = pd.read_excel("finance/invoice-register.xlsx", dtype={"invoice_no": "string"})
    assert len(register) == rows_before + counts["accepted"], "register row count mismatch"
    dupes = register.duplicated(subset=["supplier", "invoice_no"], keep=False)
    assert not dupes.any(), f"duplicate invoices in register: {register[dupes]['invoice_no'].tolist()}"
    arithmetic = (register["net"] + register["vat"] - register["gross"]).abs() > 0.02
    assert not arithmetic.any(), "register contains rows that fail net + VAT = gross"
    print(f"run verified: {counts}")

Send the counts and the quarantine reasons to the accounts payable team after each run. A quarantine folder nobody looks at is just a slower way to lose invoices; the email step from email generated reports with Python fits directly here.

FAQ

Why not use a single invoice-parsing library for all suppliers? Generic invoice parsers help with common layouts, but still need validation and per-supplier fallbacks. Starting with explicit rules for your top suppliers covers most volume and keeps failures understandable.

Should the register be a database instead of Excel? For more than a few thousand invoices a year or several concurrent users, yes — write to SQLite or your finance system's import format and generate the Excel view from it. The pipeline stages stay the same.

How do I add a new supplier? Run the diagnostic on a few of their PDFs, add a rule, and run the job against those files in a test folder. Quarantine reasons will tell you exactly which field the rule misses.

What about invoices with multiple VAT rates? Extract the VAT table rows with pdfplumber's table extraction and sum them; validate the sum against the total VAT line, as in Extracting Tables from PDFs.

Part of Processing Email Attachments Automatically.