PLEX
2025-12-01

Migrating an Excel Spreadsheet into a Fully Fledged Operational Database with Python

Databases, Python · Dorian Sotpyrc

From one fragile Excel file to a repeatable database pipeline

Many teams have a single Excel workbook that quietly runs a chunk of the business: a Customer_Master.xlsx, an orders tracker, a stock log. It works, until it doesn’t: someone overwrites a sheet, two versions diverge, or a formula error goes unnoticed for months.

In this walkthrough we’ll migrate a multi-sheet Excel workbook into a proper relational database using Python, pandas and SQLAlchemy. We’ll build a small but reusable migration tool, not just a one-off script, and we’ll keep everything configuration-driven so you can adapt it to your own workbook.

GitHub repo for this article

All the code in this article lives in the public repository: excel-to-operational-database-python . Clone it and follow along, or point it at your own workbook once you’re comfortable with the pattern.

Abstract Python and data illustration representing a migration pipeline from Excel to a relational database
We’ll turn a business-critical Excel workbook into a repeatable, validated database migration pipeline using Python.

The working example: customers, products and orders

To keep things concrete, we’ll use a simple but realistic example: a small business workbook with three sheets:

  • customers – who you sell to
  • products – what you sell
  • orders – which customer ordered which product, when and how much

In the GitHub repo, the sample workbook lives at data/raw/sample_workbook.xlsx. Each sheet is a clean table:

Example sheet structure

customers sheet (excerpt)
customer_id name email joined_date
1 Alice Smith [email protected] 2023-01-15
2 Bob Jones [email protected] 2023-02-01
3 Charlie Kim [email protected] 2023-03-10
products sheet (excerpt)
product_id name category unit_price
10 Premium Widget Widgets 49.90
11 Standard Widget Widgets 29.50
12 Service Plan Support 9.99
orders sheet (excerpt)
order_id customer_id product_id order_date quantity
1001 1 10 2023-04-01 1
1002 1 12 2023-04-01 1
1003 2 11 2023-04-05 2
Swap in your own workbook later

The demo workbook is deliberately simple so you can see the pattern clearly. Once you understand the flow, you can point the pipeline at your own multi-sheet workbook by updating a single YAML configuration file.

Designing a simple relational schema from the workbook

Before writing any Python, it’s worth sketching a relational schema that matches the meaning of the Excel sheets. Each sheet becomes a table, and we add primary keys and relationships explicitly.

SCHEMA 

customers
---------
customer_id  (PK, int)
name         (string, not null)
email        (string, nullable)
joined_date  (datetime, nullable)

products
--------
product_id   (PK, int)
name         (string, not null)
category     (string, nullable)
unit_price   (decimal/float, nullable)

orders
------
order_id     (PK, int)
customer_id  (FK -> customers.customer_id)
product_id   (FK -> products.product_id)
order_date   (datetime, nullable)
quantity     (int, nullable)

This is the schema implemented in both the GitHub repo’s documentation and example configuration. It’s simple enough to follow by inspection, but it’s also “database shaped” enough that you can build realistic queries and reports on top of it.

Think in entities and relationships

A good starting point is to ask: “What is a row in this sheet actually describing?” If the answer is “one customer”, “one product” or “one order”, it probably wants its own table with a clear primary key and named foreign keys where sheets refer to each other.

Capturing the design in YAML configuration

Instead of hard-coding sheet names and column mappings in Python, the repo describes the migration in a YAML file:

YAML 

# config/config.yaml
excel_path: "data/raw/sample_workbook.xlsx"
database_url: "sqlite:///data/db/app.db"

sheets:
  customers:
    table: customers
    index_column: customer_id
    dtypes:
      customer_id: int
      name: string
      email: string
      joined_date: date

  products:
    table: products
    index_column: product_id
    dtypes:
      product_id: int
      name: string
      category: string
      unit_price: decimal

  orders:
    table: orders
    index_column: order_id
    dtypes:
      order_id: int
      customer_id: int
      product_id: int
      order_date: date
      quantity: int

The excel_path points at the workbook, database_url specifies where to write the results (SQLite in this case), and the sheets block maps each sheet to a target table, index column and target data types.

Config-first, not script spaghetti

Treat the YAML file as the single source of truth for how your workbook maps to tables. As your Excel evolves, you can update the config instead of editing Python code in multiple places.

Repository structure: a reusable Excel→database migration tool

The GitHub repo is structured as a small, reusable Python package rather than a single ad-hoc script:

TEXT 

