How to Extract Tables from a PDF to Excel with Python (Camelot vs. pypdf)

Copying a table out of a PDF and pasting it into Excel is slow, and it's easy to shift a column or drop a row without noticing. If you handle invoices, financial statements, or research tables, those hours add up.

The short answer: use Camelot to detect the tables in a text-based PDF, clean each one with pandas, and write the result to a .xlsx file. The pypdf library reads text well, but it has no table detection.

In this tutorial for Codingvila readers, you'll compare the two libraries and then run a complete command-line script. It handles one or many tables, falls back to a second parsing mode, cleans headers, converts numbers, and reports problems clearly.

What You Need Before You Start

• Python 3.10 or newer. Camelot 2.0 requires it.
• A terminal and basic comfort running a Python script.
• A PDF with a table where you can select the text with your mouse. Scanned documents need OCR first, covered below.

The code in this article was run on Python 3.12 with Camelot 2.0.0 and 1.0.9, pandas 3.0, openpyxl 3.1, and pypdf 5.9 and 6.20. It was tested on Linux only, so check file paths and shell quoting on Windows.

The Core Challenge: Why PDF Table Extraction Breaks

A PDF describes how a page looks, not what the data means. It has no built-in idea of a "table," a "row," or a "cell." It stores instructions such as "draw this text at these coordinates" and "draw a line from here to there."

That's why extraction tools have to guess. Some tables are drawn with visible lines. Others are just text aligned in columns with nothing around it. The two libraries below handle that guessing very differently.

• pypdf is a lightweight library for extracting text, splitting and merging pages, and reading metadata. It has no table-detection feature.
• Camelot is built specifically for extracting tables from text-based PDFs. It analyzes the page layout to rebuild rows and columns.

---

Setting Up Your Python Environment

Create a virtual environment first. Installing into a clean environment avoids version conflicts. For example, Camelot 1.0.x limits the pypdf version, so a newer pypdf already installed on your system can clash with it.

python -m venv venv
source venv/bin/activate        # Windows: venv\Scripts\activate
pip install pypdf camelot-py openpyxl

When it finishes, pip prints a "Successfully installed" line. Camelot installs pandas for you, and pandas uses openpyxl to write .xlsx files.

Note on Camelot versions: Since Camelot 1.0.0 (December 2024), table detection for lattice mode uses pdfium by default, so you don't need Ghostscript. Ghostscript is now an optional backend. Older tutorials, and Camelot 0.x, tell you to install Ghostscript and run pip install "camelot-py[base]". Camelot 2.0 (June 2026) requires Python 3.10+, changed the default line_scale for lattice mode, and changed the defaults of Table.to_excel. The script in this article uses pandas to write the file, so that last change doesn't affect it. Run pip show camelot-py to see your version.

---

Method 1: Quick Text Extraction with pypdf

If your PDF contains plain paragraphs or a simple single-column list, pypdf is the fastest option.

from pypdf import PdfReader

def extract_raw_text(pdf_path):
    reader = PdfReader(pdf_path)
    # Target the first page
    page = reader.pages[0]
    text = page.extract_text()
    print(text)

# Example usage
# extract_raw_text("financial_report.pdf")

What you'll see: with the sample sales table used later in this article, the default mode prints every cell on its own line. The text is all there, but you can't tell which cells belong to the same row:

Codingvila Store - Sales Report
Invoice No
Customer
Amount (USD)
Status
CV-0001
Maple Books
$1,250.00
Paid
...

pypdf 4.0 and later also offer extract_text(extraction_mode="layout"). The pypdf documentation describes it as experimental. It tries to keep the visual spacing, so the same page prints like this:

Invoice No          Customer                         Amount (USD)             Status
CV-0001             Maple Books                                  $1,250.00    Paid
CV-0002             Northwind Cafe                                 $980.50    Due

That's easier to read, but it's still one block of text. Your code would have to guess where each column starts, so it isn't a reliable way to build a spreadsheet. Results also vary from PDF to PDF.

