Converting PDF to database is a common requirement in data-driven applications. Many business documents—such as invoices, reports, and financial records—store structured information in PDF format, but this data is not directly usable for querying or analysis.

To make this data accessible, developers often need to convert PDF to SQL by extracting structured content and inserting it into relational databases like SQL Server, MySQL, or PostgreSQL. Manually handling this process is inefficient and error-prone, especially at scale.

In this guide, we focus on extracting table data from PDFs and building a complete pipeline to transform and insert it into an SQL database in Python with Spire.PDF for Python. This approach reflects the most practical and scalable solution for real-world PDF to database workflows.

Quick Navigation

• Understanding the Workflow
• Prerequisites
• Step 1: Extract Table Data from PDF
• Step 2: Transform and Insert Data into Database
• Complete Pipeline: From PDF Extraction to SQL Storage
• Adapting to Other SQL Databases
• Handling Other Types of PDF Data
• Common Pitfalls When Converting PDF Data to a Database
• Conclusion
• FAQ

Understanding the Workflow

Before diving into the implementation, it's important to understand the overall process of converting PDF data into a database.

Instead of treating each operation as completely separate, this workflow can be viewed as two main stages:

Each stage plays a distinct role in the pipeline:

• Extract Tables: Retrieve structured table data from the PDF document

• Process & Store Data: Clean, structure, and insert the extracted data into a relational database

  • Transform Data: Convert raw rows into structured, database-ready records
  • Insert into SQL Database: Persist the processed data into an SQL database

This end-to-end pipeline reflects how most real-world systems handle PDF to database workflows—by first extracting usable data, then processing and storing it in a database for querying and analysis.

Prerequisites

Before getting started, make sure you have the following:

• Python 3.x installed

• Spire.PDF for Python installed:

  pip install Spire.PDF
  

  You can also download Spire.PDF for Python and add it to your project manually.

• A relational database system (e.g., SQLite, SQL Server, MySQL, or PostgreSQL)

This guide demonstrates the workflow using SQLite for simplicity, while also showing how the same approach can be applied to other SQL databases.

Step 1: Extract Table Data from PDF

In most business documents, such as invoices or reports, data is organized in tables. These tables already follow a row-and-column structure, making them ideal for direct insertion into an SQL database.

Table data in PDFs is typically already structured in rows and columns, making it the most suitable format for database storage.

Extract Tables Using Python

Below is an example of how to extract table data from a PDF file using Spire.PDF:

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

# Load PDF document
pdf = PdfDocument()
pdf.LoadFromFile("Quarterly Sales.pdf")

# Method for ligature normalization
def normalize_text(text: str) -> str:
    if not text:
        return text
    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()

table_data = []

# Iterate through pages
for i in range(pdf.Pages.Count): 
    # Extract tables from pages
    extractor = PdfTableExtractor(pdf)
    tables = extractor.ExtractTable(i)
    
    if tables:
        print(f"Page {i} has {len(tables)} tables.")
        for table in tables:
            rows = []
            for row in range(table.GetRowCount()):
                row_data = []
                for col in range(table.GetColumnCount()):
                    text = table.GetText(row, col)
                    text = normalize_text(text)
                    row_data.append(text.strip() if text else "")
                rows.append(row_data)
            table_data.extend(rows)

pdf.Close()

# Print extracted data
for row in table_data:
    print(row)

Below is a preview of the extracting result:

Code Explanation

• LoadFromFile: Loads the PDF document
• PdfTableExtractor: Identifies tables within each page
• GetText(row, col): Retrieves cell content
• table_data: Stores extracted rows as a list of lists

At this stage, the data is extracted but still unstructured in terms of database usage. Once the table data is extracted, we need to convert it into a structured format for SQL insertion.

Alternatively, you can export the extracted data to a CSV file for validation or batch import. See: Convert PDF Tables to CSV in Python

Step 2: Transform and Insert Data into Database

Raw table data extracted from PDFs often requires cleaning and structuring before it can be inserted into an SQL database.

For simplicity, the following examples demonstrate how to process a single extracted table. In real-world scenarios, PDFs may contain multiple tables, which can be handled using the same logic in a loop.

Transform Data (Single Table Example)

structured_data = []

# Assume first row is header
headers = table_data[0]

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

    record = {}
    for i in range(len(headers)):
        value = row[i] if i < len(row) else ""
        record[headers[i]] = value

    structured_data.append(record)

# Preview structured data
for item in structured_data:
    print(item)

What This Step Does

• Converts rows into dictionary-based records
• Maps column headers to values
• Filters out empty rows
• Prepares structured data for database insertion

You can also:

• Normalize column names for SQL compatibility
• Convert numeric fields
• Standardize date formats

Transforming raw PDF data into a structured format ensures it can be reliably inserted into a relational database. After transformation, the data is immediately ready for database insertion, which completes the pipeline.

Insert Data into SQLite (Single Table Example)

Using the structured data from a single table, we can dynamically create a database schema and insert records without hardcoding column names.

import sqlite3

# Connect to SQLite database
conn = sqlite3.connect("sales_data.db")
cursor = conn.cursor()

# Create table dynamically based on headers
columns_def = ", ".join([f'"{h}" TEXT' for h in headers])

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

# Prepare insert statement
placeholders = ", ".join(["?" for _ in headers])
column_names = ", ".join([f'"{h}"' for h in headers])

# Insert data
for record in structured_data:
    values = [record.get(h, "") for h in headers]
    cursor.execute(f"""
    INSERT INTO invoices ({column_names})
    VALUES ({placeholders})
    """, values)

# Commit and close
conn.commit()
conn.close()

Key Points

• Dynamically creates database tables based on extracted headers
• Uses parameterized queries (?) to prevent SQL injection
• Keeps the schema flexible without hardcoding column names
• Column names can be normalized to ensure SQL compatibility
• Batch inserts can improve performance for large datasets

