How to Extract and Query Tables from PDFs

Every quarter, someone sends me a PDF. Sometimes it's a financial report. Sometimes it's a vendor invoice with line items buried in a three-page table. Sometimes it's a regulatory filing that must be in PDF because apparently the SEC hates structured data. The table I need is right there on page 7, perfectly formatted for human eyes, completely useless for analysis.

PDFs are containers, not data formats. They describe where to draw text on a page — coordinates, fonts, spacing. There's no concept of "row" or "column" or "cell." When you see a neat table in a PDF, what you're really seeing is a grid of text fragments positioned to look like a table. Extracting that into an actual dataframe is reverse-engineering a visual layout back into structure. It works surprisingly well for clean PDFs. It falls apart fast for messy ones.

The Python extraction stack

Three libraries dominate PDF table extraction in Python: pdfplumber, camelot-py, and tabula-py. They all take different approaches, and the right choice depends on the PDF you're dealing with.

pdfplumber works by analyzing the character positions and line objects in the PDF to detect table boundaries. It's pure Python, doesn't need Java, and handles most well-structured PDFs reliably.

import pdfplumber
import pandas as pd
 
with pdfplumber.open("quarterly_report.pdf") as pdf:
    page = pdf.pages[6]  # page 7, zero-indexed
    table = page.extract_table()
 
df = pd.DataFrame(table[1:], columns=table[0])
print(df.head())

That's the happy path. One page, one table, clean extraction. pdfplumber returns a list of lists — the first row is typically your header, the rest is data. Wrapping it in a DataFrame is straightforward.

camelot-py uses two detection modes: lattice (for tables with visible gridlines) and stream (for tables without borders). Lattice mode is remarkably accurate when the table has drawn lines. Stream mode guesses table boundaries from text alignment — it's less reliable but sometimes the only option.

import camelot
 
# Lattice mode — tables with visible borders
tables = camelot.read_pdf("invoice.pdf", pages="1-3", flavor="lattice")
 
for i, table in enumerate(tables):
    print(f"Table {i}: {table.shape}")
    df = table.df
    # camelot gives you a DataFrame directly
    # but the first row might be headers
    df.columns = df.iloc[0]
    df = df[1:].reset_index(drop=True)

One thing about camelot: it needs Ghostscript installed. If you're running in a Docker container or CI pipeline, that's an extra dependency to manage. It also requires Java for some operations through its tabula-py dependency — though the lattice/stream engines are actually built on OpenCV.

tabula-py is a Python wrapper around Tabula (Java). It's been around the longest, has the most Stack Overflow answers, and still works fine for straightforward tables. But you need a JVM installed, which makes it the heaviest dependency of the three.

My default order: pdfplumber first, camelot lattice mode if pdfplumber misses table boundaries, tabula-py as a fallback. For programmatic pipelines, I'd stick with pdfplumber to avoid the Java/Ghostscript dependencies.

Where extraction breaks

The code above works great for maybe 60% of real-world PDFs. The other 40% will make you question your career choices. Here's what goes wrong.

Merged cells. Financial tables love merging cells across rows or columns for category headers. pdfplumber sees a single text element spanning two columns and has no idea it's a merged cell. You'll get the text in one column and an empty string in the other — or worse, the text gets assigned to the wrong column entirely.

Multi-page tables. A table that spans pages 3 through 7 is actually five separate tables as far as any extraction library is concerned. Headers might repeat on each page, or they might not. You need to extract each page, detect and strip repeated headers, then concatenate.

import pdfplumber
import pandas as pd
 
all_rows = []
header = None
 
with pdfplumber.open("long_report.pdf") as pdf:
    for page_num in range(2, 7):  # pages 3-7, zero-indexed
        page = pdf.pages[page_num]
        table = page.extract_table()
        if table is None:
            continue
 
        if header is None:
            header = table[0]
            all_rows.extend(table[1:])
        else:
            # Check if first row matches header (repeated)
            if table[0] == header:
                all_rows.extend(table[1:])
            else:
                all_rows.extend(table)
 
df = pd.DataFrame(all_rows, columns=header)

This works for the simple case — repeated headers on every page. It doesn't handle tables where the header row has slightly different spacing on page 5 vs page 3, which happens more often than you'd think. String comparison fails silently when there's an extra space or a different dash character.

