How to Import a CSV File into SQLite (Command Line and Python)

Ninad Pathak

October 7, 2026

To run SQL on a CSV file, the rows first have to be in a table. The sqlite3 shell can load a CSV in one command. In this article I’ll walk you through importing a CSV into a properly typed SQLite table, plus the Python version for when you need more control.

TLDR

A single command imports a CSV and creates the table from its header: sqlite3 shop.db ".import --csv users.csv users".
A table created this way has only TEXT columns. For real types, create the table first and import with --skip 1 to drop the header row.

Empty fields come in as empty strings. Turn them into NULL with an UPDATE afterwards.
For other delimiters, use .mode csv and .separator ; before .import.
In Python, read the file with csv.DictReader and insert with executemany() in one transaction.

What is .import and what will we build?

.import is a command of the sqlite3 shell that reads a CSV file into a table. The shell reads input “according to the RFC 4180 specification”, so quoted fields with commas inside them work (sqlite3 CLI: importing CSV).

To show you how it works, I will load a small customer export, users.csv, into a database called shop.db, first quickly and then with proper column types.

Prerequisites

What	Version used here
`sqlite3` shell	3.51.0
Python (optional, last step)	3.9.6, standard library only

Importing the CSV file

Each step below builds on the one before, in the same shop.db.

Step 1: Look at the CSV file

Besides the header row, it has a name with a comma inside quotes and one empty email:

$ cat users.csv
id,name,email,signup_date,orders
1,Ada Lovelace,ada@example.com,2026-01-15,3
2,"Hopper, Grace",grace@example.com,2026-02-02,12
3,Linus Torvalds,,2026-03-20,0

Step 2: Import it in one command

If the table doesn’t exist, .import creates it and uses “the content of the first input row” for the column names (sqlite3 CLI). That’s fast, but every column is TEXT, so orders is stored as text:

$ sqlite3 shop.db ".import --csv users.csv users_raw"
sqlite> .schema users_raw
CREATE TABLE IF NOT EXISTS "users_raw"(
"id" TEXT, "name" TEXT, "email" TEXT, "signup_date" TEXT,
 "orders" TEXT);
sqlite> SELECT name, email, orders, typeof(orders) FROM users_raw;
name            email              orders  typeof(orders)
--------------  -----------------  ------  --------------
Ada Lovelace    ada@example.com    3       text
Hopper, Grace   grace@example.com  12      text
Linus Torvalds                     0       text

Step 3: Import into a typed table

For real types, create the table first:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT,
  signup_date TEXT,
  orders INTEGER NOT NULL DEFAULT 0
);

When the table already exists, every row of the file is treated as data, including the header. --skip 1 drops it. Now orders comes in as an integer:

$ sqlite3 shop.db < users_table.sql
$ sqlite3 shop.db ".import --csv --skip 1 users.csv users"
sqlite> SELECT id, name, email, orders, typeof(orders) FROM users;
id  name            email              orders  typeof(orders)
--  --------------  -----------------  ------  --------------
1   Ada Lovelace    ada@example.com    3       integer
2   Hopper, Grace   grace@example.com  12      integer
3   Linus Torvalds                     0       integer

Step 4: Turn empty fields into NULL

.import stores an empty field as an empty string, not NULL. Fix that after the import:

sqlite> UPDATE users SET email = NULL WHERE email = '';
sqlite> SELECT id, name, email FROM users WHERE email IS NULL;
id  name            email
--  --------------  -----
3   Linus Torvalds  NULL

Step 5: Import from Python instead

When you need to clean values on the way in, Python’s csv module gives you full control. executemany() inside with con: inserts every row in one transaction:

import csv
import sqlite3

con = sqlite3.connect("shop.db")
con.execute("""CREATE TABLE IF NOT EXISTS customers (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT, orders INTEGER)""")

with open("users.csv", newline="", encoding="utf-8") as f:
    rows = [
        (int(r["id"]), r["name"], r["email"] or None, int(r["orders"]))
        for r in csv.DictReader(f)
    ]
with con:  # one transaction: all rows or none
    con.executemany("INSERT INTO customers VALUES (?, ?, ?, ?)", rows)

for row in con.execute("SELECT id, name, email, orders FROM customers"):
    print(row)

Running it

Run the script from the folder that holds users.csv:

$ python3 import_csv.py
(1, 'Ada Lovelace', 'ada@example.com', 3)
(2, 'Hopper, Grace', 'grace@example.com', 12)
(3, 'Linus Torvalds', None, 0)

To check a round trip, export the table back to CSV with the shell’s -csv mode:

$ sqlite3 -header -csv shop.db "SELECT id, name, orders FROM users;" > out.csv
$ cat out.csv
id,name,orders
1,"Ada Lovelace",3
2,"Hopper, Grace",12
3,"Linus Torvalds",0

Common problems

Each of these was reproduced in the test run.

The header row ends up as data

Importing into an existing table without --skip 1 inserts the header as a row. Because id is a plain INTEGER column here, the text id was stored without an error:

sqlite> CREATE TABLE users2 (id INTEGER, name TEXT, email TEXT, signup_date TEXT, orders INTEGER);
$ sqlite3 shop.db ".import --csv users.csv users2"
sqlite> SELECT id, name, typeof(id) FROM users2 LIMIT 2;
id  name          typeof(id)
--  ------------  ----------
id  name          text
1   Ada Lovelace  integer

The file uses semicolons, not commas

Set CSV mode and the separator before .import:

$ cat cities.csv
id;name;city
1;Ana;Lisbon
2;Ben;Berlin
$ sqlite3 shop.db ".mode csv" ".separator ;" ".import cities.csv cities"
sqlite> SELECT * FROM cities;
id  name  city
--  ----  ------
1   Ana   Lisbon
2   Ben   Berlin

Rows with too many or too few fields

.import warns and keeps going. Extra fields are dropped, and missing ones become NULL:

$ cat ragged.csv
id,name
1,Ada
2,Grace,extra
3
$ sqlite3 shop.db ".import --csv ragged.csv ragged"
ragged.csv:3: expected 2 columns but found 3 - extras ignored
ragged.csv:4: expected 2 columns but found 1 - filling the rest with NULL
sqlite> SELECT * FROM ragged;
id  name
--  -----
1   Ada
2   Grace
3   NULL

Check the warnings after every import, or validate the file in Python first.

Conclusion

For a quick look at a CSV, .import --csv with an automatic table is enough. For data you’ll keep, create a typed table first and import with --skip 1. Then turn empty strings into NULL. Reach for Python when values need cleaning on the way in.