This section demonstrates the core workflow for converting PDF table data into a relational database using a single table example. In the next section, we extend this approach to handle multiple tables automatically.

Complete Pipeline: From PDF Extraction to SQL Storage

Here's a complete runnable example that demonstrates the entire workflow from PDF to database:

from spire.pdf import *
from spire.pdf.common import *
import sqlite3
import re

# ---------------------------
# Utility Functions
# ---------------------------

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()


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


# ---------------------------
# Step 1: Extract Tables (STRUCTURED)
# ---------------------------

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

extractor = PdfTableExtractor(pdf)

all_tables = []

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

    if tables:
        for table in tables:
            table_rows = []

            for row in range(table.GetRowCount()):
                row_data = []
                for col in range(table.GetColumnCount()):
                    text = table.GetText(row, col)
                    row_data.append(normalize_text(text))
                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.")

# ---------------------------
# Step 2 & 3: Process + Insert Each Table
# ---------------------------

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

for table_index, table in enumerate(all_tables):

    if len(table) < 2:
        continue  # skip invalid tables

    raw_headers = table[0]

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

    # Generate table name
    table_name = f"table_{table_index+1}"

    # Create table
    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}
    )
    """)

    # Prepare insert
    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})
    """

    # Insert data
    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.")

Below is a preview of the insertion result in the database:

This complete example demonstrates the full PDF to database pipeline:

1. Load and extract table data from PDF using Spire.PDF
2. Transform raw data into structured records
3. Insert into SQLite database with proper schema

SQLite automatically creates a system table called sqlite_sequence when using AUTOINCREMENT to track the current maximum ID. This is expected behavior and does not affect your data. You can run this code directly to convert PDF table data into a database.

Adapting to Other SQL Databases

While this guide uses SQLite for simplicity, the same approach works for other SQL databases. The extraction and transformation steps remain identical—only the database connection and insertion syntax vary slightly.

The following examples assume you are using the normalized column names (headers) generated in the previous step.

SQL Server Example

import pyodbc

# Connect to SQL Server
conn_str = (
    "DRIVER={SQL Server};"
    "SERVER=your_server_name;"
    "DATABASE=your_database_name;"
    "UID=your_username;"
    "PWD=your_password"
)
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# Generate dynamic column definitions using normalized headers
columns_def = ", ".join([f"[{h}] NVARCHAR(MAX)" for h in headers])

# Create table dynamically
cursor.execute(f"""
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'invoices')
BEGIN
    CREATE TABLE invoices (
        id INT IDENTITY(1,1) PRIMARY KEY,
        {columns_def}
    )
END
""")

# Prepare insert statement
placeholders = ", ".join(["?" for _ in headers])
column_names = ", ".join([f"[{h}]" for h in headers])

# Insert data
for record in structured_data:
    values = [record.get(h, "") for h in headers]
    cursor.execute(f"""
    INSERT INTO invoices ({column_names})
    VALUES ({placeholders})
    """, values)

# Commit and close
conn.commit()
conn.close()

MySQL Example

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)
cursor = conn.cursor()

# Use the same dynamic table creation and insert logic as shown earlier,
# with minor syntax adjustments if needed

PostgreSQL Example

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="your_database",
    user="your_username",
    password="your_password"
)
cursor = conn.cursor()

# Use the same dynamic table creation and insert logic as shown earlier,
# with minor syntax adjustments if needed

The core extraction and transformation steps remain the same across different SQL databases, especially when using normalized column names for compatibility.

Handling Other Types of PDF Data

While this guide focuses on table extraction, PDFs often contain other types of data that can also be integrated into a database, depending on your use case.

Text Data (Unstructured → Structured)

In many documents, important information such as invoice numbers, customer names, or dates is embedded in plain text rather than tables.

You can extract raw text using:

from spire.pdf import *

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

for i in range(pdf.Pages.Count):
    page = pdf.Pages.get_Item(i)
    extractor = PdfTextExtractor(page)
    options = PdfTextExtractOptions()
    options.IsExtractAllText = True
    text = extractor.ExtractText(options)
    print(text)

However, raw text cannot be directly inserted into a database. It typically requires parsing into structured fields, for example:

• Using regular expressions to extract key-value pairs
• Identifying patterns such as dates, IDs, or totals
• Converting text into dictionaries or structured records

Once structured, the data can be inserted into a database as part of the same transformation and insertion pipeline described earlier.

For more advanced techniques, you can learn more in the detailed Python PDF text extraction guide.

Images (OCR or File Reference)

Images in PDFs are usually not directly usable as structured data, but they can still be integrated into database workflows in two ways:

Option 1: OCR (Recommended for data extraction) Convert images to text using OCR tools, then process and store the extracted content.

Option 2: File Storage (Recommended for document systems) Store images as:

• File paths in the database
• Binary (BLOB) data if needed

Below is an example of extracting images:

from spire.pdf import *

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

helper = PdfImageHelper()

for i in range(pdf.Pages.Count):
    page = pdf.Pages.get_Item(i)
    images = helper.GetImagesInfo(page)
    for j, img in enumerate(images):
        img.Image.Save(f"image_{i}_{j}.png")

To further process image-based content, you can use OCR to extract text from images with Spire.OCR for Python.

Full PDF Storage (BLOB or File Reference)

In some scenarios, the goal is not to extract structured data, but to store the entire PDF file in a database.

This is commonly used in:

• Document management systems
• Archival systems
• Compliance and auditing workflows

You can store PDFs as:

• BLOB data in the database
• File paths referencing external storage

This approach represents another meaning of "PDF in database", but it is different from structured data extraction.

Key Takeaway

While PDFs can contain multiple types of content, table data remains the most efficient and scalable format for database integration. Other data types typically require additional processing before they can be stored or queried effectively.