---

Method 2: Table Parsing with Camelot (Recommended for Tables)

Camelot offers two parsing modes, which it calls flavors:

1. Lattice: Looks for the lines that frame the cells. Use it for tables with visible gridlines, such as many invoices and ledgers.
2. Stream: Uses the gaps between text to guess where columns are. Use it for tables without visible borders. It's a guess, so always check the result.

The script below tries lattice first and falls back to stream only if lattice finds nothing. The flowchart shows that logic.

Try it with a sample PDF

To follow along without hunting for a file, install reportlab (pip install reportlab) and run this script. It creates sales_report.pdf with a gridlined table. It uses the fictional Codingvila Store.

from reportlab.lib import colors
from reportlab.lib.pagesizes import letter
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.platypus import Paragraph, SimpleDocTemplate, Spacer, Table, TableStyle

rows = [
    ["Invoice No", "Customer", "Amount (USD)", "Status"],
    ["CV-0001", "Maple Books", "$1,250.00", "Paid"],
    ["CV-0002", "Northwind Cafe", "$980.50", "Due"],
    ["CV-0003", "Blue Harbor LLC", "($120.00)", "Refunded"],
    ["CV-0004", "Summit Gear", "$2,400.00", "Paid"],
]
table = Table(rows, colWidths=[90, 140, 110, 90])
table.setStyle(TableStyle([("GRID", (0, 0), (-1, -1), 0.8, colors.black)]))

title = Paragraph("Codingvila Store - Sales Report", getSampleStyleSheet()["Title"])
SimpleDocTemplate("sales_report.pdf", pagesize=letter).build([title, Spacer(1, 12), table])

The complete script

Save this as pdf_tables_to_excel.py. It's a command-line tool, so you can reuse it on any text-based PDF without editing the code.

"""Extract tables from a text-based PDF and save them to an Excel workbook.

Usage:
    python pdf_tables_to_excel.py sales_report.pdf sales_report.xlsx --pages 1-3
"""

from __future__ import annotations

import argparse
import logging
import sys
from pathlib import Path

import camelot
import pandas as pd

log = logging.getLogger("pdf_tables_to_excel")


def read_tables(pdf_path: Path, pages: str, flavor: str, table_areas=None):
    """Return (flavor_used, TableList). 'auto' tries lattice, then stream."""
    flavors = ["lattice", "stream"] if flavor == "auto" else [flavor]
    options = {"table_areas": table_areas} if table_areas else {}
    for name in flavors:
        log.info("Trying the %s flavor on pages %s...", name, pages)
        tables = camelot.read_pdf(str(pdf_path), pages=pages, flavor=name, **options)
        if len(tables) > 0:
            return name, tables
    return None, []


def clean_frame(df: pd.DataFrame) -> pd.DataFrame:
    """Trim whitespace and drop rows that are completely empty."""
    df = df.apply(lambda column: column.str.strip())
    df = df.replace("", pd.NA).dropna(how="all")
    return df.reset_index(drop=True)


def unique_headers(values) -> list[str]:
    """Turn a row of cell values into unique, non-empty column names."""
    seen: dict[str, int] = {}
    names = []
    for position, value in enumerate(values, start=1):
        base = str(value).strip() if pd.notna(value) else ""
        base = base or f"Column {position}"
        count = seen.get(base, 0)
        seen[base] = count + 1
        names.append(base if count == 0 else f"{base} ({count + 1})")
    return names


def convert_numeric_columns(df: pd.DataFrame) -> pd.DataFrame:
    """Convert columns such as '$1,250.00' or '(120.00)' to numbers.

    A column is converted only if every non-empty value is numeric, and it
    is skipped if any value has a leading zero (IDs, ZIP codes).
    """
    result = df.copy()
    for column in result.columns:
        series = result[column].astype("string")
        cleaned = (
            series.str.replace(r"[$,\s]", "", regex=True)
            .str.replace(r"^\((.*)\)$", r"-\1", regex=True)
        )
        filled = cleaned.notna()
        if not filled.any() or cleaned[filled].str.match(r"^-?0\d").any():
            continue
        converted = pd.to_numeric(cleaned, errors="coerce")
        if converted[filled].notna().all():
            result[column] = converted
    return result


