Jeremy K.

Posted on Sep 21

Extract PDF Tables to SQLite Database with Python

#database #python #programming

Importing tables from PDFs into SQLite is a common data task. Quarterly reports, bank statements, business ledgers—business teams send PDFs, and you need structured data for analysis.

This guide shows a complete Python workflow for:

extracting tables from PDFs
cleaning field names and text
creating SQLite tables dynamically
inserting rows in batches

PDF parsing uses the Free Spire.PDF library. Everything else relies on the standard library, so deployment is simple.

Requirements and Limitations

Install the PDF library:

pip install Spire.Pdf.Free

Import it:

import re
import sqlite3

from spire.pdf import *
from spire.pdf.common import *

Before you start, note two important limitations:

The free version processes only the first 10 pages of a PDF. Content after page 10 is not returned.
Table extraction depends on visible border lines in the PDF. Scanned documents and borderless tables are not supported and require OCR.

Workflow at a Glance

The process has three main steps:

Extract the raw text matrix of every table, page by page.
Normalize and deduplicate headers to create valid column names.
Dynamically create one SQLite table per PDF table and batch-insert the rows.

This approach is useful when a single PDF contains multiple tables with different structures. You do not need to know the column layout in advance.

Step 1: Clean Text and Fix Ligatures

PDFs often replace ligatures with Unicode Private Use Area characters. For example, fi or ff may appear as \ue005 or \ue000.

If inserted directly, these become garbled boxes. Normalize them first.

def normalize_text(text: str) -> str:
    if not text:
        return ""

    ligature_map = {
        '\ue000': 'ff',
        '\ue001': 'ft',
        '\ue002': 'ffi',
        '\ue003': 'ffl',
        '\ue004': 'ti',
        '\ue005': 'fi',
    }

    for k, v in ligature_map.items():
        text = text.replace(k, v)

    return text.strip()

You do not need to map every possible character upfront. Add new mappings as you encounter them.

Step 2: Normalize and Deduplicate Column Names

Raw PDF headers often contain spaces, mixed case, Chinese characters, or symbols. They cannot be used directly as database column names.

This step does two things:

Converts non-alphanumeric characters to underscores.
Falls back to column_N for empty headers.
Resolves duplicate column names, such as two columns both named "Amount".

def normalize_column_name(name: str, index: int) -> str:
    if not name:
        return f"column_{index}"

    name = name.lower()
    name = re.sub(r'[^a-z0-9]+', '_', name).strip('_')

    return name or f"column_{index}"


def deduplicate_columns(columns):
    seen = set()
    result = []

    for col in columns:
        base = col
        count = 1

        while col in seen:
            col = f"{base}_{count}"
            count += 1

        seen.add(col)
        result.append(col)

    return result

The deduplication logic uses an incrementing suffix:

amount
amount_1
amount_2

This prevents columns from overwriting each other.

Step 3: Extract Tables from the PDF

PdfTableExtractor is the core object. Call ExtractTable(page_index) for each page to get all tables on that page.

For each table:

use GetRowCount() and GetColumnCount() to iterate
use GetText(row, col) to read cell text

pdf = PdfDocument()
pdf.LoadFromFile("Quarterly Sales.pdf")

extractor = PdfTableExtractor(pdf)
all_tables = []

for page_index in range(pdf.Pages.Count):
    tables = extractor.ExtractTable(page_index)

    if not tables:
        continue

    for table in tables:
        table_rows = []

        for row in range(table.GetRowCount()):
            row_data = [
                normalize_text(table.GetText(row, col))
                for col in range(table.GetColumnCount())
            ]
            table_rows.append(row_data)

        if table_rows:
            all_tables.append(table_rows)

pdf.Close()

if not all_tables:
    raise ValueError("No tables found in PDF.")

Note the pdf.Close() call. When processing many files, closing the document is important. Otherwise, memory usage will keep growing.

Step 4: Create Tables Dynamically and Insert Rows

Different PDF tables have different columns. The simplest approach is to name each table table_N and generate the DDL dynamically.

All columns are declared as TEXT. Type inference can be handled later during querying.

conn = sqlite3.connect("sales_data.db")
cursor = conn.cursor()

for table_index, table in enumerate(all_tables, start=1):
    if len(table) < 2:
        continue  # Skip tables with headers only

    raw_headers = table[0]

    normalized_headers = [
        normalize_column_name(h, i)
        for i, h in enumerate(raw_headers)
    ]
    normalized_headers = deduplicate_columns(normalized_headers)

    table_name = f"table_{table_index}"

    columns_def = ", ".join(
        [f'"{col}" TEXT' for col in normalized_headers]
    )

    cursor.execute(f"""
        CREATE TABLE IF NOT EXISTS "{table_name}" (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            {columns_def}
        )
    """)

    placeholders = ", ".join(["?" for _ in normalized_headers])
    column_names = ", ".join([f'"{col}"' for col in normalized_headers])

    insert_sql = f"""
        INSERT INTO "{table_name}" ({column_names})
        VALUES ({placeholders})
    """

    batch = []

    for row in table[1:]:
        if not any(row):
            continue

        values = [
            row[i] if i < len(row) else ""
            for i in range(len(normalized_headers))
        ]
        batch.append(values)

    if batch:
        cursor.executemany(insert_sql, batch)

    print(f"Inserted {len(batch)} rows into {table_name}")

conn.commit()
conn.close()

print(f"Processed {len(all_tables)} tables from PDF.")

Implementation Notes

Skip empty tables.

if len(table) < 2 filters out pseudo-tables that contain only headers, or only a single row of empty strings.
Align column counts.

If a row has fewer columns than the header, this expression fills in empty strings:

row[i] if i < len(row) else ""

If a row has more columns than the header, the extra values are discarded. This can happen with merged cells. In production, log rows with abnormal column counts so they can be reviewed later.

Use executemany for batch inserts. Single INSERT statements become slow even on tables with a few hundred rows. Batch execution significantly reduces SQLite transaction overhead.

Final Thoughts

Apart from the PDF parsing library, this solution has no third-party dependencies. It is easy to embed in existing data processing scripts and convenient to run in containers.

If your PDFs are relatively short and have well-structured tables, this code can usually be adapted for production by changing only a few constants.

DEV Community