Samlify

PDF invoices into Excel

By the Samlify team · · 5 min read · Excel, PDF

If your invoices are PDFs made by a computer, a short Python script can read a whole folder into one Excel workbook. It takes the number, date, total and line items, and checks that the lines add up to the total. Scanned invoices have no text to read, so they need OCR first. The script flags them instead of guessing.

1. Find out whether the PDF has text

A PDF made by a computer holds text the script can read. A scan holds only a picture of the page, and the script reports it as no text layer. The pdfplumber library says it "works best on machine-generated, rather than scanned, PDFs" and that it provides no OCR.

For scans, OCRmyPDF adds a text layer to a scanned PDF using the open-source Tesseract engine. Its own page says the accuracy "may not match that of commercial OCR solutions", so check the numbers afterwards. The command is ocrmypdf scan.pdf scan-ocr.pdf. We did not run OCR in this guide.

2. Try Excel's own PDF import first

Excel can read tables from a PDF through Power Query. On Windows it works in Microsoft 365 subscription plans and needs .NET Framework 4.5 or later; Office 2016 and 2019 are not listed. The Mac list does not include PDF. Excel for the web lists PDF only for files in SharePoint or OneDrive for work or school.

In Power Query, select the PDF option in the connector selection, pick the file, select a table in Navigator, then Load or Transform Data. Microsoft says that to import many PDFs at once you use the Folder or SharePoint Folder connector. Microsoft also says rows that run over several lines may be misread, and that Table.FillDown or Table.Group can repair them. We did not test this on Windows, so run it on a few of your own invoices before you trust it.

3. Set up the script

You need Python 3 and two libraries. In a terminal:

python3 -m venv invoices-env
invoices-env/bin/pip install pdfplumber openpyxl

Save this as invoices_to_excel.py:

"""Read every PDF invoice in a folder into invoices.xlsx.
Sheet Invoices: one row per PDF. Sheet Lines: one row per line item.
Usage: python invoices_to_excel.py <folder>"""
import os, re, sys
from datetime import datetime
from pathlib import Path
import pdfplumber
from openpyxl import Workbook

def money(s):
    s = re.sub(r"[$€£]|USD|EUR|GBP|kr", "", str(s)).replace(" ", "").replace(" ", "")
    neg = s.startswith("(") and s.endswith(")") or s.startswith("-")
    s = s.strip("()-")
    if re.fullmatch(r"\d{1,3}(\.\d{3})*,\d{2}", s): s = s.replace(".", "").replace(",", ".")
    elif re.fullmatch(r"\d{1,3}(,\d{3})*\.\d{2}|\d+\.\d{2}", s): s = s.replace(",", "")
    else: return None
    return -float(s) if neg else float(s)

def to_date(s):
    # Only unambiguous formats. 03/10/2026 could be 10 March or 3 October, so it is left for a person.
    for fmt in ("%Y-%m-%d", "%d.%m.%Y", "%B %d, %Y", "%b %d, %Y", "%d %B %Y", "%d %b %Y"):
        try: return datetime.strptime(s.strip(), fmt).date()
        except ValueError: pass
    return None

MONTH = r"(?:[A-Za-z]{3,9}\.? \d{1,2}, \d{4}|\d{1,2} [A-Za-z]{3,9} \d{4})"
def header(text):
    no = re.search(r"(?:invoice\s*(?:no\.?|number|#)|reference)\s*:?\s*(\S*\d\S*)", text, re.I)
    dt = re.search(r"(?:invoice date|date|issued)\s*:?\s*(" + MONTH + r"|\d{4}-\d{2}-\d{2}|\d{1,2}[./]\d{1,2}[./]\d{4})", text, re.I)
    total = tax = None
    for line in text.splitlines():
        m = re.match(r"\s*(?:grand total|total due|amount due|total)\b[^\d(-]*([\d.,() -]*\d[\d.,)]*)\s*$", line, re.I)
        if m and money(m.group(1)) is not None: total = money(m.group(1))
        m = re.match(r"\s*(?:sales tax|vat|tax|moms)\b.*?([\d.,]*\d)\s*$", line, re.I)
        if m and money(m.group(1)) is not None: tax = (tax or 0) + money(m.group(1))
    raw = dt.group(1) if dt else None
    return {"number": no.group(1) if no else None, "date": to_date(raw) if raw else None,
            "date_raw": raw, "total": total, "tax": tax}