excel-to-operational-database-python/
  src/
    __init__.py
    config.py
    extract_excel.py
    transform_clean.py
    load_database.py
    validate_data.py
    schema_design.py
    models.py
    pipeline.py
    cli.py
  config/
    config.example.yaml
    config.yaml
  data/
    raw/
      sample_workbook.xlsx
    db/
      app.db
    intermediate/
      ...
  docs/
    schema-diagram.md
    migration-notes.md
  scripts/
    run_example_migration.sh
  tests/
    test_transform_clean.py
    test_validate_data.py

You can browse the full tree and code on GitHub: github.com/dorian-sotpyrc/excel-to-operational-database-python . The rest of this article walks through the most important pieces.

Step 1: Load configuration and Excel sheets

The first job is to load the YAML configuration and read the configured sheets from the workbook. That logic lives in src/config.py and src/extract_excel.py.

PYTHON 

# src/config.py (excerpt)
from pathlib import Path
from typing import Any, Dict

import yaml


class ConfigError(Exception):
    """Raised when the configuration file is invalid."""


def load_config(path: str | Path) -> Dict[str, Any]:
    cfg_path = Path(path)
    if not cfg_path.exists():
        raise ConfigError(f"Config file not found: {cfg_path}")

    with cfg_path.open("r", encoding="utf8") as f:
        cfg = yaml.safe_load(f) or {}

    for key in ("excel_path", "database_url", "sheets"):
        if key not in cfg:
            raise ConfigError(f"Config missing required key: {key}")

    excel_path = Path(cfg["excel_path"])
    cfg["excel_path"] = excel_path

    sheets = cfg.get("sheets", {})
    if not isinstance(sheets, dict) or not sheets:
        raise ConfigError("'sheets' must be a non-empty mapping")

    return cfg
PYTHON 

# src/extract_excel.py (excerpt)
from pathlib import Path
from typing import Dict

import pandas as pd


def extract_sheets(
    excel_path: Path,
    sheets_cfg: dict,
) -> Dict[str, pd.DataFrame]:
    if not excel_path.exists():
        raise FileNotFoundError(f"Excel file not found: {excel_path}")

    frames: Dict[str, pd.DataFrame] = {}

    for sheet_name in sheets_cfg.keys():
        df = pd.read_excel(excel_path, sheet_name=sheet_name)
        frames[sheet_name] = df

    return frames
  1. Describe your workbook in YAML
    Point excel_path at your workbook, choose a database_url, and define each sheet’s columns and types.
  2. Load the config safely
    load_config validates that required keys exist and converts excel_path into a real Path object.
  3. Extract sheets into DataFrames
    extract_sheets reads only the configured sheets into pandas DataFrames ready for cleaning.

Step 2: Clean and type your data with pandas

Real-world workbooks often have messy column names, mixed types and stray empty rows. The transform_clean.py module normalises headers, coerces types according to the config and drops fully empty rows.

PYTHON 

# src/transform_clean.py (excerpt)
import pandas as pd


def _normalise_column_name(name: str) -> str:
    return (
        str(name)
        .strip()
        .lower()
        .replace(" ", "_")
        .replace("-", "_")
    )


def _apply_type(df: pd.DataFrame, column: str, kind: str) -> pd.Series:
    kind = kind.lower()
    series = df[column]

    if kind in {"int", "integer"}:
        series = pd.to_numeric(series, errors="coerce").astype("Int64")
    elif kind in {"float", "decimal", "number"}:
        series = pd.to_numeric(series, errors="coerce")
    elif kind in {"string", "str", "text"}:
        series = series.astype("string").str.strip()
    elif kind in {"bool", "boolean"}:
        series = series.astype("boolean")
    elif kind in {"date", "datetime"}:
        series = pd.to_datetime(series, errors="coerce")
    elif kind in {"category", "categorical"}:
        series = series.astype("category")

    return series
PYTHON 

def clean_single_sheet(df_raw, sheet_name: str, cfg: dict):
    df = df_raw.copy()
    df.columns = [_normalise_column_name(c) for c in df.columns]

    dtypes_cfg: dict[str, str] = cfg.get("dtypes", {}) or {}
    if dtypes_cfg:
        missing = [c for c in dtypes_cfg.keys() if c not in df.columns]
        if missing:
            raise ValueError(
                f"Sheet '{sheet_name}' missing expected columns: {missing}"
            )

        df = df[list(dtypes_cfg.keys())]

        for col, kind in dtypes_cfg.items():
            df[col] = _apply_type(df, col, kind)

    df = df.dropna(how="all").reset_index(drop=True)

    index_col = cfg.get("index_column")
    if index_col and index_col not in df.columns:
        raise ValueError(
            f"index_column '{index_col}' not found in cleaned sheet '{sheet_name}'"
        )

    return df, {"rows_clean": len(df)}
Where to put your own rules

If you need to drop rows with missing IDs, cap outliers or normalise categories, this is the right layer. Extend clean_single_sheet so the transforms stay close to the configuration and data types.

