Extracting Tables from PDFs to Excel: What Works, What Doesn't and Why

"Can you put the numbers from this PDF into a spreadsheet?" It sounds like a five-minute job. Anyone who has tried knows it can eat an afternoon. This post explains why PDF tables are hard to extract, which approaches work for which documents and how to check the output so you do not ship wrong numbers.

Why PDF tables are difficult

A PDF is designed for display, not data. In most PDFs there is no "table" object at all. There are only text fragments positioned at x/y coordinates, plus some lines. What looks like a neat grid to you is, to a computer, hundreds of independent strings floating on a page.

An extractor has to infer:

• which text fragments belong to the same row
• where column boundaries are
• whether a cell spans multiple lines
• which rows are headers, subtotals or footnotes

Each of these is a heuristic, and heuristics fail on unusual layouts.

Two very different kinds of PDF

Text-based PDFs

These are exported from software such as Excel, accounting systems or reporting tools. The text is real text, so you can select and copy it. Extraction works well here because the positions and characters are exact.

Scanned PDFs

These are images of paper. There is no text layer, just pixels. Extraction requires OCR (optical character recognition) first, which introduces recognition errors, such as 0 versus O and 1 versus l, before table detection even starts.

Quick test: Try to select a word in the PDF. If you can highlight the text, it is text-based. If you can only draw a box around an image, it is scanned.

Approaches compared

Approach	Best for	Weakness
Copy and paste	Tiny tables	Columns collapse into one
Browser-based converter	Text-based reports and statements	Struggles with scans
Python (pdfplumber, camelot, tabula-py)	Batch jobs and repeatable layouts	Needs coding and tuning
OCR suites	Scanned documents	Error-prone and needs review
Manual retyping	Very messy, small tables	Slow and tedious

A quick no-code workflow

For a text-based PDF, a browser tool is usually fastest. FileOtter's PDF to Excel converter reads text-based tables and outputs an XLSX file, processing everything locally in the browser, so financial documents are not uploaded anywhere. It does not do OCR, so it is not the right tool for scans.

A quick code workflow

For repeatable jobs, pdfplumber gives you control:

import pdfplumber, pandas as pd

rows = []
with pdfplumber.open("statement.pdf") as pdf:
    for page in pdf.pages:
        table = page.extract_table()
        if table:
            rows.extend(table[1:])  # skip the header row on each page

df = pd.DataFrame(rows, columns=["Date", "Description", "Amount", "Balance"])
df.to_excel("statement.xlsx", index=False)

Tune table_settings (for example vertical_strategy="text") when tables have no ruling lines.

Validating the output

Never trust extracted numbers blindly. Checks I always run:

1. Row count. Does the number of rows match the document?
2. Totals. Sum the columns and compare them with printed totals.
3. Data types. Are amounts numbers or text? Watch for thousands separators and currency symbols.
4. Negative values. Parentheses like (120.00) and trailing minus signs are often misread.
5. Multi-line cells. Descriptions that wrap can split into extra rows.
6. Dates. Check whether 03/04 means March 4 or April 3.

Tips for cleaner results

• Extract one page first to test the settings.
• Crop away headers and footers if the tool allows it.
• If a table spans pages, check that each page's header is not repeated as data.
• Keep the original PDF next to the spreadsheet for audits.

Conclusion

PDF table extraction is a solved problem only for well-behaved, text-based documents. Know which kind of PDF you have, choose the right tool and always validate totals. Ten minutes of checking beats an afternoon of explaining wrong numbers.