def build_sheets(tables, combine: bool, numeric: bool) -> dict[str, pd.DataFrame]:
    """Return {sheet_name: DataFrame}. Optionally stack same-shape tables."""
    sheets: dict[str, pd.DataFrame] = {}
    stacked: list[pd.DataFrame] = []
    header: list[str] | None = None

    for table in tables:
        raw = clean_frame(table.df)
        # Stream can "find" a table in plain text, so require a real grid.
        if raw.shape[0] < 2 or raw.shape[1] < 2:
            log.warning("Skipping a table on page %s: too small to be a real table.",
                        table.page)
            continue

        if combine and header is None:
            header = unique_headers(raw.iloc[0])
            stacked.append(raw.iloc[1:].set_axis(header, axis=1))
        elif combine and raw.shape[1] == len(header):
            body = raw
            if unique_headers(raw.iloc[0]) == header:
                body = raw.iloc[1:]  # drop the header repeated on this page
            stacked.append(body.set_axis(header, axis=1))
        else:
            name = f"Page{table.page}_Table{table.order}"
            body = raw.iloc[1:].set_axis(unique_headers(raw.iloc[0]), axis=1)
            sheets[name] = body.reset_index(drop=True)

    if stacked:
        sheets = {"Combined": pd.concat(stacked, ignore_index=True), **sheets}

    if numeric:
        sheets = {name: convert_numeric_columns(df) for name, df in sheets.items()}
    return sheets


def extract_tables_to_excel(
    pdf_path: Path,
    output_path: Path,
    pages: str = "1",
    flavor: str = "auto",
    combine: bool = False,
    numeric: bool = False,
    table_areas: list[str] | None = None,
) -> int:
    """Extract tables and write one workbook. Returns the number of sheets."""
    flavor_used, tables = read_tables(pdf_path, pages, flavor, table_areas)
    if not tables:
        raise RuntimeError(
            "No tables were found. If you can't select text in the PDF, "
            "it is probably a scan and needs OCR first."
        )
    log.info("Found %d table(s) using the %s flavor.", len(tables), flavor_used)
    if flavor_used == "stream":
        log.warning("Stream guesses column positions. Check the workbook carefully.")

    sheets = build_sheets(tables, combine, numeric)
    if not sheets:
        raise RuntimeError("Detected tables were too small to use. Check the PDF.")

    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        for name, df in sheets.items():
            df.to_excel(writer, sheet_name=name[:31], index=False)
    return len(sheets)


def main(argv: list[str] | None = None) -> int:
    parser = argparse.ArgumentParser(description="Extract PDF tables to Excel.")
    parser.add_argument("pdf", type=Path, help="input PDF (text-based, not scanned)")
    parser.add_argument("xlsx", type=Path, help="output .xlsx file")
    parser.add_argument("--pages", default="1", help="e.g. 1, 1-3, 1,3,5 or all")
    parser.add_argument("--flavor", choices=["auto", "lattice", "stream"], default="auto")
    parser.add_argument("--table-area", action="append", metavar="x1,y1,x2,y2",
                        help="limit parsing to a region (PDF points); repeatable")
    parser.add_argument("--combine", action="store_true",
                        help="stack tables with the same column count on one sheet")
    parser.add_argument("--numeric", action="store_true",
                        help="convert numeric-looking columns to numbers")
    args = parser.parse_args(argv)

    logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")

    if not args.pdf.is_file() or args.pdf.suffix.lower() != ".pdf":
        log.error("Input must be an existing .pdf file: %s", args.pdf)
        return 2
    if args.xlsx.suffix.lower() != ".xlsx":
        log.error("Output file name must end in .xlsx: %s", args.xlsx)
        return 2

    try:
        count = extract_tables_to_excel(
            args.pdf, args.xlsx, args.pages, args.flavor,
            args.combine, args.numeric, args.table_area,
        )
    except Exception as error:  # report any parsing or file error cleanly
        log.error("%s", error)
        return 1

    log.info("Saved %d sheet(s) to %s", count, args.xlsx)
    return 0