Step 3: Load into SQLite with SQLAlchemy

Once the DataFrames are clean, we use SQLAlchemy to connect to SQLite and pandas’ to_sql to write each sheet into its target table.

PYTHON 

# src/load_database.py (excerpt)
from typing import Dict, Any

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.engine import Engine


def create_db_engine(database_url: str) -> Engine:
    return create_engine(database_url, future=True)


def load_frames_to_db(
    frames: Dict[str, pd.DataFrame],
    sheets_cfg: Dict[str, Any],
    engine: Engine,
    if_exists: str = "replace",
) -> Dict[str, int]:
    inserted_counts: Dict[str, int] = {}

    for sheet_name, df in frames.items():
        cfg = sheets_cfg.get(sheet_name, {})
        table_name = cfg.get("table", sheet_name)

        df.to_sql(
            table_name,
            engine,
            if_exists=if_exists,
            index=False,
        )
        inserted_counts[table_name] = len(df)

    return inserted_counts

For the example we use SQLite because it’s easy to run locally and ship in a repo, but you can point database_url at PostgreSQL, MySQL or another supported database and keep the same pipeline.

Step 4: Validate that the migration actually worked

A successful script run is not the same as a correct migration. The repo includes a basic validation step that compares the expected row counts (from the load step) with the rows actually present in the database.

PYTHON 

# src/validate_data.py (excerpt)
from typing import Dict, Any

from sqlalchemy import text
from sqlalchemy.engine import Engine


def validate_row_counts(
    engine: Engine,
    expected_rows: Dict[str, int],
) -> Dict[str, Any]:
    results: Dict[str, Any] = {}
    with engine.connect() as conn:
        for table_name, expected in expected_rows.items():
            count = conn.execute(
                text(f"SELECT COUNT(*) FROM {table_name}")
            ).scalar_one()
            results[table_name] = {
                "expected": int(expected),
                "actual": int(count),
                "match": int(count) == int(expected),
            }
    return results

The pipeline.py module ties everything together and returns a summary dictionary that includes transform stats, load stats, row-count results and a high-level validation summary.

PYTHON 

# src/pipeline.py (excerpt)
from .config import load_config
from .extract_excel import extract_sheets
from .transform_clean import transform_all
from .load_database import create_db_engine, load_frames_to_db
from .validate_data import validate_row_counts, summarise_validation
from .schema_design import describe_schema


def run_pipeline(config_path, dry_run: bool = False, intermediate_dir="data/intermediate"):
    cfg = load_config(config_path)
    excel_path = cfg["excel_path"]
    database_url = cfg["database_url"]
    sheets_cfg = cfg["sheets"]

    frames_raw = extract_sheets(excel_path, sheets_cfg)
    cleaned_frames, transform_stats = transform_all(
        frames_raw,
        sheets_cfg,
        intermediate_dir=intermediate_dir,
    )

    if dry_run:
        return {
            "mode": "dry_run",
            "excel_path": str(excel_path),
            "database_url": database_url,
            "schema": describe_schema(sheets_cfg),
            "transform_stats": transform_stats,
            "load_stats": {},
            "row_counts": {},
            "validation_summary": {},
        }

    engine = create_db_engine(database_url)
    load_stats = load_frames_to_db(cleaned_frames, sheets_cfg, engine)
    row_counts = validate_row_counts(engine, load_stats)
    validation_summary = summarise_validation(row_counts)

    return {
        "mode": "full",
        "excel_path": str(excel_path),
        "database_url": database_url,
        "schema": describe_schema(sheets_cfg),
        "transform_stats": transform_stats,
        "load_stats": load_stats,
        "row_counts": row_counts,
        "validation_summary": validation_summary,
    }

Step 5: Run the CLI – dry-run first, then full migration

You don’t have to import anything in a notebook to run the pipeline. The repo exposes a small command-line interface in src/cli.py so you can run migrations directly from the terminal.

PYTHON 

# src/cli.py (excerpt)
import argparse
import json

from .pipeline import run_pipeline


def main(argv: list[str] | None = None) -> None:
    parser = argparse.ArgumentParser(
        description="Migrate an Excel workbook into a relational database using Python."
    )
    subparsers = parser.add_subparsers(dest="command", required=True)

    migrate = subparsers.add_parser("migrate", help="Run the Excel -> DB migration pipeline.")
    migrate.add_argument(
        "--config", "-c",
        type=str,
        default="config/config.yaml",
    )
    migrate.add_argument(
        "--dry-run",
        action="store_true",
        help="Run extract/transform only, without writing to the database.",
    )
    migrate.add_argument(
        "--json",
        action="store_true",
        help="Print a JSON summary instead of human-readable text.",
    )

    args = parser.parse_args(argv)

    if args.command == "migrate":
        result = run_pipeline(args.config, dry_run=args.dry_run)

        if args.json:
            print(json.dumps(result, indent=2, default=str))
            return

        # ... print human-readable summary ...


