PDF to Excel: Extract Tables from PDF to .xlsx (Free)

import camelot
import pandas as pd
 
def extract_tables_from_pdf(pdf_path: str, output_path: str, flavor: str = "lattice"):
    """
    flavor: "lattice" for tables with borders, "stream" for borderless tables
    """
    tables = camelot.read_pdf(pdf_path, pages="all", flavor=flavor)
 
    print(f"Found {len(tables)} table(s)")
 
    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        for i, table in enumerate(tables):
            df = table.df
            sheet_name = f"Table_{i+1}_p{table.page}"
            df.to_excel(writer, sheet_name=sheet_name, index=False)
            print(f"  Sheet '{sheet_name}': {df.shape[0]} rows × {df.shape[1]} cols | Accuracy: {table.accuracy:.1f}%")
 
    print(f"Saved: {output_path}")
 
 
# For lattice tables (borders visible)
extract_tables_from_pdf("financial_report.pdf", "financial_report.xlsx", flavor="lattice")
 
# For stream tables (no borders)
extract_tables_from_pdf("bank_statement.pdf", "bank_statement.xlsx", flavor="stream")
 
# Check extraction accuracy
tables = camelot.read_pdf("report.pdf", pages="1-3", flavor="lattice")
for t in tables:
    print(f"Page {t.page}, accuracy: {t.accuracy:.1f}%, whitespace: {t.whitespace:.1f}%")

pip install camelot-py[cv]   # For lattice mode (requires OpenCV)
pip install camelot-py[base] # For stream mode only
pip install openpyxl pandas

import tabula
import pandas as pd
 
def pdf_to_excel_tabula(pdf_path: str, output_path: str):
    # Extract all tables from all pages
    dfs = tabula.read_pdf(pdf_path, pages="all", multiple_tables=True)
 
    print(f"Extracted {len(dfs)} table(s)")
 
    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        for i, df in enumerate(dfs):
            df.to_excel(writer, sheet_name=f"Table_{i+1}", index=False)
 
    print(f"Saved: {output_path}")
 
 
# For specific pages and areas
def extract_specific_area(pdf_path: str, page: int, area: list):
    """
    area: [top, left, bottom, right] in points (1 pt = 1/72 inch)
    """
    df = tabula.read_pdf(
        pdf_path,
        pages=page,
        area=area,       # [top, left, bottom, right]
        lattice=True,    # True for bordered tables
    )
    return df
 
# Convert all pages and save as CSV
tabula.convert_into("report.pdf", "output.csv", output_format="csv", pages="all")

pip install tabula-py
# Requires Java 8+ installed on the system

import pdfplumber
import pandas as pd
 
def extract_with_pdfplumber(pdf_path: str, output_path: str):
    all_tables = []
 
    with pdfplumber.open(pdf_path) as pdf:
        for page_num, page in enumerate(pdf.pages, start=1):
            tables = page.extract_tables()
 
            for t_idx, table in enumerate(tables):
                df = pd.DataFrame(table[1:], columns=table[0])   # First row as header
                df["_source_page"] = page_num
                all_tables.append((f"P{page_num}_T{t_idx+1}", df))
                print(f"  Page {page_num}, Table {t_idx+1}: {df.shape}")
 
    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        for sheet_name, df in all_tables:
            df.to_excel(writer, sheet_name=sheet_name, index=False)
 
    print(f"Saved {len(all_tables)} tables to {output_path}")
 
 
# Custom table settings for unusual layouts
def extract_custom_settings(pdf_path: str, page_num: int = 1):
    table_settings = {
        "vertical_strategy": "lines",         # "lines" | "lines_strict" | "text" | "explicit"
        "horizontal_strategy": "lines",
        "explicit_vertical_lines": [],
        "explicit_horizontal_lines": [],
        "snap_tolerance": 3,
        "join_tolerance": 3,
        "edge_min_length": 3,
        "min_words_vertical": 3,
        "min_words_horizontal": 1,
        "intersection_tolerance": 3,
        "text_tolerance": 3,
    }
 
    with pdfplumber.open(pdf_path) as pdf:
        page = pdf.pages[page_num - 1]
        table = page.extract_table(table_settings)
        return pd.DataFrame(table[1:], columns=table[0])

# Convert PDF to CSV (for simple tables)
libreoffice --headless --infilter="writer_pdf_import" \
  --convert-to csv input.pdf
 
# Or open in LibreOffice and manually export to Excel
libreoffice input.pdf

import pandas as pd
import re
 
def clean_extracted_table(df: pd.DataFrame) -> pd.DataFrame:
    # Remove rows that are entirely empty or header-repeat rows
    df = df.dropna(how="all")
    
    # Strip whitespace from all string columns
    df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)
    
    # Convert numeric strings to actual numbers
    def to_numeric(val):
        if isinstance(val, str):
            cleaned = re.sub(r"[,\s$€£¥]", "", val)
            try:
                return float(cleaned) if "." in cleaned else int(cleaned)
            except ValueError:
                return val
        return val
    
    df = df.applymap(to_numeric)
    
    # Promote first non-empty row to header if header is None
    if df.columns.tolist() == list(range(len(df.columns))):
        df.columns = df.iloc[0]
        df = df[1:].reset_index(drop=True)
    
    return df