Scanned PDFs. If the PDF is a scan (an image embedded in a PDF wrapper), none of these libraries will find any text at all. You're in OCR territory now — tesseract, or a commercial OCR API. The extraction quality drops by an order of magnitude, and you're fighting font recognition errors on top of table detection. A "1" becomes an "l", a "0" becomes an "O", decimal points vanish. For scanned PDFs with tables, I've had better luck with AWS Textract or Google Document AI than with open-source OCR pipelines.

Inconsistent column alignment. Stream-mode extraction (both camelot and pdfplumber) infers column boundaries from text positions. If one row has an extra-long value that shifts everything right by a few pixels, the library might split it into a different column or merge two columns. You won't notice until your data has mysterious NaN columns and values in the wrong fields.

Cleaning extracted data

Raw extraction output is almost never ready for analysis. Columns come out as strings. Numbers have currency symbols or commas. Dates are in whatever format the PDF author preferred that day.

import re
 
def clean_currency(val):
    """Strip $, commas, parens (negative), and cast to float."""
    if not val or val.strip() == "":
        return None
    val = val.strip()
    negative = val.startswith("(") and val.endswith(")")
    val = re.sub(r"[$(,)]", "", val)
    try:
        result = float(val)
        return -result if negative else result
    except ValueError:
        return None
 
# Apply to financial columns
for col in ["Revenue", "COGS", "Net Income"]:
    df[col] = df[col].apply(clean_currency)
 
# Dates are just as fun
df["Quarter End"] = pd.to_datetime(df["Quarter End"], format="mixed", dayfirst=False)

The parentheses-as-negatives pattern catches people off guard. Financial PDFs represent negative numbers as (1,234.56) instead of -1234.56. If you're not handling that, your P&L analysis has expenses showing as positive. Ask me how I know.

Loading into DuckDB for SQL analysis

Once you have a clean DataFrame, the fastest path to SQL analysis is DuckDB. No server, no setup, and it queries DataFrames directly without copying data.

import duckdb
 
# Query the DataFrame directly — no INSERT needed
result = duckdb.sql("""
    SELECT
        "Quarter End",
        "Revenue",
        "Net Income",
        ROUND("Net Income" / NULLIF("Revenue", 0) * 100, 1) AS margin_pct
    FROM df
    WHERE "Revenue" > 0
    ORDER BY "Quarter End"
""").fetchdf()
 
print(result)

DuckDB reads the pandas DataFrame as a virtual table. No import step, no schema definition. If you want persistence — say you're extracting tables from 50 quarterly reports and want to query across all of them — write to a Parquet file or a DuckDB database file.

For Postgres, the path is similar but you need to define a schema and insert:

CREATE TABLE quarterly_financials (
    quarter_end  DATE,
    revenue      NUMERIC(15,2),
    cogs         NUMERIC(15,2),
    net_income   NUMERIC(15,2),
    source_file  TEXT
);
 
-- Then from Python:
-- df.to_sql('quarterly_financials', engine, if_exists='append', index=False)

The source_file column matters more than you'd think. When you're querying across 20 extracted PDFs and a number looks wrong, you need to trace it back to the source document. Always track provenance.

Putting it into a pipeline

A one-off extraction is fine for a single report. But if you're processing vendor invoices monthly, or pulling data from regulatory filings every quarter, you need something repeatable. The extraction script needs error handling, logging, validation checks (did the expected number of columns come out?), and a place to land the data.

You can build this yourself — a Python script with pdfplumber, a cleaning step, a DuckDB or Postgres load, a cron job. It's maybe two days of work for a solid v1. Then you spend another week handling the edge cases: the PDF that has a different table layout, the one where page numbers appear inside the table area, the scanned one that someone forgot to mention.

Or you can skip the plumbing. Fastero handles PDF table extraction as part of its file processing — upload a PDF, and you get queryable tables. The extraction, cleaning, and schema inference happen automatically. From there you can run SQL, build charts, or let the AI agent analyze the data directly. No Ghostscript, no JVM, no debugging why camelot returned 47 columns when the table has 8.

For teams running recurring extraction jobs, scheduled Python scripts can process new PDFs as they arrive — no Kubernetes cluster to manage.

The code in this post will get you through most single-file extractions. For anything beyond that, you're building infrastructure. We'd rather you didn't have to.

Try Fastero free — upload a PDF and query the tables inside it in under a minute. No credit card required.