How to import a CSV file into SQLite
Import CSV into SQLite with .import or Python, keep real number types instead of TEXT, skip the header row and turn empty fields into NULL.
Quick Answer
Create the table with real types first, then import and skip the header row:
sqlite3 shop.db <<'SQL'
CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER, signup TEXT, note TEXT);
.import --csv --skip 1 people.csv people
UPDATE people SET age = NULLIF(age, ''); -- empty CSV fields arrive as '', not NULL
SQL
If the table doesn't exist, .import --csv creates it from the header row, but every column is TEXT.
Import with the sqlite3 shell
.import has two behaviours depending on whether the table already exists:
| Command | Table exists? | What happens |
|---|---|---|
.import --csv people.csv people | No | Creates it from the header row, all columns TEXT |
.import --csv --skip 1 people.csv people | Yes | Loads the rows into your typed columns, skipping the header |
.import --csv people.csv people | Yes | Loads the header line as a data row |
sqlite> .import --csv people.csv people
sqlite> .schema people
CREATE TABLE IF NOT EXISTS "people"(
"id" TEXT, "name" TEXT, "age" TEXT, "signup" TEXT,
"note" TEXT);
The --csv flag handles quoted fields, commas and line breaks inside quotes, and doubled "" quotes. On older shells without it, run .mode csv before .import.
In VisuaLeaf
Import a CSV with real column types
The all-TEXT problem in our findings below happens because the shell has no type information to apply. In VisuaLeaf you set the types as part of the import.
- Right-click the table, or the database, in your SQLite connection and choose Import Data.
- Pick CSV as the source, set the delimiter, and mark the first line as a header so it is not imported as a row.
- In field mapping, convert columns from string to number or date, so an age column compares as a number rather than as text.
- The live preview shows what the job will write for a real source row, and Check job runs the mapping on sample rows without writing anything.
Save it as a job to repeat the same import on a schedule. See Export and import.
What we found testing this
A 5-row CSV with a quoted comma, an embedded line break, doubled quotes, an empty age and the age 041:
- An auto-created table stores numbers as text, and comparisons go wrong. Every value had
typeof() = text.ORDER BY ageput041before36, andWHERE age > 50returned a 9-year-old, because'9' > '50'as text. With anINTEGERcolumn the same file stored41,36and85as real integers. - Empty fields become empty strings, not NULL, even in an
INTEGERcolumn:quote(age)returned''withtypeof()text.UPDATE โฆ SET age = NULLIF(age, '')fixes it. - Forgetting
--skip 1doesn't fail. Importing into an existing table stored the header as a row, with'id'sitting in the integeridcolumn. - Rows with the wrong column count are imported anyway, with only a warning:
expected 2 columns but found 3 - extras ignoredandโฆ found 1 - filling the rest with NULL. The shell still exited 0, so a script won't notice. - In code, the transaction is what matters, not the method. 5,000 single inserts in autocommit mode took 4.76 s (about 48 s projected for 50,000). 50,000 inserts inside one transaction took 0.07 s,
executemany0.08 s, and the CLI's.import0.07 s.
Import with Python
Python's csv module plus executemany in one transaction is as fast as the shell, and lets you clean values on the way in:
import csv, sqlite3
conn = sqlite3.connect("shop.db")
conn.execute("""CREATE TABLE IF NOT EXISTS people (
id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER, signup TEXT, note TEXT)""")
with open("people.csv", newline="", encoding="utf-8") as f, conn: # "with conn" commits once
rows = csv.reader(f)
next(rows) # skip header
conn.executemany(
"INSERT INTO people VALUES (?, ?, ?, ?, ?)",
([r[0], r[1], r[2] or None, r[3], r[4] or None] for r in rows),
)
newline="" is required for fields that contain line breaks, and or None turns empty strings into real NULLs.
Fix types after an all-TEXT import
If you already imported into an auto-created table, copy it into a typed one rather than altering columns, which SQLite can't do in place:
CREATE TABLE people_typed (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, signup TEXT, note TEXT);
INSERT INTO people_typed
SELECT CAST(id AS INTEGER), name, CAST(NULLIF(age, '') AS INTEGER), signup, NULLIF(note, '')
FROM people;
DROP TABLE people;
ALTER TABLE people_typed RENAME TO people;
Store dates as ISO text (2026-03-09) or Unix seconds. SQLite has no date type, and ISO strings sort and compare correctly as text.
Frequently Asked Questions
How do I import a CSV with a header into SQLite?
If the table does not exist, .import --csv file.csv table uses the header as column names. If it already exists, add --skip 1 so the header is not loaded as a row.
Why are all my imported columns TEXT?
When .import creates the table, it makes every column TEXT. Create the table with the types you want first, then import with --skip 1.
How do I import empty CSV values as NULL?
SQLite imports them as empty strings. Run UPDATE table SET col = NULLIF(col, '') afterwards, or convert empty values to None when inserting from Python.
Why is my Python CSV import into SQLite so slow?
Each insert outside a transaction is its own commit and waits for the disk. Wrap the whole import in one transaction (with conn: and executemany); in our test that took 50,000 rows from about 48 s to 0.08 s.