Common Pitfalls When Converting PDF Data to a Database

While the process of converting PDF to a database may seem straightforward, several practical challenges can arise.

1. Inconsistent Table Structures

Not all PDFs follow a consistent table format:

• Missing columns
• Merged cells
• Irregular layouts

Solution:

• Validate row lengths
• Normalize structure
• Handle missing values

2. Poor Table Detection

Some PDFs do not define tables properly internally, such as no grid structure or irregular cell sizes.

Solution:

• Test with multiple files
• Use fallback parsing logic
• Preprocess PDFs if needed

3. Data Cleaning Issues

Extracted data may contain:

• Extra spaces
• Line breaks
• Formatting issues

Solution:

• Strip whitespace
• Normalize values
• Validate types

4. Character Encoding Issues (Ligatures & Fonts)

PDF table extraction can introduce unexpected characters due to font encoding and ligatures. For example, common letter combinations such as:

• fi, ff, ffi, ffl, ft, ti

may be stored as single glyphs in the PDF. When extracted, they may appear as:

di\ue000erence   → difference
o\ue002ce        → office
\ue005le         → file

These are typically private Unicode characters (e.g., \ue000–\uf8ff) caused by custom font mappings.

Solution:

• Detect private Unicode characters (\ue000–\uf8ff)

• Build a mapping table for ligatures, such as:

  • \ue000 → ff
  • \ue001 → ft
  • \ue002 → ffi
  • \ue003 → ffl
  • \ue004 → ti
  • \ue005 → fi

• Normalize text before inserting into the database

• Optionally log unknown characters for further analysis

Handling encoding issues properly ensures data accuracy and prevents subtle corruption in downstream processing.

5. Cross-Page Table Fragmentation

Large tables in PDFs are often split across multiple pages. When extracted, each page may be treated as a separate table, leading to:

• Broken datasets
• Repeated headers
• Incomplete records

Solution:

• Compare column counts between consecutive tables
• Check header consistency or data type patterns in the first row
• Merge tables when structure and schema match
• Skip duplicated header rows when concatenating data

In practice, combining column structure and value pattern detection provides a reliable way to reconstruct full tables across pages.

6. Database Schema Mismatch

Incorrect mapping between extracted data and database columns can cause errors.

Solution:

• Align headers with schema
• Use explicit field mapping

7. Performance Issues with Large Files

Processing large PDFs can be slow.

Solution:

• Use batch processing
• Optimize insert operations

By anticipating these issues, you can build a more reliable PDF to database workflow.

Conclusion

Converting PDF to a database is not a one-step operation, but a structured process involving extracting data and processing it for database storage (including transformation and insertion)

By focusing on table data and using Python, you can efficiently implement a complete PDF to database pipeline, making it easier to automate data integration tasks.

This approach is especially useful for handling invoices, reports, and other structured business documents that need to be stored in SQL Server or other relational databases.

If you want to evaluate the performance of Spire.PDF for Python and remove any limitations, you can apply for a 30-day free trial.

FAQ

What does "PDF to database" mean?

It refers to the process of extracting structured data from PDF files and storing it in a database. This typically involves parsing PDF content, transforming it into structured formats, and inserting it into SQL databases for further querying and analysis.

Can Python convert PDF directly to a database?

No. Python cannot directly convert a PDF into a database in one step. The process usually involves extracting data from the PDF first, transforming it into structured records, and then inserting it into a database using SQL connectors.

How do I convert PDF to SQL using Python?

The typical workflow includes:

1. Extracting table or text data from the PDF
2. Converting it into structured records (rows and columns)
3. Inserting the processed data into an SQL database such as SQLite, MySQL, or SQL Server using Python database libraries

Can I store PDF files directly in a database?

Yes. PDF files can be stored as binary (BLOB) data in a database. However, this approach is mainly used for document storage systems, while structured extraction is preferred for data analysis and querying.

What SQL databases can I use for PDF data integration?

You can use almost any SQL database, including SQLite, SQL Server, MySQL, and PostgreSQL. The overall extraction and transformation process remains the same, while only the database connection and insertion syntax differ slightly.

Importing an Excel file in Python typically involves more than just reading the file. In most cases, the data needs to be converted into Python structures such as lists, dictionaries, or other formats that can be directly used in your application.

This transformation step is important because Excel data is usually stored in a tabular format, while Python applications often require structured data for processing, integration, or storage. Depending on how the data will be used, it may be represented as a list for sequential processing, a dictionary for field-based access, custom objects for structured modeling, or a database for persistent storage.

This guide demonstrates how to import Excel file in Python and convert the data into multiple structures using Spire.XLS for Python, with practical examples for each approach.

Overall Implementation Approach and Quick Example

Importing Excel data into Python is essentially a two-step process:

1. Load Excel file – Load the Excel file and access its raw data
2. Transform data – Convert the data into Python structures such as lists, dictionaries, or objects

This separation is important because in real-world applications, simply reading Excel is not enough—the data must be transformed into a format that can be processed, stored, or integrated into systems.

Key Components

When importing Excel data using Spire.XLS for Python, the following components are involved:

• Workbook – Represents the entire Excel file and is responsible for loading data from disk
• Worksheet – Represents a single sheet within the Excel file
• CellRange – Represents a group of cells that contain actual data
• Data Transformation Layer – Your Python logic that converts cell values into target structures

Data Flow Overview

The typical workflow looks like this:

Excel File → Workbook → Worksheet → CellRange → Python Data Structure

Understanding this pipeline helps you design flexible import logic for different scenarios.

Quick Example: Import Excel File in Python

Before running the example, install Spire.XLS for Python using pip:

pip install spire.xls

If needed, you can also download Spire.XLS for Python manually and include it in your project.

