PDF to Excel in Python: Convert Tables to Multi-Sheet XLSX

Direct answer: Extract the PDF’s tables as structured JSON with pdfmux — process("report.pdf", output_format="json") returns result.tables, a list of {"headers": [...], "rows": [[...]], "page": N} objects — then write each table to its own sheet in an .xlsx file with pandas and openpyxl. This is where Excel differs from CSV: a CSV is one flat table per file, so a PDF with three distinct tables becomes three loose files with no types and no structure. An .xlsx workbook holds all three tables as separate sheets in one file, preserves numeric and date types, and keeps formatting. Reach for Excel when a PDF has several distinct tables you want to keep together; reach for CSV when you have one table feeding a script or database load.

Why PDF-to-Excel is harder than it looks

A PDF has no concept of a table. There are no rows, no columns, no cells — only glyphs painted at (x, y) coordinates and, sometimes, line segments drawn nearby. “Extracting a table” means reconstructing structure that was thrown away when the document was rendered. Four problems show up constantly:

• No table structure. The extractor has to infer cell boundaries from line geometry or whitespace alignment. Two visually identical tables can need completely different detection logic depending on whether the borders are real lines or just gaps.
• Multiple tables per page. Financial statements and reports routinely stack a summary table above a detail table on the same page. A naive extractor merges them into one ragged grid. For Excel, you want them as separate sheets, which means the extractor has to segment them first.
• Merged and spanning cells. A header that spans three columns, or a label that spans two rows, breaks the rectangular grid assumption. The extractor has to decide how to fill the gaps.
• Numbers stored as text. Everything extracted from a PDF arrives as a string. "1,234.50" and "(420)" are text, not a float and a negative number. Drop them into Excel as-is and every numeric column is left-aligned text you can’t sum, sort, or chart.

pdfmux handles the first three at extraction time — it classifies each page, segments multiple tables, and returns each one with its own headers and rows. The fourth (number typing) is a post-processing step you control, covered in Step 3.

Step 1: Extract tables as structured JSON

Install pdfmux and run process with output_format="json". Unlike the default Markdown output, JSON gives you the table broken into headers and rows you can iterate over directly.

pip install pdfmux pandas openpyxl

from pdfmux import process

result = process("report.pdf", quality="standard", output_format="json")

# result.tables -> [{"headers": [...], "rows": [[...]], "page": N}]
for table in result.tables:
    print(f"Page {table['page']}: "
          f"{len(table['headers'])} columns, {len(table['rows'])} rows")

Each entry in result.tables is a dictionary with three keys:

• headers — the column names as a list of strings.
• rows — a list of rows, each row a list of cell strings.
• page — the source page number, so you can label sheets or trace a value back to its origin.

The quality="standard" setting runs the full extract-audit-repair pipeline: it extracts, audits every page against quality checks, and re-extracts anything that fails. That matters here because a half-detected table produces a half-correct spreadsheet, and a wrong number in a finance workbook is worse than a missing one. For the mechanics of how detection works, see extract tables from PDF in Python.

Step 2: Write each table to its own sheet

Now turn each extracted table into a pandas DataFrame and write it to a dedicated sheet in one workbook. The two things that bite people here are Excel’s 31-character sheet-name limit and its rule that sheet names must be unique. Handle both with a small helper.

import pandas as pd
from pdfmux import process


def safe_sheet_name(base: str, used: set[str]) -> str:
    """Return an Excel-legal, unique sheet name (<= 31 chars)."""
    # Excel forbids these characters in sheet names.
    for ch in r'[]:*?/\\':
        base = base.replace(ch, " ")
    base = base.strip() or "Sheet"
    name = base[:31]

    # Ensure uniqueness while staying within 31 chars.
    suffix = 2
    while name.lower() in used:
        tail = f"_{suffix}"
        name = base[:31 - len(tail)] + tail
        suffix += 1

    used.add(name.lower())
    return name


def pdf_to_excel(pdf_path: str, xlsx_path: str) -> int:
    result = process(pdf_path, quality="standard", output_format="json")

    if not result.tables:
        raise ValueError(f"No tables detected in {pdf_path}")

    used_names: set[str] = set()
    with pd.ExcelWriter(xlsx_path, engine="openpyxl") as writer:
        for i, table in enumerate(result.tables, start=1):
            df = pd.DataFrame(table["rows"], columns=table["headers"])
            sheet = safe_sheet_name(
                f"Table {i} (p{table['page']})", used_names
            )
            df.to_excel(writer, sheet_name=sheet, index=False)

    return len(result.tables)


