SQLite Import from CSV, SQL Files, JSON and Excel

.import is the sqlite3 shell's command for loading a file into a table. With --csv it reads standard CSV, quoted fields with commas inside included, and when the table does not exist it creates one using the first line as column names.

.import --csv new_users.csv new_users

The examples use the users table from the 1bench demo database (id INTEGER PRIMARY KEY AUTOINCREMENT, name, email UNIQUE, created_at DEFAULT datetime('now')) and this file, new_users.csv:

name,email
Ada Lovelace,[email protected]
"Hopper, Grace",[email protected]
Alan Turing,

Everything below was run on SQLite 3.43.2 and 3.54.1. Where the two behave differently, the section says so.

Import a CSV into a new table

When the target table is missing, .import --csv creates it from the header and loads the remaining lines. Each column is created without a declared type on 3.54.1 and as TEXT on 3.43.2, and either way every value arrives as text.

sqlite> .import --csv new_users.csv users_import
sqlite> .schema users_import
CREATE TABLE "users_import"(
"name", "email");

That is fine for a quick look at a file, but numbers stay text and sort and compare as text. For real work, create the table with proper types first and import into it, as in the next section.

Import a CSV into an existing table

For a table that already exists, .import treats every line as data, header included. Add --skip 1 to drop the header, or you get a row whose name is "name" and whose email is "email".

.import --csv --skip 1 new_users.csv users_staging

The file must also have exactly as many columns as the table, in the same order. .import cannot fill a subset of columns, so loading new_users.csv straight into users fails on every line:

new_users.csv:2: expected 4 columns but found 2 - filling the rest with NULL
new_users.csv:2: INSERT failed: datatype mismatch

The datatype mismatch comes from the id INTEGER PRIMARY KEY column, which only takes integers. A CSV with an empty id column fails the same way, because .import writes the empty field as '' rather than NULL, so the auto-increment never kicks in.

Example: load the file into a staging table, then copy the rows into users so id and created_at get their defaults.

CREATE TEMP TABLE users_staging (name TEXT, email TEXT);
.import --csv --skip 1 new_users.csv users_staging
INSERT INTO users (name, email)
SELECT name, email FROM users_staging WHERE email <> '';

The WHERE drops Alan Turing, whose email is empty. NOT NULL would not have caught that row, since an empty string is not NULL.

Set the separator: TSV, semicolons and pipes

--csv always splits on commas, so a semicolon file imported with it lands in a single column called "name;email". For any other separator, set the mode and separator first and run .import without --csv.

.mode tabs
.import events.tsv events_raw

.mode csv
.separator ";"
.import export_eu.csv customers_raw

Quote the separator. 3.43 accepts .separator ;, but 3.54 reads the bare semicolon as the end of the command and answers "Usage: .separator COL ?ROW?". With .mode csv plus a separator, quoted fields still work, so "Hopper; Grace";[email protected] keeps the semicolon inside the name.

A stray quote in the middle of an unquoted field produces "unescaped " character" warnings. The row is still imported with the quotes kept as text, so check those rows afterwards.

Import from the command line

Every dot command also works as an argument to sqlite3, which makes imports easy to script.

sqlite3 shop.db ".import --csv --skip 1 new_users.csv users_staging"

.import reads standard input when the file name is /dev/stdin, so you can pipe a download or a decompressed file straight in.

gunzip -c new_users.csv.gz | sqlite3 shop.db ".import --csv /dev/stdin users_import"

Example: load every CSV in a folder into its own table, named after the file.

for f in *.csv; do
  sqlite3 shop.db ".import --csv $f ${f%.csv}_raw"
done

.import -v prints a summary line such as "Added 3 rows with 0 errors using 4 lines of input" when you want a count. --schema picks an attached database, as in .import --csv --schema archive new_users.csv users_import.

Import a SQL file or dump

.read runs every statement in a SQL file, which is how you load a dump made with .dump. From your own shell, redirecting the file into sqlite3 does the same thing.

sqlite3 restored.db ".read dump.sql"
sqlite3 restored.db < dump.sql

A .dump file starts with PRAGMA foreign_keys=OFF; and BEGIN TRANSACTION; and ends with COMMIT;, so it loads in one go. Run it twice against the same database and you get "Parse error near line 3 of dump.sql: table users already exists" for every table, then "UNIQUE constraint failed: users.id" for every row, because sqlite3 reports each error and moves on to the next statement. Add -bail to stop at the first error instead.

sqlite3 -bail restored.db ".read dump.sql"

Dumps from other databases need editing first. A mysqldump table definition fails with near "AUTO_INCREMENT": syntax error, and every INSERT after it fails with "no such table".