The following example shows the simplest way to import Excel data into Python:

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("SalesReport.xlsx")

data = []
sheet = workbook.Worksheets[0]

# Get the used cell range
cellRange = sheet.AllocatedRange

# Get the data from the first row
for col in range(cellRange.Columns.Count):
    data.append(sheet.Range[1, col +1].Value)

print(data)

workbook.Dispose()

Below is a preview of the data imported from the Excel file:

This minimal example demonstrates the fundamental workflow: initialize a workbook, load the Excel file, access the worksheet and cell data, and then dispose of the workbook to release resources.

For more advanced scenarios, such as reading Excel files from memory or handling file streams, see how to import Excel data from a stream in Python.

Import Excel Data in Python as a List

One of the simplest ways to import Excel data in Python is to convert it into a list of rows. This structure is useful for iteration and basic data processing.

Example

from spire.xls import *

# Load the Workbook
workbook = Workbook()
workbook.LoadFromFile("SalesReport.xlsx")

# Get the used range in the first worksheet
sheet = workbook.Worksheets[0]
cellRange = sheet.AllocatedRange

# Create a list to store the data
data = []
for row_index in range(cellRange.RowCount):
    row_data = []
    for cell_index in range(cellRange.ColumnCount):
        row_data.append(cellRange[row_index + 1, cell_index + 1].Value)
    data.append(row_data)

workbook.Dispose()

Technical Explanation

Importing Excel data as a list treats each row in the worksheet as a Python list, preserving the original row order.

How the code works:

• A nested loop is used to traverse the worksheet in a row-first (row-major) pattern
• The outer loop iterates through rows, while the inner loop accesses each cell
• Index offsets (+1) are applied because Spire.XLS uses 1-based indexing

Why this design works:

• AllocatedRange limits iteration to only populated cells, improving efficiency
• Row-by-row extraction keeps the structure consistent with Excel’s layout
• The intermediate row_data list ensures clean aggregation before appending

This structure is ideal for sequential processing, simple transformations, or as a base format before converting into dictionaries or objects.

If you want to load more than just text and numeric data, see How to Read Excel Files in Python for more data types.

Import Excel Data as a Dictionary in Python

If your Excel file contains headers, importing it as a dictionary provides better data organization and access by column names.

Example

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("SalesReport.xlsx")

sheet = workbook.Worksheets[0]
cellRange = sheet.AllocatedRange

rows = list(cellRange.Rows)

headers = [cellRange[1, cell_index + 1].Value for cell_index in range(cellRange.ColumnCount)]

data_dict = []
for row in rows[1:]:
    row_dict = {}
    for i, cell in enumerate(row.Cells):
        row_dict[headers[i]] = cell.Value
    data_dict.append(row_dict)

workbook.Dispose()

Technical Explanation

Importing Excel data as a dictionary converts each row into a key-value structure using column headers.

How the code works:

• The first row is extracted as headers
• Each subsequent row is iterated and processed
• Cell values are mapped to headers using their column index

Why this design works:

• Both headers and row cells follow the same column order, enabling simple index-based mapping
• This removes reliance on fixed column positions
• The result is a self-descriptive structure with named fields

This method is useful when you need structured data access, such as working with JSON, APIs, or labeled datasets.

Import Excel Data into Custom Objects

For structured applications, you may need to import Excel data into Python objects to maintain type safety and encapsulate business logic.

Example

class Employee:
    def __init__(self, name, age, department):
        self.name = name
        self.age = age
        self.department = department

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("EmployeeData.xlsx")

sheet = workbook.Worksheets[0]
cellRange = sheet.AllocatedRange

employees = []
for row in list(cellRange.Rows)[1:]:
    name = row.Cells[0].Value
    age = int(row.Cells[1].Value) if row.Cells[1].Value else None
    department = row.Cells[2].Value

    emp = Employee(name, age, department)
    employees.append(emp)

workbook.Dispose()

Technical Explanation

Importing Excel data into objects maps each row to a structured class instance.

How the code works:

• A class is defined to represent the data model
• Each row is read and its values are extracted
• Values are passed into the class constructor to create objects

Why this design works:

• The constructor acts as a controlled transformation point
• It allows validation, type conversion, or preprocessing
• Data is no longer loosely structured, but aligned with domain logic

This is ideal for applications with clear data models, such as backend systems or business logic layers.

Import Excel Data to Database in Python

In many applications, Excel data needs to be stored in a database for persistent storage and querying.

Example

import sqlite3
from spire.xls import *

# Connect to SQLite database
conn = sqlite3.connect("sales.db")
cursor = conn.cursor()

# Create table matching the Excel structure
cursor.execute("""
CREATE TABLE IF NOT EXISTS sales (
    product TEXT,
    category TEXT,
    region TEXT,
    sales REAL,
    units_sold INTEGER
)
""")

# Load the Excel file
workbook = Workbook()
workbook.LoadFromFile("Sales.xlsx")

# Access the first worksheet
sheet = workbook.Worksheets[0]
rows = list(sheet.AllocatedRange.Rows)

# Iterate through rows (skip header row)
for row in rows[1:]:
    product = row.Cells[0].Value
    category = row.Cells[1].Value
    region = row.Cells[2].Value

    # Remove thousand-separators and convert to float
    sales_text = row.Cells[3].Value
    sales = float(str(sales_text).replace(",", "")) if sales_text else 0

    # Convert units sold to integer
    units_text = row.Cells[4].Value
    units_sold = int(units_text) if units_text else 0

    # Insert data into the database
    cursor.execute(
        "INSERT INTO sales VALUES (?, ?, ?, ?, ?)",
        (product, category, region, sales, units_sold)
    )

# Commit changes and close connection
conn.commit()
conn.close()

# Release Excel resources
workbook.Dispose()

