Extract Tables from a PDF into Excel with Python

Suppliers send statements as PDFs, banks send them as PDFs, and the old reporting system that nobody can change exports to PDF only. The numbers are in there — usually as a neat table — but copying them into Excel by hand is slow and error-prone, and Excel's own PDF import handles multi-page tables inconsistently. pdfplumber reads the text layer of a PDF together with the position of every character and line, which is enough to rebuild the table in Python, clean it with pandas and write it to a workbook. This page, part of Handling Excel File Formats and Conversions, walks through a two-page accounts receivable statement.

Prerequisites

pip install pdfplumber pandas openpyxl

statement.pdf lists 60 invoices in a ruled table that continues onto a second page, repeating its header row there. Amounts are formatted like 2,675.14, dates are day-first (02/09/2026), the statement total is printed in the introduction, and a small summary table without any ruling lines sits at the end. That mix — a gridded table, a page break, formatted numbers and a borderless table — is typical of system-generated PDFs.

Step 1 — See what pdfplumber finds

Open the PDF and ask each page for its tables. For tables drawn with ruling lines, the default settings use those lines to find the cell boundaries:

import pdfplumber

with pdfplumber.open("statement.pdf") as pdf:
    print(len(pdf.pages), "pages")
    for page in pdf.pages:
        for table in page.extract_tables():
            print(f"page {page.page_number}: {len(table)} rows, first two: {table[:2]}")

Each table comes back as a list of rows, each row a list of cell strings. The first page holds 35 rows and the second 27, and both start with the header row, because the report repeats it after the page break. Before writing any cleaning code, look at this raw output: it shows whether columns were split correctly, whether any cells came back as None (merged or empty cells), and whether a page contains more than one table.

A PDF with no text layer — a scan or a photographed page — returns no tables and no text at all. Check with page.extract_text(): if it is empty, the page is an image, and it needs OCR first. ocrmypdf adds a text layer to a scanned PDF, after which the steps below work, though OCR errors in the digits need the control-total check in Step 4 even more.

Step 2 — Stack the pages and drop repeated headers

Take the header from the first page, then keep every row from every page that is not a copy of it:

import pandas as pd

with pdfplumber.open("statement.pdf") as pdf:
    tables = [t for page in pdf.pages for t in page.extract_tables()]

header = tables[0][0]
body = [row for t in tables for row in t if row != header]
raw = pd.DataFrame(body, columns=header)
print(raw.shape)
print(raw.head(3).to_string(index=False))

60 rows, five columns, all text. Comparing whole rows with the header is the simplest reliable filter for repeated headers; on some reports the repeated header is slightly different (a "continued" suffix, say), in which case filter on a column instead — drop rows whose Invoice value does not match the invoice number pattern.

Step 3 — Turn text into numbers and dates

Everything pdfplumber returns is a string. Convert the columns that should be numbers and dates, and be explicit about the formats — 2,675.14 needs its thousands separator removed, and 02/09/2026 is the 2nd of September, not the 9th of February:

df = raw.copy()
df["Amount"] = pd.to_numeric(df["Amount"].str.replace(",", "", regex=False))
df["Date"] = pd.to_datetime(df["Date"], format="%d/%m/%Y")
df["Customer"] = df["Customer"].str.strip()
print(df.dtypes.to_dict())
print(df.head(3).to_string(index=False))

pd.to_numeric raises on anything it cannot parse, which is what you want here: a stray footnote marker or a cell that swallowed two values should stop the job, not become NaN. Statements from continental European systems print 2.675,14; for those, remove the dots and replace the comma with a point, as described in Read CSV Exports with Semicolons and Decimal Commas. Negative amounts printed as (1,234.00) or 1,234.00- need their own rule — see Convert Trailing Minus and CR/DR Amounts with pandas.

Step 4 — Check the result against the document

Extraction errors are silent: a row lost at a page break or a digit dropped by OCR produces a perfectly plausible table. Most statements print a total somewhere; find it in the text and compare:

import re

with pdfplumber.open("statement.pdf") as pdf:
    first_page_text = pdf.pages[0].extract_text()
printed = float(re.search(r"Statement total: ([\d,]+\.\d{2})", first_page_text).group(1).replace(",", ""))
extracted = round(df["Amount"].sum(), 2)
print(f"printed {printed:,.2f}  extracted {extracted:,.2f}  rows {len(df)}")
assert abs(printed - extracted) < 0.005, "extracted total does not match the statement"
assert df["Invoice"].is_unique

If the PDF has no total, check the row count against the invoice numbers (a gap in INV-2401 … INV-2460 means a missing row) or compare with a figure from another system. The idea is the same as in Reconcile Excel Totals with Control Totals.

