Importing CSV Files Into SQLite From the Command Line

SQLite has no COPY and no LOAD DATA. It has a shell command, .import, and the command is better than its reputation: it parses RFC 4180 quoting, reads 2,000,000 rows in about two seconds and needs no server or configuration. What it lacks is any sense of failure. A ragged record, a stray quote and a type mismatch are printed as notes on standard error, the load carries on, and the process exits with status 0. This guide covers the whole import path: the options that decide whether the header is data, the types that .import does not give you, the errors it reports and the ones it does not, how to query a CSV without importing it, how to export, and a script that turns the notes into failures.

The shortest import that works

A four row file with a header, a quoted comma and two empty fields. Inside the shell, switch to CSV mode, import, and look at the table:

id,name,email,signup_date,balance
1,Ana Silva,[email protected],2025-01-15,120.50
2,"Smith, Jane",[email protected],2025-02-03,0
3,Bo Chen,,2025-02-20,
4,Cy Dubois,[email protected],2025-03-01,15.75

sqlite3 shop.db <<'SQL'
.mode csv
.import customers.csv customers
.schema customers
.mode box
SELECT * FROM customers;
SQL

CREATE TABLE "customers"(
"id", "name", "email", "signup_date",
 "balance");
╭────┬─────────────┬───────────────────┬─────────────┬─────────╮
│ id │    name     │       email       │ signup_date │ balance │
╞════╪═════════════╪═══════════════════╪═════════════╪═════════╡
│ 1  │ Ana Silva   │ [email protected]  │ 2025-01-15  │ 120.50  │
│ 2  │ Smith, Jane │ [email protected] │ 2025-02-03  │ 0       │
│ 3  │ Bo Chen     │                   │ 2025-02-20  │         │
│ 4  │ Cy Dubois   │ [email protected]   │ 2025-03-01  │ 15.75   │
╰────┴─────────────┴───────────────────┴─────────────┴─────────╯

Everything arrived, including Smith, Jane as one value. The part to look at is the .schema line: CREATE TABLE "customers"("id", "name", "email", "signup_date", "balance"). The table was created from the header, every column has no declared type, and that matters for sorting and comparing, as the types section below and the article on numbers stored as text explain. The same import works as a single command line, sqlite3 shop.db ".import --csv customers.csv customers", which is the form I use in scripts. The CLI documentation lists every option, and using the .import command walks through them with output.

Forgetting the mode

.import reads input with the delimiters of the current output mode, and the default mode is list, which separates fields with |. If you skip .mode csv and --csv, nothing complains, and the result is a one column table named after the entire header line:

sqlite3 shop.db ".import customers.csv customers" ".schema customers" "SELECT count(*) FROM customers"

CREATE TABLE "customers"(
"id,name,email,signup_date,balance");
4

The schema shows a single column called id,name,email,signup_date,balance, and the count is 4, one less than the five lines of the file because the first line became the column name. This is the most common first mistake. When a table has one wide column whose name contains commas, the mode was wrong. --csv on the .import line is better than .mode csv before it, because it applies to that command only and does not change how the rest of the session prints query results.

How .import parses, and what the header becomes

The CLI manual says that in any mode other than ASCII, .import reads records and fields according to RFC 4180, with one exception: the field and record separators are the ones set for the current mode. That is why --csv works, since it sets the comma and the quote rules for that command, and why a pipe or a tab needs .separator first. The quoting is the standard kind, doubled quotes and newlines inside quotes, and it passed the same tests as the other readers in the CSV edge case cheat sheet.

For a delimiter that never occurs in text there is a mode of its own. --ascii uses the ASCII unit separator, byte 1F, between fields and the record separator, 1E, between rows, with no quoting at all. I wrote a file that way containing a comma and double quotes in a value, and it came through untouched:

rm -f a.db; sqlite3 a.db ".import --ascii ascii.dat t" ".mode box" "SELECT * FROM t"

╭────┬──────────────┬─────╮
│ id │     name     │ qty │
╞════╪══════════════╪═════╡
│ 1  │ Ana          │ 3   │
│ 2  │ Bo, "quoted" │ 5   │
╰────┴──────────────┴─────╯