Here is a preview of the Excel data and the SQLite database structure:

Technical Explanation

Importing Excel data into a database converts each row into a persistent record.

How the code works:

• A database connection is established and a table is created
• The table schema is aligned with the Excel structure
• Each row is read and inserted using parameterized SQL queries

Why this design works:

• Schema alignment ensures consistent data mapping
• Data normalization (e.g., numeric conversion) improves compatibility
• Parameterized queries provide safety and proper type handling

When to use this approach:

This approach is suitable for data storage, querying, and integration into larger data pipelines.

For a more detailed guide on importing Excel data into Databases, check out How to Transfer Data Between Excel Files and Databases.

Why Use Spire.XLS for Importing Excel Data

The examples in this guide use Spire.XLS for Python because it provides a clear and consistent way to access and transform Excel data. The main advantages in this context include:

• Structured Object Model The library exposes components such as Workbook, Worksheet, and CellRange, which align directly with how Excel data is organized. This makes the data flow easier to understand and implement. See more details on Spire.XLS for Python API Reference.

• Focused Data Access Layer Instead of handling low-level file parsing, you can work directly with cell values and ranges, allowing the import logic to focus on data transformation rather than file structure.

• Format Compatibility It supports common Excel formats, such as XLS and XLSX, and other spreadsheet formats, such as CSV, ODS, and OOXML, enabling the same import logic to be applied across different file types.

• No External Dependencies Excel files can be processed without requiring Microsoft Excel to be installed, which is important for backend services and automated environments.

Common Pitfalls

Incorrect File Path

Ensure the Excel file path is correct and accessible from your script. Use absolute paths or verify the current working directory.

import os
print(os.getcwd())  # Check current directory

Missing Headers

When importing as a dictionary, verify that your Excel file has headers in the first row. Otherwise, the keys will be incorrect.

Memory Management

Always dispose of the workbook object after processing to release resources, especially when processing large files.

workbook.Dispose()

Data Type Conversion

Excel cells may return different data types than expected. Validate and convert data types as needed for your application.

Import vs Read Excel in Python

In Python, "reading" and "importing" Excel files refer to related but distinct steps in data processing.

Read Excel focuses on accessing raw file content. This typically involves retrieving cell values, rows, or specific ranges without changing how the data is structured.

Import Excel includes both reading and transformation. After extracting the data, it is converted into structures such as lists, dictionaries, objects, or database records so that it can be used directly within an application.

In practice, reading is a subset of importing. The distinction lies in the goal—reading retrieves data, while importing prepares it for use.

Conclusion

Importing Excel file in Python is not just about reading data—it's about converting it into structures that your application can use effectively. In this guide, you learned how to import Excel file in Python as a list, convert Excel data into dictionaries, map Excel data into Python objects, and import Excel data into a database.

With Spire.XLS for Python, you can easily import Excel data into different structures with minimal code. The library provides a consistent API for handling various Excel formats and complex content, making it suitable for a wide range of data processing scenarios.

To evaluate the full performance of Spire.XLS for Python, you can apply for a 30 day trial license.

FAQ

What does it mean to import Excel file in Python?

Importing Excel means converting Excel data into Python structures such as lists, dictionaries, or databases for further processing and integration into your applications.

How do I import Excel data into Python?

You can use libraries like Spire.XLS for Python to load Excel files and convert their content into usable Python data structures. The process involves loading the workbook, accessing the worksheet, and iterating through cells to extract data.

Can I import Excel data into a database using Python?

Yes, you can read Excel data and insert it into databases like SQLite, MySQL, or PostgreSQL using Python. This approach is commonly used for data migration and backend system integration.

What is the best structure for importing Excel data?

The best structure depends on your use case. Lists are suitable for simple iteration, dictionaries for structured data access by column names, objects for type safety and business logic, and databases for persistent storage and querying.

Do I need Microsoft Excel installed to import Excel files in Python?

No, libraries like Spire.XLS for Python work independently and do not require Microsoft Excel to be installed on the system.

Developers often need to include Python code inside Word documents for technical documentation, tutorials, code reviews, internal reports, or client deliverables. While copying and pasting code manually works for small snippets, automated solutions provide better consistency, formatting control, and scalability — especially when working with long scripts or multiple files.

This tutorial demonstrates multiple practical methods to export Python code into Word documents using Python. Each method has its own strengths depending on whether you prioritize formatting, automation, syntax highlighting, or readability.

On This Page:

• Install Required Libraries
• Export Python Code to Word as Plain Text
  • Method 1. Insert Raw Python Code into a Word Document
  • Method 2. Generate a Word File from Markdown-Wrapped Code
• Add Syntax-Highlighted Python Code to Word
  • Method 1. Use RTF for Preformatted Code Blocks
  • Method 2. Render Highlighted Code via HTML Formatting
  • Method 3. Insert Syntax-Highlighted Code as Images
• Conclusion
• FAQs

Install Required Libraries

Install the necessary dependencies before running the examples:

pip install spire.doc pygments

Library Overview:

• Spire.Doc for Python — used to create and manipulate Word documents programmatically
• Pygments — used to generate syntax-highlighted code in RTF, HTML, or image formats
• Pathlib (built-in) — used for reading Python files from disk
• textwrap (built-in) — used to wrap long code lines before generating images formatting

Export Python Code to Word as Plain Text

Plain text insertion is the most straightforward method for embedding code in Word. It keeps scripts fully editable and preserves formatting such as indentation and line breaks.

Method 1. Insert Raw Python Code into a Word Document

This method reads a .py file and inserts the code directly into Word while applying a monospace font style.

from pathlib import Path
from spire.doc import *

# Read Python file
code_string = Path("demo.py").read_text(encoding="utf-8")

# Create a Word document
doc = Document()

# Add a section
section = doc.AddSection()
section.PageSetup.Margins.All = 60