Step 5 — Borderless tables: rebuild rows from word positions

The summary table at the end has no lines, so the default settings do not see it as a table. Switching to the text-based strategy (vertical_strategy="text") guesses column edges from how words line up, and on this table it splits words in the middle — Inv / oices, 79,080 / .86. When each cell is a single word or number, grouping words by their vertical position is simpler and more predictable:

from itertools import groupby

with pdfplumber.open("statement.pdf") as pdf:
    words = pdf.pages[-1].extract_words()

lines = [[w["text"] for w in grp]
         for _, grp in groupby(sorted(words, key=lambda w: (round(w["top"]), w["x0"])),
                               key=lambda w: round(w["top"]))]
start = lines.index(["Region", "Invoices", "Total"])
summary = pd.DataFrame(lines[start + 1:start + 3], columns=lines[start])
summary["Total"] = pd.to_numeric(summary["Total"].str.replace(",", "", regex=False))
print(summary.to_string(index=False))

extract_words returns each word with its coordinates. Sorting by the rounded top position and then by x0 puts words in reading order, and groupby on the top position collects each printed line. For borderless tables whose cells contain spaces — "Customer B01" — define the column boundaries yourself with explicit_vertical_lines, measured once from the header positions, and pass them to extract_table.

Step 6 — Write the workbook

With clean types, writing is ordinary pandas work. Give the amount column a number format, the dates a date format, and widen the columns:

with pd.ExcelWriter("statement.xlsx", engine="xlsxwriter", datetime_format="dd/mm/yyyy") as xw:
    df.to_excel(xw, sheet_name="Invoices", index=False)
    summary.to_excel(xw, sheet_name="Summary", index=False)
    money = xw.book.add_format({"num_format": "#,##0.00"})
    ws = xw.sheets["Invoices"]
    ws.set_column("A:B", 12)
    ws.set_column("C:C", 16)
    ws.set_column("E:E", 12, money)
    ws.autofilter(0, 0, len(df), len(df.columns) - 1)
    ws.freeze_panes(1, 0)
print(pd.read_excel("statement.xlsx", sheet_name=None).keys())

The amounts are now real numbers that sum, sort and pivot in Excel, and the dates are real dates — which is the whole point of the exercise.

Common pitfalls

• Scanned PDFs. No text layer, no tables. OCR first, then check totals carefully.
• Merged or empty cells. They come back as None or empty strings; forward-fill group labels that were printed once per block.
• Wrapped cell text. A long customer name that wraps onto two lines inside a cell arrives with a newline in it; replace \n with a space.
• Day-first dates. Always pass format= to to_datetime; guessing turns 02/09 into February for half the rows.
• Header rows that differ slightly per page. Filter on the content of a key column instead of comparing whole rows.

Performance notes

pdfplumber parses every character on a page, so it handles hundreds of pages per minute rather than thousands. For a large archive, crop each page to the table's area with page.crop(bbox) before extracting — less text to analyse — and process files in parallel. Keep the raw extracted rows alongside the cleaned output while you tune the rules, so a wrong conversion can be traced back to what the PDF actually said.

Conclusion

Extract each page's tables with pdfplumber, stack them while dropping repeated headers, and convert the text to numbers and dates with explicit formats. Check the result against a total or a sequence printed in the document before trusting it. For tables without ruling lines, rebuild rows from word positions or give pdfplumber explicit column edges. Then write the clean frame to Excel with number and date formats.

Frequently asked questions

Can Python convert a PDF table to Excel? Yes. pdfplumber, camelot and tabula-py extract tables from PDFs that have a text layer; pandas then cleans the result and writes it with to_excel. Scanned PDFs need OCR, for example with ocrmypdf, before any of them can read the text.

Why does pdfplumber return no tables? Either the page is a scanned image with no text layer — extract_text() returns nothing — or the table has no ruling lines. For borderless tables use the text strategy, explicit column edges, or group extract_words() output by line.

How do I handle a table that continues across pages? Extract the table from every page, take the header from the first page, and keep every row that is not a copy of the header. Then check the row count or total against the document.

How do I convert "1,234.56" strings from a PDF into numbers? Remove the thousands separator and parse: pd.to_numeric(col.str.replace(",", "")). For European formats such as 1.234,56 remove the dots and replace the comma with a point first.

Related

• Up one level: Handling Excel File Formats and Conversions — every format that ends up in a workbook.
• Convert Word Document Tables to Excel with Python — the same job for .docx files.
• Convert Currency Strings to Numbers with pandas — symbols, separators and brackets.
• Scrape a Web Page Table into Excel with pandas — when the table lives in HTML instead.