Loading an Excel File Into SQLite With Python and the CLI

SQLite is the easiest database to load a spreadsheet into, because it is a file and needs no server, and the easiest to load wrongly, because it is forgiving about types in a way that rewrites your values. It has no Excel reader at all. This post gets a sheet into SQLite two ways, explains the affinity rule that has caught many people, and converts Excel's date numbers. It belongs to the cluster on getting Excel data into a database.

Route one: pandas to_sql

Python ships with the sqlite3 module, so the only extra pieces are pandas and openpyxl:

import sqlite3
import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders", dtype=str, keep_default_na=False)
df.columns = ["order_no", "customer", "amount_eur", "ordered"]

with sqlite3.connect("orders.db") as conn:
    df.to_sql("stage_orders", conn, if_exists="replace", index=False)
    print(conn.execute("select count(*) from stage_orders").fetchone())

(2,)

With dtype=str, every column is a string, and to_sql creates every column as TEXT. Passing the standard-library connection works with pandas as long as it is a sqlite3 connection; for other databases pandas needs SQLAlchemy. The with block commits the transaction at the end. if_exists="replace" drops and recreates the table, which is right for a staging table that you rebuild on every load, and wrong for a final table with constraints and indexes that you want to keep. For those, create the table first and use if_exists="append".

Route two: the sqlite3 shell and a CSV

If you prefer the command line, save the sheet as CSV UTF-8 and import it:

sqlite3 orders.db

.mode csv
.import --csv orders.csv stage_orders
.schema stage_orders
select count(*) from stage_orders;

When the table does not exist, .import creates it using the first row as column names, with every column of type TEXT. When the table exists, the first row is imported as data unless you skip it with --skip 1, a trap that adds the header as a data row. Read the .schema output after the first import to see what was created. Note that .import is a feature of the shell, not an SQL statement, so you cannot run it through a driver.

Column names with spaces or parentheses, such as Amount (EUR), are created exactly as written and need quoting in every query. Rename them in the CSV header before you import, or load into a table you created with clean names.

The affinity trap

SQLite does not enforce column types the way other databases do. A column has a type affinity that influences how a stored value is converted. A column declared INTEGER or NUMERIC tries to convert text that looks like a number. Insert the text 007 into an INTEGER column and SQLite stores the integer 7. The leading zeros are gone, with no error and no warning. Insert 12abc and it stays text, because it cannot be converted; so a single column can hold integers and text.

You can see it in a tiny experiment:

create table t (a integer, b text);
insert into t values ('007', '007');
select a, typeof(a), b, typeof(b) from t;

7|integer|007|text

The same input gives 7 in the integer column and 007 in the text column. This is why staging tables declare every column TEXT and why identifier columns in the final table should too. For new tables, SQLite 3.37 and later offers STRICT tables, which enforce the declared types and reject a value that cannot be converted:

create table orders (
    order_no   text    not null primary key,
    customer   text    not null,
    amount_eur real,
    ordered_on text
) strict;

Strict tables accept only INT, INTEGER, REAL, TEXT, BLOB and ANY as column types. There is no DECIMAL or DATE. Money is better stored as integer cents than as REAL, since a binary float cannot hold 19.99 exactly: store 1999 and divide when displaying, or keep the text and cast when you aggregate.

Dates: serial numbers to ISO text

SQLite has no date type. It stores dates as ISO 8601 text, as Julian day numbers or as Unix timestamps, and its date functions work on all three. The conventional choice is ISO text such as 2024-01-05, which sorts correctly as text and is readable.

Excel dates arrive as serial numbers counting days from an epoch. For dates after February 1900 in the default date system, 30 December 1899 is day zero, so the date function with a day modifier converts them:

select date('1899-12-30', '+' || ordered || ' days') as ordered_on
from stage_orders;

2024-01-01
2024-01-02

The staged value 45292 gives 2024-01-01. The || operator builds the modifier string from the text, so the staged column can stay as text. If the serial number has a fractional part (a time of day), use datetime with seconds:

select datetime('1899-12-30', '+' || cast(round(45292.5 * 86400) as integer) || ' seconds');

which returns 2024-01-01 12:00:00. Another equivalent form uses Unix time: the serial number for 1 January 1970 is 25569, so date((ordered - 25569) * 86400, 'unixepoch') gives the same result. For files from the 1904 date system, add 1,462 days (Mac and Windows date systems in Excel). The general rules are in converting Excel serial dates to real dates in SQL.

Check that the conversion succeeded: date() returns NULL for input it cannot interpret, which is a convenient way to find bad rows.

select source_row, ordered from stage_orders
where date('1899-12-30', '+' || ordered || ' days') is null;

Any row returned has a value that is not a number of days, such as text typed as a date. Handle those separately (dealing with text that looks like a date in Excel).

Convert into the final table

insert into orders (order_no, customer, amount_eur, ordered_on)
select order_no,
       customer,
       cast(amount_eur as real),
       date('1899-12-30', '+' || ordered || ' days')
from stage_orders;

cast(... as real) converts text that starts with a number, and returns 0.0 for text that does not, without any error. A value of abc becomes 0.0. Validate before casting, with a glob or like test, or compare cast(x as real) converted back to text. A simple guard that catches most problems:

select source_row, amount_eur from stage_orders
where cast(amount_eur as real) = 0.0 and amount_eur not in ('0', '0.0', '0.00');

Any row listed holds text that the cast turned into zero. SQLite will happily insert it.

Check counts and keep the stage

Verify the load the same way as for any other database: compare the staged row count with the sheet's data rows, and a column total with the sheet's total (validating row counts after an Excel import). Because the whole database is one file, backing it up before a load is a file copy, and VACUUM INTO 'backup.db' makes a consistent copy while the database is in use. Keep the stage table for a while, with the file name and a batch id column, so a surprising value can be traced to its source row.

When SQLite is the wrong place for an Excel load

Several writers load at once. SQLite allows one writer at a time. Concurrent loaders queue and may hit "database is locked". Use a server database for shared pipelines.

You need exact decimals. There is no decimal type. Store integer cents or text and cast carefully, or choose a database with numeric.

The workbook has more than a few hundred thousand rows and you need ad hoc analysis. SQLite handles it, but DuckDB is built for analytical scans and can read the sheet directly (loading Excel files with DuckDB).

You rely on strict type checks as constraints. Even strict tables check only the storage class. Date validity, ranges and formats need CHECK constraints you write yourself.

SQLite's rules are described in its pages on datatypes and on date and time functions, and the shell commands in the command line shell reference. To look at a small extract as JSON, try the CSV to JSON converter.