# Add a paragraph
paragraph = section.AddParagraph()

# Insert code string to the paragraph
paragraph.AppendText(code_string)

# Create a paragraph style
style = ParagraphStyle(doc)
style.Name = "code"
style.CharacterFormat.FontName = "Consolas"
style.CharacterFormat.FontSize = 12
style.ParagraphFormat.LineSpacing = 12
doc.Styles.Add(style)

# Apply the style to the paragraph
paragraph.ApplyStyle("code")

# Save the document
doc.SaveToFile("Output.docx", FileFormat.Docx2019)
doc.Dispose()

How It Works:

This technique treats Python code as plain text and inserts it directly into a Word paragraph. The script reads the .py file using Path.read_text(), preserving indentation, blank lines, and overall structure.

After inserting the text, a custom paragraph style is created and applied. The use of a monospace font such as Consolas ensures alignment and readability, while fixed line spacing maintains consistent formatting across lines.

Because no intermediate format is used, this is the simplest and fastest approach. However, it does not provide syntax highlighting or semantic styling—Word only displays the code as formatted text.

Output:

You May Also Like: Generate Word Documents Using Python

Method 2. Generate a Word File from Markdown-Wrapped Code

If your workflow already uses Markdown, wrapping Python code inside fenced blocks provides a structured way to convert scripts into Word documents.

from pathlib import Path
from spire.doc import *

# Read Python file
code = Path("demo.py").read_text(encoding="utf-8")

# Convert to Markdown
md_content = f"```python\n{code}\n```"
Path("temp.md").write_text(md_content, encoding="utf-8")

# Load Markdown into Word
doc = Document()
doc.LoadFromFile("temp.md")

# Update page settings
doc.Sections[0].PageSetup.Margins.All = 60

# Save as a DOCX file
doc.SaveToFile("Output.docx", FileFormat.Docx)
doc.Dispose()

How It Works:

Instead of inserting text directly, this method wraps Python code inside Markdown fenced code blocks. The generated Markdown file is then loaded into Word using Spire.Doc’s Markdown parsing capability.

When Word imports Markdown, it automatically preserves code formatting such as indentation and line breaks. This approach is useful when your documentation workflow already uses Markdown or when code needs to coexist with headings, lists, and descriptive text.

Since Markdown itself does not inherently apply syntax coloring inside Word, the result is still plain code formatting—but the structure is cleaner and easier to manage within technical documentation pipelines.

Output:

Add Syntax-Highlighted Python Code to Word

Syntax highlighting makes code easier to read and understand. By integrating Pygments, Python scripts can be converted into stylized formats before being embedded into Word.

This section explores three approaches — RTF, HTML, and image rendering — each with different strengths depending on your formatting goals.

Method 1. Use RTF for Preformatted Code Blocks

RTF allows syntax-highlighted code to remain fully editable within Word.

from pathlib import Path
from pygments import highlight
from pygments.lexers import PythonLexer
from pygments.formatters import RtfFormatter
from spire.doc import *

# Read Python file
code = Path("demo.py").read_text(encoding="utf-8")

# Set font
formatter = RtfFormatter(fontface ="Consolas")

# Specify the lexer
rtf_text = highlight(code, PythonLexer(), formatter)
rtf_text = rtf_text.replace(r"\f0", r"\f0\fs24") # font size (24 for 12-point font)

# Create a Word document
doc = Document()

# Add a section
section = doc.AddSection()
section.PageSetup.Margins.All = 60

# Add a paragraph
paragraph = section.AddParagraph()

# Insert the syntax-highlighted code as RTF
paragraph.AppendRTF(rtf_text)

# Save the document
doc.SaveToFile("Output.docx", FileFormat.Docx2019)
doc.Dispose()

How It Works:

Pygments analyzes Python syntax using a lexer, identifying tokens such as keywords, strings, and comments. The RTF formatter applies styling rules that represent colors and fonts using RTF control words.

The resulting RTF string is inserted directly into Word using AppendRTF(). Because RTF is a native Word-compatible format, the document preserves fonts, colors, and spacing without requiring additional rendering steps.

Font size is controlled by modifying RTF control words (e.g., \fs24), allowing precise control over appearance. This method produces editable, selectable code with syntax highlighting inside Word.

Output:

Method 2. Render Highlighted Code via HTML Formatting

HTML rendering provides visually rich syntax highlighting and automatic text wrapping.

from pathlib import Path
from pygments import highlight
from pygments.lexers import PythonLexer
from pygments.formatters import HtmlFormatter
from spire.doc import *

# Read Python file
code = Path("demo.py").read_text(encoding="utf-8")

# Generate HTML from the Python code with syntax highlighting
html_text = highlight(code, PythonLexer(), HtmlFormatter(full=True))

# Create a Word document
doc = Document()

# Add a section
section = doc.AddSection()
section.PageSetup.Margins.All = 60

# Add a paragraph
paragraph = section.AddParagraph()

# Add the HTML string to the paragraph
paragraph.AppendHTML(html_text)

# Save the document
doc.SaveToFile("Output.docx", FileFormat.Docx2019)
doc.Dispose()

How It Works:

Here, Pygments converts Python code into styled HTML using the HtmlFormatter. The HTML output includes inline styles or CSS rules that represent syntax colors and formatting.

Spire.Doc then interprets the HTML content and renders it into Word. During this process, HTML elements are translated into Word formatting structures, allowing the highlighted code to appear visually similar to web-based code blocks.

This approach is ideal when code originates from web content, static documentation sites, or Markdown-to-HTML workflows.

Output:

You May Also Like: Convert HTML to Word DOC or DOCX in Python

Method 3. Insert Syntax-Highlighted Code as Images

For scenarios where visual consistency matters more than editability, code can be rendered as an image before insertion.