count = pdf_to_excel("report.pdf", "report.xlsx")
print(f"Wrote {count} sheets to report.xlsx")

pd.ExcelWriter(..., engine="openpyxl") opens one workbook; each df.to_excel(writer, sheet_name=...) call adds a sheet. index=False drops pandas’ auto-generated row numbers, which you almost never want in a tables export. The result is a single .xlsx file with one tab per source table — three tables in the PDF, three sheets in Excel, all in the right order with their headers intact.

If you only ever have one table per file and want flat output instead, the CSV path is simpler — no sheet-name juggling, no workbook object.

Step 3: Fix numbers stored as text

Every cell came out of the PDF as a string, so Excel will treat your numeric columns as text: left-aligned, un-summable, and unsortable. Convert them with pandas. pd.to_numeric(..., errors="coerce") turns anything that parses into a real number and turns everything else into NaN, so a stray label in a number column never crashes the run.

import pandas as pd


def coerce_types(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()

    for col in df.columns:
        # Strip thousands separators, currency symbols, and whitespace,
        # and turn accounting-style "(420)" into "-420".
        cleaned = (
            df[col].astype(str)
            .str.strip()
            .str.replace(r"[,$£€\s]", "", regex=True)
            .str.replace(r"^\((.*)\)$", r"-\1", regex=True)
        )

        numeric = pd.to_numeric(cleaned, errors="coerce")
        # Only adopt the numeric version if most of the column parsed.
        if numeric.notna().mean() >= 0.8:
            df[col] = numeric
            continue

        # Otherwise try dates (e.g. "2026-06-15", "15/06/2026").
        dates = pd.to_datetime(df[col], errors="coerce", dayfirst=True)
        if dates.notna().mean() >= 0.8:
            df[col] = dates

    return df

The 0.8 threshold is the guard rail: a column only becomes numeric if at least 80% of its values parse cleanly, so a mostly-text column with one stray digit stays text. Drop coerce_types into the Step 2 loop right before writing each sheet:

df = pd.DataFrame(table["rows"], columns=table["headers"])
df = coerce_types(df)
df.to_excel(writer, sheet_name=sheet, index=False)

Now Excel right-aligns the numbers, sums them, and renders the date columns as dates. If you’d rather hand structured, already-typed records to a downstream LLM or pipeline instead of a spreadsheet, PDF to JSON for LLM pipelines covers that shape.

Handling scanned PDFs

If your PDF is a scan — an image with no text layer — pdfmux OCRs the affected pages automatically as part of the same process() call. You don’t switch tools or flags; a scanned bank statement and a born-digital report run through the identical code above. How the OCR engines are selected and chained is covered in OCR PDF extraction in Python.

OCR is also where the per-page confidence score earns its keep. A blurry scan or a faint fax can produce a table that looks fine but has a transposed digit. pdfmux scores every page, so you can flag the sheets you shouldn’t trust without eyeballing all of them:

result = process("scanned-statement.pdf", quality="standard",
                 output_format="json")

print(f"Document confidence: {result.confidence:.2f}")

low_conf_pages = {p.number for p in result.pages if p.confidence < 0.8}
for table in result.tables:
    flag = " ⚠️ REVIEW" if table["page"] in low_conf_pages else ""
    print(f"Page {table['page']} table{flag}")

result.confidence is the document-level score; result.pages carries per-page confidence and number. The extract-audit-repair loop that produces those scores — extract, audit with quality checks, re-extract failures — is documented in self-healing PDF extraction. Scanned financial documents are the canonical use case; see extract bank statement data in Python for an end-to-end statement workflow.

CSV vs Excel: which should you use

Both start from the same result.tables. The choice is about shape and consumer, not about the extractor.

Rule of thumb: if a human is going to open it and look at several related tables together, ship Excel. If a machine is going to load one table, ship CSV. The full CSV walkthrough — including the standard-library-only path — is in PDF to CSV in Python.

Keep reading

• PDF to CSV in Python — the flat-file companion to this post; reach for it when you have one table per file feeding a script or database.
• Extract tables from PDF in Python — how table detection actually works under the hood, with lattice vs. stream structures.
• OCR PDF extraction in Python — how pdfmux OCRs scanned pages automatically and which engines it chains.
• Self-healing PDF extraction — the extract-audit-repair loop and the per-page confidence scores that tell you which sheets to trust.

Last updated: June 2026.