if __name__ == "__main__":
    main()

With a virtual environment activated and dependencies installed, you can run:

BASH 

# Clone the repo
git clone [email protected]:dorian-sotpyrc/excel-to-operational-database-python.git
cd excel-to-operational-database-python

python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

# Dry-run (no database writes)
python -m src.cli migrate --config config/config.yaml --dry-run

# Full migration
python -m src.cli migrate --config config/config.yaml
Helper script in the repo

The repository includes a helper script, scripts/run_example_migration.sh, which runs a dry-run first and then a full migration. It’s a good starting point for wiring this pipeline into your own scheduler or deployment process.

Visualising row-count validation

For the sample workbook, each table ends up with three rows. The pipeline’s expected row counts and the counts queried from the database match exactly, which is what you want to see after a migration.

Expected vs actual row counts after migration
Expected rows Actual rows
Download chart data (CSV)
0 1 2 3 customers products orders Table Row count

In the sample workbook, all three tables end up with three rows each, and the validation step confirms that expected and actual row counts match. For larger migrations, the same pattern helps you quickly spot missing or duplicated records.

Adapting the pipeline to your own Excel workbooks

Once you’ve run the example end-to-end, you can start pointing the tool at your own workbooks by updating configuration rather than rewriting code.

Pre-migration checklist

Run through this quick checklist before trusting your first migration:

  • Each sheet you want to migrate has a clear “one row = one thing” meaning.
  • There is a stable column you can use as a primary key for each table.
  • Cross-sheet references (e.g. customer IDs in orders) are understood.
  • You have a backup copy of the original workbook.
  • The YAML configuration matches your sheet names and column names.
  • A dry-run completes successfully and the transform stats look sensible.

Changing the config, not the code

To migrate a different workbook, you typically only need to:

  • Update excel_path to point at your file.
  • Adjust the sheets mapping for any new or renamed sheets.
  • Update dtypes if your columns/types differ.

The Python code remains unchanged. This is the main benefit of a configuration-first approach: your migration logic becomes a reusable internal tool instead of a tangle of one-off scripts.

Moving beyond SQLite

For production, you might want your operational database to live in PostgreSQL, MySQL or SQL Server instead of a local SQLite file. In most cases, you only need to change database_url in the YAML file:

  • PostgreSQL: postgresql+psycopg2://user:password@host:5432/dbname
  • MySQL: mysql+pymysql://user:password@host:3306/dbname

Because the pipeline uses SQLAlchemy, most of the code doesn’t care which database you choose, as long as the required drivers are installed.

Extending validation rules

Row counts are a good baseline, but they’re not the whole story. You can build extra migration checks into validate_data.py, for example:

  • Verifying that all orders.customer_id values exist in customers.
  • Checking that primary keys are unique and not null.
  • Flagging suspicious dates (far in the future or before your business existed).
  • Enforcing that quantities are non-negative.

Common pitfalls when migrating Excel to a database

A few issues come up repeatedly when people move from spreadsheets to an operational database:

  • Columns that mix numbers and text, making type conversion tricky.
  • Merged cells or header rows that break the “one row per record” assumption.
  • Hidden rows and columns with stale data that suddenly reappear in the database.
  • IDs that are not truly unique, or that change over time, causing duplicate key issues.
  • Dates stored as free-form text in inconsistent formats.
When to pause and redesign

If your workbook relies heavily on merged cells, manually-coloured “section breaks” or formulas that blur the notion of a row as a single record, consider building a cleaner staging workbook or redesigning the underlying data structure before you migrate. A database will force you to be explicit.

Wrap-up: from one-off import to repeatable migration standard

Migrating from Excel to a database doesn’t have to mean rewriting everything or buying a heavyweight ETL tool. A small, configuration-driven Python pipeline can give you:

  • An explicit schema and mapping from sheets to tables.
  • A repeatable process for loading and cleaning data.
  • Built-in validation so you can trust the results.

The repository behind this article is meant to be a starting point you can adapt for your own organisation: github.com/dorian-sotpyrc/excel-to-operational-database-python . Clone it, run the sample migration, then point it at the workbook that’s been running too much of your business for too long.

Related PLEX reading

Excel vs Database: When to Make the Switch
Learn how to tell when your growing spreadsheets become a risk, and how to decide if a proper database is the right next step for your team.
Read
Introduction to Null Hypothesis Testing with Python
A walkthrough of statistical testing in Python using real data and the same PLEX code style used in this article.
Read
Where does money come from?
A research-based explainer on modern money creation, central banks and commercial bank deposits.
Read

References & further reading