from pathlib import Path
import textwrap
from pygments import highlight
from pygments.lexers import PythonLexer
from pygments.formatters import ImageFormatter
from spire.doc import *

# Read Python file
code = Path("demo.py").read_text(encoding="utf-8")

# Wrap long lines manually
def wrap_code_lines(code_text, max_width=75):
    wrapped_lines = []
    for line in code_text.splitlines():
        if len(line) > max_width:
            wrapped_lines.extend(textwrap.wrap(
                line,
                width=max_width,
                replace_whitespace=False,
                drop_whitespace=False
            ))
        else:
            wrapped_lines.append(line)
    return "\n".join(wrapped_lines)

code = wrap_code_lines(code, max_width=75)

# Step 3: Generate image
formatter = ImageFormatter(
    font_name="Consolas",
    font_size=18,
    scale=2,            
    image_pad=10,
    line_pad=2,
    background_color="#ffffff"
)

img_bytes = highlight(code, PythonLexer(), formatter)

with open("code.png", "wb") as f:
    f.write(img_bytes)

# Create a Word document
doc = Document()
section = doc.AddSection()
section.PageSetup.Margins.All = 60

# Insert into Word
paragraph = section.AddParagraph()
picture = paragraph.AppendPicture("code.png")

# Ensure image fits page width
page_width = (
    section.PageSetup.PageSize.Width
    - section.PageSetup.Margins.Left
    - section.PageSetup.Margins.Right
)
picture.Width = page_width

# Save the document
doc.SaveToFile("Output.docx", FileFormat.Docx2019)
doc.Dispose()

How It Works:

This method renders Python code as an image instead of editable text. Pygments generates a syntax-highlighted bitmap using the ImageFormatter, allowing full visual control over fonts, colors, padding, and DPI.

Since image rendering does not automatically wrap long lines, the script manually wraps lengthy code lines using Python’s textwrap module before generating the image. This prevents oversized images that exceed page width.

After inserting the image into Word, its width is dynamically resized to fit the printable page area. Because the code is embedded as a graphic, it preserves exact visual appearance across platforms and prevents formatting inconsistencies—but the text is no longer editable.

Output:

Conclusion

Converting Python code to Word documents can be achieved through several approaches depending on your goals. Plain text methods provide simplicity and flexibility, while RTF and HTML techniques offer powerful syntax highlighting with selectable text. Image-based code blocks deliver consistent visual formatting but require careful line wrapping and scaling.

For most documentation workflows:

• Use plain text for editable technical content
• Use HTML or RTF for syntax-highlighted documentation
• Use images when formatting consistency is critical

FAQs

Which method is best for tutorials?

HTML or RTF methods provide clear syntax highlighting with selectable text.

How can I preserve indentation and blank lines?

Read the .py file using .read_text() without stripping or modifying lines.

Why do image-based code blocks become too small?

Word scales images to fit page width. Increasing the image formatter’s scale or adjusting the wrapping width can improve readability.

Can readers copy code from Word?

Yes — except when code is inserted as an image.

Do I need Markdown for conversion?

No. Markdown is optional but useful when working with documentation pipelines.

Can I export the generated document as a PDF file?

Yes. When saving the document, simply specify PDF as the output format in the Document.SaveToFile() method.

Get a Free License

To fully experience the capabilities of Spire.Doc for Python without any evaluation limitations, you can request a 30-day trial license.

CSV (Comma-Separated Values) files are the backbone of data exchange across industries—from data analysis to backend systems. They’re lightweight, human-readable, and compatible with almost every tool (Excel, Google Sheets, databases). If you’re a developer seeking a reliable way to create a CSV file in Python, Spire.XLS for Python is a powerful library that simplifies the process.

In this comprehensive guide, we'll explore how to generate a CSV file in Python with Spire.XLS, covering basic CSV creation and advanced use cases like list to CSV and Excel to CSV conversion.

What You’ll Learn

• Installation and Setup
• Basic: Create a Simple CSV File in Python
• Dynamic Data: Generate CSV from a List of Dictionaries in Python
• Excel-to-CSV: Generate CSV From an Excel File in Python
• Best Practices for CSV Creation
• FAQ: Create CSV in Python

Installation and Setup

Getting started with Spire.XLS for Python is straightforward. Follow these steps to set up your environment:

Step 1: Ensure Python 3.6 or higher is installed.

Step 2: Install the library via pip (the official package manager for Python):

pip install Spire.XLS

Step 3 (Optional): Request a temporary free license to test full features without any limitations.

Basic: Create a Simple CSV File in Python

Let’s start with a simple scenario: creating a CSV file from scratch with static data (e.g., a sales report). The code below creates a new workbook, populates it with data, and saves it as a CSV file.

from spire.xls import *
from spire.xls.common import *

# 1. Create a new workbook
workbook = Workbook()
    
# 2. Get the first worksheet (default sheet)
worksheet = workbook.Worksheets[0]

# 3. Populate data into cells
# Header row
worksheet.Range["A1"].Text = "ProductID"
worksheet.Range["B1"].Text = "ProductName"
worksheet.Range["C1"].Text = "Price"
worksheet.Range["D1"].Text = "QuantitySold"

worksheet.Range["A2"].NumberValue = 101
worksheet.Range["B2"].Text = "Wireless Headphones"
worksheet.Range["C2"].NumberValue = 79.99
worksheet.Range["D2"].NumberValue = 250

worksheet.Range["A3"].NumberValue = 102
worksheet.Range["B3"].Text = "Bluetooth Speaker"
worksheet.Range["C3"].NumberValue = 49.99
worksheet.Range["D3"].NumberValue = 180

# Save the worksheet to CSV
worksheet.SaveToFile("BasicSalesReport.csv", ",", Encoding.get_UTF8())
workbook.Dispose()

Core Workflow