SKIP = re.compile(r"^(sub ?total|total|grand total|amount due)|^(sales )?tax\b|^vat\b|^moms\b|\b(tax|vat)\s*\d+([.,]\d+)?\s*%", re.I)
NUM = re.compile(r"^[\d.,()$€£-]*\d[\d.,)]*$")
def text_lines(page):
    # No drawn table: read each text line as "description, then numbers", amount last.
    for line in (page.extract_text() or "").splitlines():
        tok = line.split()
        if len(tok) < 2 or money(tok[-1]) is None: continue
        i = len(tok)
        while i > 1 and NUM.match(tok[i - 1]): i -= 1
        desc = " ".join(tok[:i])
        if desc and not SKIP.match(desc): yield [desc] + tok[i:]
def lines(pdf):
    rows = []
    for page in pdf.pages:
        tables = page.extract_tables()  # drawn lines
        if not tables: tables = [list(text_lines(page))]
        for t in tables:
            for row in t:
                cells = [re.sub(r"\s+", " ", c or "").strip() for c in row]
                cells = [c for c in cells if c]
                if len(cells) < 2 or SKIP.match(cells[0]) or money(cells[0]) is not None: continue
                amt = money(cells[-1])
                if amt is not None: rows.append({"description": cells[0], "amount": amt, "row": " | ".join(cells)})
    return rows

def main(folder):
    folder = Path(folder)
    if not folder.is_dir(): sys.exit(f"Folder not found: {folder}")
    pdfs = sorted(p for p in folder.iterdir() if p.suffix.lower() == ".pdf")
    if not pdfs: sys.exit(f"No PDF files in {folder}")
    invoices, items = [], []
    for p in pdfs:
        rec = {"file": p.name, "number": None, "date": None, "total": None, "lines_sum": None}
        try:
            with pdfplumber.open(p) as pdf:
                text = "\n".join(pg.extract_text() or "" for pg in pdf.pages)
                if not text.strip():
                    rec["check"] = "no text layer: run OCR first"; invoices.append(rec); continue
                h = header(text); rows = lines(pdf)
            rec.update(number=h["number"], date=h["date"], total=h["total"], tax=h["tax"])
            items += [{"file": p.name, **r} for r in rows]
            rec["lines_sum"] = round(sum(r["amount"] for r in rows), 2)
            missing = [k for k in ("number", "date", "total") if rec[k] is None]
            if h["date"] is None and h["date_raw"]: missing[missing.index("date")] = f"date ('{h['date_raw']}' is ambiguous)"
            if missing: rec["check"] = "missing " + ", ".join(missing)
            elif abs(rec["lines_sum"] - h["total"]) < 0.005: rec["check"] = "ok"
            elif h["tax"] is not None and abs(rec["lines_sum"] + h["tax"] - h["total"]) < 0.005: rec["check"] = "ok (lines + tax)"
            else: rec["check"] = "lines do not add up to total"
        except Exception as e:
            rec["check"] = f"error: {type(e).__name__}"
        invoices.append(rec)
    wb = Workbook(); ws = wb.active; ws.title = "Invoices"
    cols = ["file", "number", "date", "total", "lines_sum", "check"]
    ws.append(cols)
    for r in invoices:
        ws.append([r.get(c) for c in cols])
        ws.cell(ws.max_row, 2).number_format = "@"   # keep invoice numbers as text
    wl = wb.create_sheet("Lines"); wl.append(["file", "description", "amount", "row"])
    for r in items: wl.append([r["file"], r["description"], r["amount"], r["row"]])
    out = folder / "invoices.xlsx"; tmp = folder / "invoices.tmp.xlsx"
    wb.save(tmp)
    try: os.replace(tmp, out)
    except PermissionError:
        tmp.unlink(missing_ok=True); sys.exit(f"Close {out.name} in Excel first, then run again.")
    w = max(len(r["file"]) for r in invoices)
    for r in invoices:
        print(f"{r['file']:<{w}}  {str(r['number']):<12} {str(r['date']):<11} {str(r['total']):>9} {str(r['lines_sum']):>9}  {r['check']}")
    print(f"\n{len(items)} line items written to {out}")