if __name__ == "__main__":
    sys.exit(main())

What each part does

• read_tables() calls camelot.read_pdf(). With --flavor auto, it tries lattice and then stream, and returns the first flavor that finds anything.
• clean_frame() trims whitespace and removes completely empty rows. Camelot returns every cell as a text string, so this step works on strings.
• unique_headers() turns the first row into column names. It fills blank names and renames duplicates, because blank or repeated headers cause confusing Excel output.
• convert_numeric_columns() runs only with --numeric. It turns values such as $1,250.00 and ($120.00) into real numbers (1250.0 and -120.0) so Excel can sum them. It skips any column where a value has a leading zero, so IDs such as 00123 keep their zeros.
• build_sheets() discards tables under 2 rows or 2 columns, then either keeps one sheet per table or stacks them (--combine).
• main() validates the file names and returns exit code 0 for success, 1 for "no usable table," and 2 for bad arguments, so other scripts can check the result.

Run it and check the result

python pdf_tables_to_excel.py sales_report.pdf sales_report.xlsx --numeric

You should see:

INFO: Trying the lattice flavor on pages 1...
INFO: Found 1 table(s) using the lattice flavor.
INFO: Saved 1 sheet(s) to sales_report.xlsx

Open sales_report.xlsx. It has one sheet, Page1_Table1, containing:

Invoice No	Customer	Amount (USD)	Status
CV-0001	Maple Books	1250	Paid
CV-0002	Northwind Cafe	980.5	Due
CV-0003	Blue Harbor LLC	-120	Refunded
CV-0004	Summit Gear	2400	Paid

Amounts are real numbers, so Excel's SUM works on them. Without --numeric, they stay as text such as $1,250.00.

Command-line options

Option	What it does
--pages	Pages to read: 1, 1-3, 1,3,5, or all. Default is 1.
--flavor	auto (default), lattice, or stream.
--table-area	Limits parsing to a region, in PDF points. Repeat it for several regions.
--combine	Stacks tables with the same column count into one Combined sheet and removes repeated header rows.
--numeric	Converts numeric-looking columns to numbers.

When the fallback finds the wrong thing

The stream flavor is a guess, and it doesn't fail politely. In testing, a borderless version of the sales table came back with the page title treated as a table row, so the column names were wrong. A plain paragraph with no table at all also came back as a one-cell "table," and Camelot reported an accuracy of 100 for it. Don't treat the accuracy figure as proof that the table is correct.

The script discards one-cell results, and it logs a warning whenever stream is used. For borderless tables, tell Camelot where the table is:

python pdf_tables_to_excel.py borderless.pdf out.xlsx --flavor stream --table-area "40,670,572,520"

The four numbers are x1,y1,x2,y2, in PDF points, for the top-left and bottom-right corners of the table. The origin is the bottom-left corner of the page, and a US Letter page is 612 by 792 points. The coordinates above fit my test file only. Yours will differ. Camelot's documentation describes a visual-debugging plot, which needs matplotlib, to help you find them.

---

Feature Comparison: pypdf vs. Camelot

Automation Metric	pypdf	Camelot
Best For	Metadata, plain-text paragraphs, pulling out specific strings	Rebuilding tables, with or without cell borders
Excel Compatibility	Poor: you have to write your own parsing logic	Good: each table is a pandas DataFrame, so .to_excel() works
Handling Borderless Tables	No table detection	Supported with flavor='stream', but it guesses, so check the output
Processing Speed	Generally faster	Generally slower, because it analyzes the page layout
Scanned PDFs	Needs OCR first	Needs OCR first

---

Handling Real-World Edge Cases

1. Multi-Page Tables