• Initialize Core object: Workbook() creates a new Excel workbook, Worksheets[0] accesses the target sheet.
• Fill data into cells: Use .Text (for strings) and .NumberValue (for numbers) to ensure correct data types.
• Export & cleanup: SaveToFile() exports the worksheet to CSV , and Dispose() prevents memory leaks.

Output:

The resulting BasicSalesReport.csv will look like this:

Dynamic Data: Generate CSV from a List of Dictionaries in Python

In real-world scenarios, data is often stored in dictionaries (e.g., from APIs/databases). The code below converts a list of dictionaries to a CSV:

from spire.xls import *
from spire.xls.common import *

# Sample data (e.g., from a database/API)
customer_data = [
    {"CustomerID": 1, "Name": "John Doe", "Email": "john@example.com", "Country": "USA"},
    {"CustomerID": 2, "Name": "Maria Garcia", "Email": "maria@example.es", "Country": "Spain"},
    {"CustomerID": 3, "Name": "Li Wei", "Email": "wei@example.cn", "Country": "China"}
]

# 1. Create workbook and worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]

# 2. Write headers (extract keys from the first dictionary)
headers = list(customer_data[0].keys())
for col_idx, header in enumerate(headers, start=1):
    worksheet.Range[1, col_idx].Text = header  # Row 1 = headers

# 3. Write data rows
for row_idx, customer in enumerate(customer_data, start=2):  # Start at row 2
    for col_idx, key in enumerate(headers, start=1):
        # Handle different data types (text/numbers)
        value = customer[key]
        if isinstance(value, (int, float)):
            worksheet.Range[row_idx, col_idx].NumberValue = value
        else:
            worksheet.Range[row_idx, col_idx].Text = value

# 4. Save as CSV
worksheet.SaveToFile("CustomerData.csv", ",", Encoding.get_UTF8())
workbook.Dispose()

This example is ideal for JSON to CSV conversion, database dumps, and REST API data exports. Key advantages include:

• Dynamic Headers: Automatically extracts headers from the keys of the first dictionary in the dataset.
• Scalable: Seamlessly adapts to any volume of dictionaries or key-value pairs (perfect for dynamic data).
• Clean Output: Preserves the original order of dictionary keys for consistent CSV structure.

The generated CSV file:

Excel-to-CSV: Generate CSV From an Excel File in Python

Spire.XLS excels at converting Excel (XLS/XLSX) to CSV in Python. This is useful if you have Excel reports and need to export them to CSV for data pipelines or third-party tools.

from spire.xls import *

# 1. Initialize a workbook instance
workbook = Workbook()

# 2. Load a xlsx file
workbook.LoadFromFile("Expenses.xlsx")

# 3. Save Excel as a CSV file
workbook.SaveToFile("XLSXToCSV.csv", FileFormat.CSV)
workbook.Dispose()

Conversion result:

Note: By default, SaveToFile() converts only the first worksheet. For converting multiple sheets to separate CSV files, refer to the comprehensive guide: Convert Excel (XLSX/XLS) to CSV in Python – Batch & Multi-Sheet

Best Practices for CSV Creation

Follow these guidelines to ensure robust and professional CSV output:

1. Validate Data First: Clean empty rows/columns before exporting to CSV.
2. Use UTF-8 Encoding: Always specify UTF-8 encoding (Encoding.get_UTF8()) to support international characters seamlessly.
3. Batch Process Smartly: For 100k+ rows, process data in chunks (avoid loading all data into memory at once).
4. Choose the Correct Delimiter: Be mindful of regional settings. For European users, use a semicolon (;) as the delimiter to avoid locale issues.
5. Dispose Objects: Release workbook/worksheet resources with Dispose() to prevent memory leaks.

Conclusion

Spire.XLS simplifies the process of leveraging Python to generate CSV files. Whether you're creating reports from scratch, converting Excel workbooks, or handling dynamic data from APIs and databases, this library delivers a robust and flexible solution.

By following this guide, you can easily customize delimiters, specify encodings such as UTF-8, and manage data types—ensuring your CSV files are accurate, compatible, and ready for any application. For more advanced features, you can explore the Spire.XLS for Python tutorials.

FAQ: Create CSV in Python

Q1: Why choose Spire.XLS over Python’s built-in csv module?

A: While Python's csv module is excellent for basic read/write operations, Spire.XLS offers significant advantages:

• Better data type handling: Automatic distinction between text and numeric data.
• Excel Compatibility: Seamlessly converts between Excel (XLSX/XLS) and CSV—critical for teams using Excel as a data source.
• Advanced Customization: Supports customizing the delimiter and encoding of the generated CSV file.
• Batch processing: Efficient handling of large datasets and multiple files.
• Cross-Platform Support: Works on Windows, macOS, and Linux (no Excel installation required).

Q2: Can I use Spire.XLS for Python to read CSV files?

A: Yes. Spire.XLS supports parsing CSV files and extracting their data. Details refer to: How to Read CSV Files in Python: A Comprehensive Guide

Q3: Can Spire.XLS convert CSV files back to Excel format?

A: Yes! Spire.XLS supports bidirectional conversion. A quick example:

from spire.xls import *

# Create a workbook
workbook = Workbook()

# Load a CSV file
workbook.LoadFromFile("sample.csv", ",", 1, 1)

# Save CSV as Excel
workbook.SaveToFile("CSVToExcel.xlsx", ExcelVersion.Version2016)

Q4: How do I change the CSV delimiter?

A: The SaveToFile() method’s second parameter controls the delimiter:

# Semicolon (for European locales): 
worksheet.SaveToFile("EU.csv", ";", Encoding.get_UTF8())
# Tab (for tab-separated values/TSV)
worksheet.SaveToFile("TSV_File.csv", "\t", Encoding.get_UTF8())