if __name__ == "__main__":
    main(sys.argv[1] if len(sys.argv) > 1 else sys.exit("Usage: python invoices_to_excel.py <folder>"))

4. Run it on a copy of your folder

Run invoices-env/bin/python invoices_to_excel.py invoices/. It writes invoices.xlsx into the same folder, with a sheet Invoices (one row per PDF) and a sheet Lines (one row per item). Run it on a copy of the folder, because it replaces invoices.xlsx each time. Close the workbook in Excel first. On Windows an open workbook blocks the save, and the script then asks you to close it. We could not test that case here.

We ran it on 7 sample files that we made ourselves with the ReportLab library. They are not real invoices, and the companies and amounts are made up. Two files are not normal on purpose: f_scan.pdf holds only a picture, and g_broken.pdf is not a PDF at all. The output, as printed:

a_ruled.pdf                NWS-1042     2026-10-03      320.0     320.0  ok
b_european_borderless.pdf  2026-0877    2026-10-14     1300.5    1300.5  ok
c_tax_wrapped.pdf          CC-2026-118  2026-10-15      372.6     345.0  ok (lines + tax)
d_ambiguous_date.pdf       5521         None            500.0     500.0  missing date ('03/10/2026' is ambiguous)
e_short.pdf                BC-9         2026-10-01      150.0     100.0  lines do not add up to total
f_scan.pdf                 None         None             None      None  no text layer: run OCR first
g_broken.pdf               None         None             None      None  error: PdfminerException

9 line items written to data/invoices.xlsx

The five readable files show what to expect:

  • a_ruled.pdf has lines around every cell and read correctly.
  • b_european_borderless.pdf has no table lines, European number format (1.200,00) and the letters ü, ø, Ä and Ö. It read correctly, with the total 1.300,50 stored as the number 1300.5.
  • c_tax_wrapped.pdf has a description that wraps onto two lines and a sales tax line. The lines sum to 345.00 and the total is 372.60, so the check reads ok (lines + tax).
  • d_ambiguous_date.pdf has the date 03/10/2026. That could be 10 March or 3 October, so the script leaves the date empty and says why.
  • e_short.pdf has lines that add up to 100.00 but a printed total of 150.00. The check says so.

The scan and the broken file each get a message in the check column, and the run goes on.

5. Adjust it for your suppliers

Read the check column first. Every row that is not ok needs a person. Three things you may want to change:

  • Labels. The invoice number is found after "Invoice No", "Invoice number", "Invoice #" or "Reference", and must contain a digit. If a supplier uses another word, add it to the pattern in header().
  • Date order. Dates such as 03/10/2026 are never guessed. Add %d/%m/%Y or %m/%d/%Y to the list in to_date() if all your suppliers use one order.
  • Number format. money() reads 1,234.56 and 1.234,56, but only with two decimals. An amount such as 1,200 with no cents is not read as money.

When this is not enough

The script reads text, not pictures, so scans need OCR first. Our samples are clean computer-made files, and real invoices come with more layouts. When a table has no lines, the script reads each text line as a description followed by numbers, so a description that wraps onto a second line loses its second half in that case. A line item whose description starts with "Tax" or "VAT" is skipped as a tax row. Invoices that show tax in several rates, or in a currency other than the total's, will not pass the check. If you get invoices from many suppliers in many layouts, a ready-made invoice reader or your accounting software is the better choice.

How we made this guide

On 19 November 2026 we wrote the script, generated 7 sample PDFs with ReportLab 5.1.0 (the two special files included), and ran the script with Python 3.11 and pdfplumber 0.11.10 and openpyxl 3.1.5. The output above is the real output of that run. We also ran it on an empty folder and on a folder that does not exist, and both stopped with a message. On the same day we read the Microsoft Learn PDF connector page, the Microsoft Support page on Power Query data sources in Excel versions, the pdfplumber README and the OCRmyPDF introduction. We did not run OCR, Excel's PDF import, or any paid service.

Sources