The second row's name is Bo, "quoted", exactly as written, because nothing in the format needs escaping. It is a good choice for data that moves between two programs you control, as the delimiter article argues, and a bad choice for anything a person has to open.

When the table does not exist, SQLite takes the column names from the first row and makes them safe to use as identifiers only in the loosest sense: it quotes them. A header of Order ID,Customer Name,2025 total,"a,b", produced a table with columns named exactly that, spaces and comma included, and an empty last name became ?:

printf 'Order ID,Customer Name,2025 total,"a,b",\n1,Ana,10,x,y\n' > odd-names.csv
sqlite3 shop.db ".import --csv odd-names.csv t" ".schema t"

CREATE TABLE "t"(
"Order ID", "Customer Name", "2025 total", "a,b",
 "?");

A duplicate name is renamed with a notice, so Region,2025 Target,Region became Region_1 and Region_3, as the notice in the folder article shows. Columns like those are legal and miserable to query, and since an untyped table is already a compromise, I rename the columns in the file's header or create the table myself.

The header is data when the table exists

The rule for the first line is in the manual: when the target table does not exist, SQLite creates it and uses the first row as column names. When it does exist, every row of the input, including the first, is data. So an import into a table you created yourself loads the header as a row. I created customers (id INTEGER, name TEXT, ...) and imported without --skip:

sqlite3 shop.db "CREATE TABLE customers (id INTEGER, name TEXT, email TEXT, signup_date TEXT, balance REAL)" ".import --csv customers.csv customers" "SELECT id, name, balance FROM customers ORDER BY rowid"

id|name|balance
1|Ana Silva|120.5
2|Smith, Jane|0.0
3|Bo Chen|
4|Cy Dubois|15.75

The first output row is id|name|balance: the header, stored in an INTEGER column as the text id, because SQLite accepts text in any column unless the table is strict. There are 5 rows in a table that should have 4, and nothing said so. --skip 1 is the fix, and with it the count comes out right:

sqlite3 shop.db "CREATE TABLE customers (id INTEGER, name TEXT, email TEXT, signup_date TEXT, balance REAL)" ".import --csv --skip 1 customers.csv customers" "SELECT count(*) AS rows, min(id), max(id) FROM customers"

4|1|4

4|1|4 is the count, the minimum id and the maximum id. The two cases need opposite options, which is a reason to create tables yourself and always pass --skip 1, and to import into a temporary table when you want SQLite to name the columns. The article on importing into an existing table covers duplicates, missing columns and column order.

Types are your job

A table created by .import has columns with no declared type, and every value is stored as text. A table you create can declare types, and then SQLite's type affinity rules decide what happens to each value. The rules are in the datatype page and they are looser than most databases: an INTEGER column converts text that looks like an integer, and leaves anything else as text.

I loaded a four row products file, with prices, stock counts and ZIP codes, into a table whose last column I deliberately declared INTEGER:

sqlite3 shop.db <<'SQL'
CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price REAL, stock INTEGER, zip INTEGER);
.import --csv --skip 1 products.csv products
SELECT sku, price, typeof(price), stock, typeof(stock), zip, typeof(zip) FROM products;
SQL

A-100|9.9|real|10|integer|501|integer
A-101|1.25|real|200|integer|2134|integer
B-007|12.0|real|0|integer|90210|integer
B-010|24.5|real|15|integer|10001|integer

The prices became real, the stock integer, and the ZIP codes 00501, 02134 became the integers 501 and 2134. The affinity conversion dropped the leading zeros, and no warning was given. Declaring the ZIP column TEXT keeps them. The stricter option is STRICT, available since SQLite 3.37, which makes a column accept only its declared type:

sqlite3 shop.db <<'SQL' 2>&1
CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price REAL, stock INTEGER, zip TEXT) STRICT;
.import --csv --skip 1 products-bad.csv products
SELECT count(*) AS rows_loaded FROM products;
SQL

products-bad.csv:2: INSERT failed: cannot store TEXT value in REAL column products.price
1