Import JSON

SQLite has no .import --json, but json_each turns a JSON array into rows, and the shell's readfile() reads the file. ->> pulls a field out as a plain SQL value.

Example: insert users from a file that holds an array of objects, using the current time when created_at is missing.

INSERT INTO users (name, email, created_at)
SELECT value->>'name',
       value->>'email',
       coalesce(value->>'created_at', datetime('now'))
FROM json_each(readfile('users.json'));

readfile() exists only in the sqlite3 shell. From application code, read the file yourself and pass the text as a parameter, as in FROM json_each(?). The ->> operator needs SQLite 3.38 or later; on older versions use json_extract(value, '$.name').

Import from Excel

The sqlite3 shell cannot read .xlsx files. In Excel, choose File, Save As, CSV UTF-8, then import the CSV as above. Excel puts a byte order mark at the start of that file, and .import strips it, so the first column name comes through clean on both versions tested.

.import --csv customers.csv customers_raw

With several sheets, or to keep number types without creating the table by hand, pandas reads the workbook directly. It needs the openpyxl package.

import sqlite3
import pandas as pd

con = sqlite3.connect("shop.db")
df = pd.read_excel("rollout.xlsx", sheet_name="Rollout")
df.to_sql("rollout_xlsx", con, if_exists="replace", index=False)

Import with Python

The standard library's csv and sqlite3 modules are enough for most imports. with con: wraps the inserts in one transaction, so a failing row rolls back the whole batch and also keeps large loads fast.

import csv
import sqlite3

con = sqlite3.connect("shop.db")
with open("new_users.csv", newline="", encoding="utf-8-sig") as f:
    rows = [(r["name"], r["email"]) for r in csv.DictReader(f) if r["email"]]

with con:
    con.executemany("INSERT INTO users (name, email) VALUES (?, ?)", rows)

utf-8-sig drops the byte order mark Excel adds. The if r["email"] filter skips the row with no email. Turning empty fields into NULL with r["email"] or None instead makes the insert fail with sqlite3.IntegrityError: NOT NULL constraint failed: users.email, and because of with con: nothing is written.

pandas does the same in one line and infers types: to_sql creates INTEGER and REAL columns where the data fits and writes empty cells as NULL.

import sqlite3
import pandas as pd

con = sqlite3.connect("shop.db")
pd.read_csv("new_users.csv").dropna(subset=["email"]).to_sql(
    "users", con, if_exists="append", index=False
)

if_exists decides what happens when the table exists. The default, "fail", raises "Table 'users' already exists"; "append" inserts only the DataFrame's columns, so id and created_at get their defaults; "replace" drops the table and recreates it without your primary key, constraints or indexes. A DataFrame column the table does not have fails with "table users has no column named extra".

Import a table from another database file

ATTACH opens a second SQLite file in the same session, so copying a table between files is a plain INSERT ... SELECT or CREATE TABLE ... AS.

ATTACH 'old_shop.db' AS old;

CREATE TABLE users_archive AS SELECT * FROM old.users;

INSERT OR IGNORE INTO users SELECT * FROM old.users;

CREATE TABLE ... AS copies the columns and their rough types (id INT, name TEXT) but not the primary key, AUTOINCREMENT, UNIQUE or DEFAULT clauses. To keep those, create the table with its full definition first and use INSERT INTO ... SELECT. A plain INSERT of rows whose ids already exist fails with "UNIQUE constraint failed: users.id"; INSERT OR IGNORE skips them, and INSERT OR REPLACE overwrites them.

NULLs and column types

.import never writes NULL. An empty field is stored as an empty string, even in an INTEGER or REAL column, where it stays text because '' cannot be converted to a number.

Example: rollout.csv has an empty rollout_percentage for one feature. Import it into a typed table, then turn the empty strings into NULL.

CREATE TABLE rollout_typed (feature_key TEXT, enabled INTEGER, rollout_percentage REAL);
.import --csv --skip 1 rollout.csv rollout_typed

UPDATE rollout_typed SET rollout_percentage = NULL WHERE rollout_percentage = '';

The typed table matters. In the same file imported with no table created first, SELECT count(*) FROM rollout_raw WHERE rollout_percentage > 50 returns 3 on 3.54 (text is always greater than a number in a column with no type) and 0 on 3.43 (the TEXT column compares '100' to '50' as strings). The right answer, 1, only comes from the REAL column. If you have already imported into text columns, CAST(rollout_percentage AS REAL) fixes a single query, and a new typed table plus INSERT ... SELECT fixes the data.