Moving From Excel to SQLite for a Small Team Step by Step

A small team keeps its orders, clients or equipment in a workbook. It has outgrown the file, but a database server is more than they want to run. SQLite sits in the middle: a real relational database in a single file, with no service to install, no accounts to manage and a command-line tool that takes a minute to learn. This post walks through the move from a workbook to a SQLite database, including cleaning, table design, loading, checking and the ways people will then use it. It is a spoke of when a spreadsheet has become a database.

Is SQLite the right target

SQLite is a library that reads and writes a database file directly. There is no server process. It is reliable, widely deployed and supports most of standard SQL, including joins, transactions and window functions. For a small team it has a clear profile.

Strengths: zero administration, one file to back up, fast reads, a good fit for tens of thousands to millions of rows, included in Python (import sqlite3).

Limits: one writer at a time (many readers are fine), no built-in user accounts or permissions, a network share is a poor place to keep it, and fewer data types than larger systems. A database file shared over SMB by several writers can corrupt, because file locking on network filesystems is unreliable. The SQLite documentation says so plainly.

If the team edits in turns, or only one tool (a script or a small web form) writes, SQLite is fine. If five people edit at once from their desks, a client-server database is the better choice (Access versus SQLite versus PostgreSQL for a small app, and moving from Excel to PostgreSQL step by step).

Step 1: clean and understand the sheet

Before loading, make the data loadable. Fix the structure (one header row, no merged cells, no subtotal rows), convert numbers stored as text, normalize category spellings, and find the key (cleaning Excel data before analysis). Keep the cleaning in a script and the original file untouched.

Then look at the content as a modeler. A typical orders sheet has columns Order, Date, Customer, Customer Address, Product, Qty, Price. That is at least three things: customers, products and orders. Repeating the address on every order is a defect waiting to happen.

Step 2: design the tables

Sketch the tables on paper before writing SQL. For the example:

• customers(id, name, address)

• products(id, name, price)

• orders(id, order_date, customer_id)

• order_lines(order_id, product_id, quantity, unit_price)

Each table has a primary key. Relationships become foreign keys. Prices appear twice, once as the current price on the product and once as the price at the time of the order on the line, because they differ over time. The process of finding this structure is covered in designing tables from a messy Excel sheet.

Create the schema with explicit types and constraints:

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
    id      INTEGER PRIMARY KEY,
    name    TEXT NOT NULL UNIQUE,
    address TEXT
);
CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE,
    price NUMERIC NOT NULL CHECK (price >= 0)
);
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    order_date  TEXT NOT NULL CHECK (order_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'),
    customer_id INTEGER NOT NULL REFERENCES customers(id)
);
CREATE TABLE order_lines (
    order_id   INTEGER NOT NULL REFERENCES orders(id),
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);

SQLite has a few quirks to know. It does not enforce foreign keys unless you run PRAGMA foreign_keys = ON on each connection. Its column types are advisory: a TEXT column accepts an integer unless you add a CHECK or declare the table STRICT (available from version 3.37). It has no date type; store dates as ISO 8601 text (2025-03-14), which sorts correctly. Declaring STRICT makes types enforced and is worth using for new tables.

Step 3: load with Python

Load in dependency order, inside a transaction, so a failure leaves the database empty and not half full:

import sqlite3
import pandas as pd

df = pd.read_excel("orders.xlsx", dtype={"Order": str})
df.columns = ["order", "date", "customer", "address", "product", "qty", "price"]

con = sqlite3.connect("shop.db")
con.execute("PRAGMA foreign_keys = ON")

with con:                                           # one transaction
    customers = df[["customer", "address"]].drop_duplicates("customer")
    con.executemany("INSERT INTO customers(name, address) VALUES (?, ?)",
                    customers.itertuples(index=False, name=None))

    products = df[["product", "price"]].drop_duplicates("product")
    con.executemany("INSERT INTO products(name, price) VALUES (?, ?)",
                    products.itertuples(index=False, name=None))

    cid = dict(con.execute("SELECT name, id FROM customers"))
    pid = dict(con.execute("SELECT name, id FROM products"))

    orders = df[["order", "date", "customer"]].drop_duplicates("order")
    con.executemany("INSERT INTO orders(id, order_date, customer_id) VALUES (?, ?, ?)",
                    [(int(o), d, cid[c]) for o, d, c in orders.itertuples(index=False, name=None)])

    con.executemany("INSERT INTO order_lines VALUES (?, ?, ?, ?)",
                    [(int(r.order), pid[r.product], int(r.qty), r.price) for r in df.itertuples()])

The with con: block commits on success and rolls back on an exception. Parameters with ? placeholders keep text from being interpreted as SQL. If a constraint fails, you get an error naming it: a negative quantity, a missing customer, a duplicate key. Treat each as a data problem to examine; do not loosen the constraint to make the load pass.

For large sheets, df.to_sql("staging", con, index=False) loads a staging table quickly, and SQL INSERT ... SELECT moves the data into the real tables (staging Excel data in a table).

Step 4: check the result

Compare the database with the sheet before anyone relies on it.

SELECT COUNT(*) FROM order_lines;                          -- equals the sheet's data rows
SELECT SUM(quantity * unit_price) FROM order_lines;        -- equals the sheet's total
SELECT COUNT(*) FROM customers;                            -- equals distinct customers

Compare each figure with the same calculation in Excel. Check the orphan cases: no order without lines, no line without an order. Run PRAGMA foreign_key_check; to list any foreign-key violations, and PRAGMA integrity_check; for the file itself. Keep the numbers in a note with the date. This is the proof that the move lost nothing (validating row counts after an Excel import).

Step 5: how people use it

A database nobody can reach is no improvement. Three common ways in:

Read through Excel. Install the SQLite ODBC driver and use Data, Get Data, From Other Sources, From ODBC, with a query. Pivots refresh from the live data. Or export views to CSV on a schedule.

Browse with a GUI. DB Browser for SQLite lets non-programmers look at and edit tables. Editing through it is a manual process with no validation beyond constraints; it is fine for a person or two.

Write through a script or a form. The safest pattern is one program that writes, with validation, and everyone else reading. A small web form, a command-line tool or a Python script that takes a CSV and loads it keeps writes orderly. This also keeps SQLite's single-writer limit from ever mattering.

Backups and where the file lives

Put the database file on a local disk of one machine, not on a shared drive that many open directly. Back it up with the SQLite online backup (sqlite3 shop.db ".backup backup.db") or by copying the file while nothing is writing; copying a file in the middle of a write can produce a bad backup. Schedule it, keep several generations and test a restore. Enable write-ahead logging for better concurrency of readers with a writer: PRAGMA journal_mode = WAL;. WAL does not work well on network filesystems either.

Plan the transition

Run the old and new in parallel for a cycle. Freeze the workbook when you load, tell the team that edits go to the new system from a date, and keep the workbook read-only as the archive. Giving people a quick way to do what they did before, such as an input form or a documented script, matters more than the technology.

When SQLite is too small for the team

Several people edit at once. SQLite serializes writers. With many simultaneous writers, you get "database is locked" errors. A client-server database handles it.

You need permissions. SQLite has no users. If some people must not see some data, use a system with roles.

The file has to sit on a network share. File locking over SMB or NFS is not reliable enough for writes.

You need remote access. Reaching a file from many machines means a server in front of it, and at that point a server database is simpler.

See SQLite's pages on appropriate uses and STRICT tables, and Python's sqlite3 module. To convert a small extract to JSON, use the CSV to JSON converter.