With a STRICT table and a file whose first record has the price not a price, the shell printed products-bad.csv:2: INSERT failed: cannot store TEXT value in REAL column products.price, skipped that record, and loaded the second. The count of 1 is the only sign afterwards. In a non-strict table, the same value was stored quietly as the text not a price in the REAL column. Setting column types when SQLite imports a CSV has the whole comparison, and the STRICT tables page lists the allowed types.

When the import fails halfway

Errors fall into two groups with different consequences. Problems with the shape of the file, a short or long record, a stray quote, are warnings: the shell prints a line, loads what it can and exits with status 0. Problems storing a row, a constraint violation or a type mismatch in a strict table, are errors: the shell prints INSERT failed, skips that row, loads the rest and exits with status 1. I loaded a five line file with a repeated id on its third data row into a table with a primary key:

rm -f p.db; sqlite3 p.db "CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT, qty INTEGER)"
sqlite3 p.db ".import --csv --skip 1 dup-middle.csv t"; echo "exit status of the import: $?"
sqlite3 p.db "SELECT id, name FROM t ORDER BY id"

dup-middle.csv:4: INSERT failed: UNIQUE constraint failed: t.id
exit status of the import: 1
1|Ana
2|Bo
4|Dee
5|Eli

Rows 1, 2, 4 and 5 are in the table and the duplicate was skipped, with exit status 1. There is no all or nothing behavior by default. And when sqlite3 is given commands as arguments, a failing .import stops the later arguments from running: a SELECT I put after it never executed. In a script, that is what you want, since the failure is not ignored, and in an interactive session or a heredoc the next lines do run.

Wrapping the import in a transaction gives the atomic behavior that PostgreSQL has by default:

rm -f p.db; sqlite3 p.db "CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT, qty INTEGER)"
sqlite3 p.db <<'SQL'
BEGIN;
.import --csv --skip 1 dup-middle.csv t
SELECT 'after the failed row, same session: ' || count(*) FROM t;
ROLLBACK;
SELECT 'after ROLLBACK: ' || count(*) FROM t;
SQL
echo "exit status: $?"

dup-middle.csv:4: INSERT failed: UNIQUE constraint failed: t.id
after the failed row, same session: 4
after ROLLBACK: 0
exit status: 1

Inside BEGIN, the failed row still failed and the other four rows were counted, and ROLLBACK brought the table back to zero rows. The pattern is to BEGIN, import, check the log and the count, and then COMMIT or ROLLBACK. My checked script below does it in a simpler way, with a fresh table that it drops on failure, which suits a table that is replaced in full each time.

What the import tells you, and what it does not

The shell reports problems on standard error, one line per problem, with the file name and line number. I made a file with five different problems and imported it:

rm -f e.db; sqlite3 e.db ".import --csv bad-shapes.csv t" "SELECT count(*) AS rows_imported FROM t" "SELECT * FROM t" 2>&1

bad-shapes.csv:3: expected 3 columns but found 2 - filling the rest with NULL
bad-shapes.csv:4: expected 3 columns but found 4 - extras ignored
bad-shapes.csv:5: unterminated "-quoted field
bad-shapes.csv:5: expected 3 columns but found 2 - filling the rest with NULL
4
1|Ana|3
2|Bo|
3|Cy|5
4|Dee,6
5,Eli,7
|

A record with too few fields was padded with NULL, a record with too many was truncated with extras ignored, and an unterminated quote made record 4 swallow the line after it. All of that was imported: the count is 4 and the last row holds Dee,6 and 5,Eli,7 as one field. The exit status, in a separate run, was 0:

exit status with the warning on stderr: 0

and .bail on, the setting that stops a script at the first error, did not stop the import on any of them. Bailing applies to SQL errors, and these are warnings. So a nightly job that checks the exit status of sqlite3 believes every one of these loads succeeded. The article on the import errors gives the cause and the repair for each message, and the script below turns them into failures.

Existing data, duplicates and column order