For a table that continues across pages, pass a page range and add --combine:

python pdf_tables_to_excel.py statement.pdf statement.xlsx --pages 1-3 --combine --numeric

Camelot returns one table per page. With --combine, the script takes the header from the first table, removes the header row if it repeats on later pages, and stacks everything on one sheet named Combined. Tables with a different column count get their own sheets. In a test with a three-page table, 12 rows came out of 3 pages of 4 rows each, with one header row.

Long PDFs use more memory because Camelot holds every page's data until read_pdf returns. Camelot's documentation suggests processing page ranges in chunks and saving each chunk before moving on.

2. Scanned PDFs (No Text Layer)

A quick test: open the PDF and try to select a word with your mouse. If you can't, the file is an image of a page, probably from a scanner. Camelot's documentation says it works only with text-based PDFs, and the pypdf documentation says pypdf is not OCR software.

You need Optical Character Recognition (OCR) first. One common route is OCRmyPDF, which adds a text layer to the file so you can then run the script on the result. Another is to convert each page to an image and run it through Tesseract with pytesseract. OCR makes mistakes, and rebuilding tables from OCR output is harder than from a native digital PDF, so check the numbers.

Camelot 2.0 also lists an optional machine-learning flavor for borderless and scanned tables. It's installed as an extra and isn't covered here. See the Camelot documentation if you want to try it.

3. Password-Protected PDFs

Camelot's read_pdf() accepts a password argument, for example camelot.read_pdf("report.pdf", password="your-password"). The script above doesn't expose it. If you add it, read the password from an environment variable instead of writing it into the code.

4. Numbers That Look Like Numbers but Aren't

Camelot returns every cell as text. Values such as $1,250.00, (120.00) for negatives, or 45% won't sum in Excel until they're converted. --numeric handles currency symbols, thousands separators, and parentheses. It deliberately leaves percent values and any column with leading-zero values alone, and you should check those yourself.

---

Common Errors and How to Fix Them

• "No tables were found": Confirm the PDF has selectable text and that --pages points at the right page. Pages are numbered from 1.
• Wrong columns or a title row inside the table: This usually happens with stream. Use --table-area to limit the table region.
• A Ghostscript error: This applies to older Camelot versions or if you selected the Ghostscript backend. Upgrade Camelot, or install Ghostscript with brew install ghostscript on macOS, sudo apt install ghostscript on Debian or Ubuntu, or the official installer on Windows.
• A pypdf version conflict from pip: Camelot 1.0.x limits the pypdf version. Use a virtual environment as shown above, or upgrade to Camelot 2.0 on Python 3.10 or newer.
• ModuleNotFoundError: The package isn't installed in the Python environment running your script. Use python -m pip install followed by the package name, so pip and python match.
• PermissionError when saving: On Windows, this usually means the output .xlsx is still open in Excel. Close it and run the script again.
• Numbers still stored as text: Add --numeric, or check whether the column has a value with a leading zero or a stray character, which blocks conversion by design.

Frequently Asked Questions

Can pypdf extract tables from a PDF?

No. pypdf extracts text, and its optional layout mode preserves spacing, but it has no table-detection feature. Use Camelot or another table-aware library for tables.

Do I need Ghostscript for Camelot?

Not in Camelot 1.0.0 and later. Those versions use pdfium by default, and Ghostscript is an optional backend. Camelot 0.x and tutorials written for it required Ghostscript.

Which Camelot flavor should I use?

Use lattice when the table has visible gridlines. Use stream when it doesn't, and check the output. The script's auto mode tries lattice first.

Can Python extract tables from a scanned PDF?

Only after OCR. Camelot and pypdf both need a text layer in the PDF.

Summary

Use pypdf for plain text, page splitting, and metadata. When the goal is a spreadsheet, use Camelot to rebuild the table and pandas to clean and save it. Start with lattice for tables with gridlines, and treat stream results as a draft you must check. Open the workbook before you trust it, especially the header row and the number columns. If the PDF is a scan, run OCR first.