How to extract tables from PDFs in ETL: step by step

Extracting tables from PDFs is common when data sources arrive as static reports. This tutorial shows how to extract, clean and transform PDF tables to CSV/Parquet — useful to integrate into ETL pipelines and automate report ingestion.

Prerequisites

• Python 3.8+ and libraries: tabula-py, pandas, camelot (optional), pyarrow.
• Java installed (required for tabula-py).
• A PDF with example tables (pages with regular tables).
• Code editor and command line.

Step 1: Understand the table format in the PDF

Before coding, open the PDF and check: do the tables have well-defined lines (grid) or are they text-aligned tables? Knowing this helps choose the library: tabula-py works well for grid tables; camelot is also useful. Note the pages and the expected columns.

Step 2: Install dependencies

Install the required libraries and confirm that Java is accessible. We use tabula-py + pandas in this example because it is simple and reliable for many tabular PDFs.

pip install tabula-py pandas pyarrow
# (optional) pip install camelot-py[cv]
# Make sure 'java -version' works in your command line

Step 3: Extract tables from the PDF with tabula-py

Use tabula-py to extract all tables from a specific page or the entire document. The initial output will be a list of pandas DataFrame objects.

from tabula import read_pdf
import pandas as pd

pdf_path = 'relatorio_exemplo.pdf'
# Extract all tables from the PDF (pages='all' or specific number)
tables = read_pdf(pdf_path, pages='all', multiple_tables=True)
print(f'Tables extracted: {len(tables)}')

Step 4: Inspect and clean each table

Check the content of each DataFrame: misaligned columns, duplicated headers, total rows. Do standard cleaning: normalize column names, remove empty rows and convert types.

def clean_table(df: pd.DataFrame) -> pd.DataFrame:
    # Remove fully empty rows
    df = df.dropna(how='all')
    # Promote the first row to header if detecting incorrect header
    if df.columns.tolist() == list(range(len(df.columns))):
        df.columns = df.iloc[0].astype(str).str.strip()
        df = df[1:]
    # Trim text columns
    df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)
    # Example: convert 'Valor' column to float
    if 'Valor' in df.columns:
        df['Valor'] = (df['Valor']
                        .astype(str)
                        .str.replace('\u00A0', '')
                        .str.replace(',', '.')
                        .str.replace('[^0-9.\-]', '', regex=True))
        df['Valor'] = pd.to_numeric(df['Valor'], errors='coerce')
    return df.reset_index(drop=True)

cleaned_tables = [clean_table(t) for t in tables]

Step 5: Reconcile tables and standardize schema

If the PDF has tables per page with the same schema, combine them. If schemas differ, map columns to a canonical schema for the ETL.

# Example: concatenate tables with the same columns
combined = pd.concat(cleaned_tables, ignore_index=True, sort=False)
# Select and rename to canonical schema
canonical_cols = {'Data':'date', 'Descrição':'description', 'Valor':'amount'}
combined = combined.rename(columns=canonical_cols)
combined = combined[['date','description','amount']]

Step 6: Validate and handle common errors

Common errors: tables not detected, split columns, numbers with local separators. Log exceptions, validate types and key rows, and create fallbacks (e.g., try camelot if tabula fails).

# Simple validations
if combined.empty:
    raise ValueError('Nenhuma linha válida extraída do PDF')
# Example checks
if combined['amount'].isna().sum() > 0:
    print('Attention: values without conversion found')

Step 7: Export to CSV or Parquet for ETL pipeline

Save the result in a format efficient for consumption by downstream ETL steps: CSV for compatibility, Parquet for Big Data performance.

combined.to_csv('relatorio_tabelas.csv', index=False)
combined.to_parquet('relatorio_tabelas.parquet', index=False)

Verify the result

Open the produced CSV/Parquet and confirm: expected number of rows, correct columns, types (date, amount). Sample and compare with the original PDF to ensure values match. Also check warning logs for rows that failed conversion.

Conclusion

By extracting tables from PDFs in ETL you can automate report ingestion and prepare data for analysis. Next steps: integrate this script into a scheduled job, add unit tests and create retries/fallbacks for problematic PDFs. Tip: when one library fails on a page, try switching between tabula-py and camelot for better results.