A table with a primary key rejects a duplicate id from the file and prints INSERT failed: UNIQUE constraint failed: customers.id, exits with status 1 and still loads the other records. A file with fewer columns than the table pads the rest with NULL, with the filling the rest with NULL note, and one with more drops the extras. A file whose columns are in a different order loads into the wrong columns, and in my test it failed with datatype mismatch only because the first column was an INTEGER PRIMARY KEY, which cannot hold a name; a plain text column in that position would have taken the names without a word. To control all of that, import into a temporary table and move the rows with SQL, where INSERT ... SELECT takes an explicit column list and ON CONFLICT says what to do with duplicates:

rm -f shop.db; sqlite3 shop.db "CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, signup_date TEXT, balance REAL)" ".import --csv --skip 1 customers.csv customers"
sqlite3 shop.db <<'SQL'
.import --csv --schema temp customers-extra.csv incoming
INSERT INTO customers SELECT * FROM temp.incoming WHERE true
  ON CONFLICT(id) DO UPDATE SET name = excluded.name, balance = excluded.balance;
SELECT id, name, balance FROM customers ORDER BY id;
SQL

1|Ana Silva|120.5
2|Smith, Jane|0.0
3|Bo Chen|
4|Cy Dubois (updated)|99.0
5|Eli Fox|6.0

Ana's row was not duplicated, Cy Dubois was updated by the second file and Eli Fox was added. This is the pattern for repeated loads, explained in importing a CSV into an existing table, and it relies on the UPSERT syntax.

Querying a CSV without importing it

If you only need to look at the file, SQLite can read it in place through the CSV virtual table, a loadable extension in the SQLite source tree. It is not part of the standard shell build, so it has to be compiled once and loaded:

sqlite3 :memory: <<'SQL'
.load /usr/local/lib/csv
CREATE VIRTUAL TABLE temp.cust USING csv(filename='customers.csv', header=YES);
SELECT * FROM temp.cust WHERE balance > 10;
SQL

1|Ana Silva|[email protected]|2025-01-15|120.50
4|Cy Dubois|[email protected]|2025-03-01|15.75

The query returned the two customers with a balance above 10. Without the extension, the same statement says no such module: csv. The CSV virtual table page documents the header, columns and schema parameters, and querying a CSV file in SQLite without importing it compares it with importing and with DuckDB, which does the same in one line with no build.

Many files, one database

A folder of monthly CSVs is a common input. For files that share a layout, loop over them and import each into a temporary table, then insert into one table with a column that records the source file. For files that do not, one table per file, with the column types inferred. Both are worth a script, and both have an article: loading many CSV files into one SQLite database in Bash and turning a folder of CSV files into a SQLite database. Concatenating the files with cat is the shortcut to avoid. A file without a final newline fuses its last line with the next file's first, and I saw 1,23,4 where 1,2 and 3,4 should have been.

Exporting back out

The shell writes CSV as well. In SQLite 3.54, .mode csv quotes a value that contains a space, comma, quote or newline, writes an empty string as "" and a NULL as an empty field, and ends lines with \r\n. The separate article on exporting a query result to CSV measures each of those and the options for changing them, including --null and .once --bom for a file that Excel will open as UTF-8.

How fast is it

I timed the import of the 2,000,000 row file four ways, three runs each with a fresh database:

python bench_sqlite.py

sqlite3 .import, 2,000,000 rows           median s     rows/s
untyped, defaults                             2.12    941,866
typed table, skip header                      2.56    780,858
typed, journal off, synchronous off           2.30    869,660
typed, WAL mode                               2.56    781,680

database size: untyped 146 MB, typed 133 MB

SELECT country, sum(amount) ... GROUP BY country over 2,000,000 rows
  untyped columns (values stored as text): 0.65 s -> FR|22969870.61|286777
  typed columns: 0.60 s -> FR|22969870.61|286777

Untyped .import took 2.12 s, about 940,000 rows per second. Into a typed table with the header skipped it took 2.56 s, and with the journal and synchronous settings turned off 2.30 s. WAL mode took 2.56 s. These are single runs of one transaction each, so the pragmas have little to save, and I would not use journal_mode = OFF for anything that must survive a crash. The typed database was smaller, 133 MB against 146 MB, because integers and reals take less space than their text. The aggregate at the bottom, the sum of amount grouped by country, took 0.65 s on the untyped copy and 0.60 s on the typed one. SQLite converted the text to numbers on the fly, so the answer was the same and the cost small, though comparisons and sorting would not be, as the numbers as text article shows.

Memory, encoding and the bytes

.import streams the file. Importing the 130 MB file peaked at about 5 MB of resident memory in my run, according to /usr/bin/time -v, and finished in 2.66 s wall time, producing a 146 MB database file:

rm -f /tmp/m.db; /usr/bin/time -v sqlite3 /tmp/m.db ".import --csv orders-2m.csv orders" 2>&1 | grep -E "Maximum resident|Elapsed" | sed 's/^\s*//'
ls -l /tmp/m.db | awk '{printf "database file: %.0f MB\n", $5/1e6}'
ls -l orders-2m.csv | awk '{printf "CSV file:      %.0f MB\n", $5/1e6}'

Elapsed (wall clock) time (h:mm:ss or m:ss): 0:02.04
Maximum resident set size (kbytes): 5024
database file: 146 MB
CSV file:      130 MB

So the size of the file is not a limit; the disk and the time are. A 50 GB CSV imports in constant memory and a few minutes, if the disk has room for a database file somewhat larger than the CSV.

On encoding, the shell is as lenient as a program can be. It reads bytes and stores them as the text of the database, which is UTF-8, without checking. A UTF-8 file with a byte order mark loaded with the BOM stripped, in the conformance runs, while a Windows 1252 or Latin 1 file loaded without any error and with wrong text, since SQLite does not validate or convert; the matrix in the garbled text guide shows SQLite as the only one of five readers that accepted every legacy file. Check the bytes first, with the preflight from that guide, because here there is no second line of defense.

The shell against a driver

The command line is not the only way in. Python's sqlite3 module reads the same file with the csv module and inserts in one transaction, which is what an application would do. I timed it on the 2,000,000 row file, three runs each:

python py_sqlite_import.py

Python sqlite3, 2,000,000 rows, one transaction         median s
executemany in chunks of 50,000                             5.67     352,563 rows/s
execute once per row                                        5.43     368,377 rows/s

executemany in chunks of 50,000 rows took 5.67 s and a plain execute per row took 5.43 s, about 350,000 to 370,000 rows per second, against 2.1 to 2.6 s for the shell's .import. The difference of a factor of 2.5 is the Python side: parsing every row into a list of strings, binding each of the eight parameters and calling into the library 2,000,000 times, while .import does it all inside one C loop. Chunking made no difference, since SQLite's execute with a prepared statement is already cheap and the transaction is the thing that matters. I did not time a commit per row, because every commit waits for the disk and it is the standard way to make an import crawl; the timing here is the reason to open one transaction and keep it for the whole file, and to run ANALYZE afterwards.

The practical rule is that the shell is the faster loader and the driver is the more controllable one. From Python you can validate each row, convert types, route rejects to a file and stop at the first problem, which the shell cannot do. For a plain file load, .import wins; for one where you must decide row by row what to do, write the loop, and expect it to take about two and a half times as long.

Choosing pragmas for a bulk load

The shell runs .import as a single transaction, so the usual advice about batching commits is already followed for you. The pragmas that remain are about durability. PRAGMA synchronous = OFF stops SQLite waiting for the disk after each write, and PRAGMA journal_mode = OFF removes the rollback journal. In my timing they took 2.56 s down to 2.30 s, roughly 10%, and both give up crash safety: a power cut in the middle of the load can leave a corrupt file. If you are rebuilding a database from a CSV that you can import again, that is a fair trade and you delete the file on failure. If the database holds anything you cannot recreate, leave them alone. WAL mode, which the write ahead logging page describes, is a good setting for a database that is read while it is written, and it was a little slower to load into, at 2.56 s, because every page is written to the log first. Switch it on after the load.

The pragma reference lists the rest, including cache_size and temp_store, which matter more when you add indexes after the import. As with the other engines, build secondary indexes after loading the data, not before, and run ANALYZE once at the end so the planner has statistics.

A load that fails when it should

This is the script I use. It counts the records with a strict reader before touching the database, imports into a new STRICT table inside a transaction, collects everything the shell printed, and compares the table's count with the file's. If anything was printed or the counts differ, it drops the new table and exits with status 1. Otherwise it swaps the new table in by renaming.

#!/usr/bin/env bash
# Import a CSV into a typed SQLite table, and fail on anything the shell would only print as a warning.
set -euo pipefail
csv=${1:?usage: sqlite_load_checked.sh file.csv database.db}
db=${2:?}

expected=$(qsv count "$csv")          # strict: stops on a ragged record
log=$(mktemp)

sqlite3 "$db" > "$log" 2>&1 <<SQL || true
BEGIN;
DROP TABLE IF EXISTS products_new;
CREATE TABLE products_new (sku TEXT PRIMARY KEY, name TEXT, price REAL, stock INTEGER, zip TEXT) STRICT;
.import --csv --skip 1 "$csv" products_new
COMMIT;
SQL

loaded=$(sqlite3 "$db" "SELECT count(*) FROM products_new" 2>/dev/null || echo 0)
echo "file: $expected records, loaded: $loaded"
if [ -s "$log" ] || [ "$loaded" -ne "$expected" ]; then
  echo "not swapping. the shell said:" >&2; cat "$log" >&2
  sqlite3 "$db" "DROP TABLE IF EXISTS products_new"
  exit 1
fi
sqlite3 "$db" "BEGIN; DROP TABLE IF EXISTS products; ALTER TABLE products_new RENAME TO products; COMMIT;"
echo "swapped in"

On the four row products file it swaps:

./sqlite_load_checked.sh products-good.csv checked.db && sqlite3 checked.db "SELECT sku, price, zip FROM products ORDER BY sku"

file: 4 records, loaded: 4
swapped in
A-100|9.9|00501
A-101|1.25|02134
B-007|12.0|90210
B-010|24.5|10001

On a file where one price is not a price, it refuses and leaves the existing table alone:

file: 2 records, loaded: 1
not swapping. the shell said:
products-bad.csv:2: INSERT failed: cannot store TEXT value in REAL column products_new.price
exit status 1
products still has 4 rows

The note from the shell, INSERT failed: cannot store TEXT value in REAL column, is printed with the failure, and the table still has its four rows. On a file with a short record, the strict count stops it earlier, with the position of the bad record:

csv error: CSV error: record 1 (line: 2, byte: 25): found record with 4 fields, but the previous record has 5 fields
exit status 1

The strict count is qsv count, which fails on a ragged record, and it is the one check that does not rely on SQLite printing anything. For files that may contain quoted newlines, the same count is the right comparison, since wc -l is not.

Where each article fits

For the command itself, see the .import command and setting column types. For loading into tables that exist, see importing into an existing table. For looking without loading, see querying a CSV without importing it. For the way out, see exporting a query result to CSV. For the error messages, see expected columns but found. For several files, see loading many files into one database and a folder of CSV files. And for the surprise with numbers, see why SQLite stores CSV numbers as text. The same task on the other engines is in the PostgreSQL guide and the MySQL guide.

When .import is not enough

.import is for a file you can see, going into a table you can inspect. Four cases need another tool.

If the import must run inside an application, use the language's driver and executemany in one transaction, which loaded the same data at a comparable speed in the earlier Python tests. If the file is not CSV, a spreadsheet or JSON, convert it first. If you need to transform values on the way in, import as text into a temporary table and do the work in SQL, which SQLite does well. And if you want to query a large CSV repeatedly with joins, window functions and types inferred correctly, DuckDB reads a CSV directly and is a better fit than a virtual table, as the DuckDB guide shows.

SQLite is also the engine to be careful with when the data is important and the shell is unattended. The shell is built for people. Everything in this guide that looks like extra work, the strict table, the count and the stderr capture, is the work that a server database does for you by failing, and which here you have to do yourself.