Using the sqlite3 dot import Command With a CSV File

The .import command does one job and has a handful of switches, and every switch changes whether the first line is a header, which columns exist, or where the table lives. Most of the time you can type .import --csv file.csv table and it works. The cases that do not are the ones where the table exists, where the header has spaces in it, or where the data needs to go somewhere temporary. This article takes each switch in turn with the output it produces on a four row file. The full path from file to checked table is in the guide to importing CSV files into SQLite.

What you need

The sqlite3 shell and a file. The shell has been able to import for a long time, but --csv, --skip and --schema are newer than the version in many Linux distributions, so check yours with sqlite3 --version. The examples use this file:

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

The two ways to say it is CSV

.import splits input using the separators of the current output mode, and CSV is not the default. The quoting it applies in CSV mode is the RFC 4180 kind, the same rules that the CSV format guide describes. You can set the mode for the whole session or the option for one command. The first form is what most tutorials show:

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   │
╰────┴─────────────┴───────────────────┴─────────────┴─────────╯

.mode csv switched the shell to comma separated, quoted output, so the later .mode box was needed to read the result. The other form is .import --csv, which affects that command only:

sqlite3 shop.db ".import --csv customers.csv customers" ".headers on" ".mode column" "SELECT id, name, typeof(id), typeof(balance), balance FROM customers"

id     name      typeof(id)  typeof(balance)  balance
--  -----------  ----------  ---------------  -------
1   Ana Silva    text        text             120.50
2   Smith, Jane  text        text             0
3   Bo Chen      text        text
4   Cy Dubois    text        text             15.75

The output, in column mode, shows the four rows and a column that matters: typeof(id) is text. The .import command stores every value as text and gives the columns no type, which the types article deals with. I prefer --csv in scripts because it leaves nothing behind. A .mode csv set earlier in a session changes the output of every later SELECT, which is a nasty way to find out that your query results now have quotes in them.

If you forget both, you get a one column table:

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 is "id,name,email,signup_date,balance", one column named after the whole first line, and the count is 4 because the first line was consumed as the name.

The first line: header, or data

The manual's rule is short. If the table does not exist, the first row is used to create it, and its fields become the column names. If the table exists, all rows are data. So you ask for one of two things:

To let SQLite build the table, import into a name that is not taken. To load into a table you built, pass --skip 1 when the file has a header. Here is what happens if you forget:

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 line of the output is id|name|balance: the header row went into the table as an ordinary record, and the real data follows. With --skip 1:

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

The result 4|1|4 is the row count, the minimum id and the maximum id, which is what a four row file should give. I check min and max of the id column after any import, since a header that was loaded as data shows up there as a text minimum, with the table's integer affinity doing nothing about it.

What the header becomes

When SQLite creates the table, it uses the header as written. A header with spaces, a comma inside quotes and an empty last field turns into this:

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",
 "?");

The columns are named Order ID, Customer Name, 2025 total, a,b and ?. SQLite quotes them, so the table is valid and every query on it has to quote the same names. The empty name became a question mark. If the header has the same name twice, SQLite renames the later one with a notice. My advice is to rename in the file or create the table yourself with names you want to type, since "Order ID" in every query is a tax that you pay forever, for one saved line.

Importing into a temporary table

--schema says which database the table belongs to, and temp is the connection's own scratch database. Used with a new name, it creates a table that disappears when you close the connection:

sqlite3 shop.db ".import --csv --schema temp customers.csv scratch" "SELECT count(*) FROM temp.scratch" ".tables" 2>&1
sqlite3 shop.db ".tables" | wc -w

4
temp.scratch
0

The first connection counted 4 rows in temp.scratch and listed it under .tables. A second connection to the same file found no tables at all, the 0 on the last line. That makes a temporary table the cleanest staging area: import the CSV into it, move the rows to a real table with SQL that can convert and filter, and nothing needs dropping afterwards. The pattern is in the existing table article.

Piped input

A file name that begins with | is not a file. SQLite runs it as a shell command and reads its standard output. This loads a gzipped CSV without unpacking it:

gzip -c customers.csv > /tmp/customers.csv.gz
sqlite3 shop.db ".import --csv '|gunzip -c /tmp/customers.csv.gz' customers" "SELECT count(*) FROM customers"

4

The same trick lets another tool prepare the data: .import --csv '|qsv select id,balance customers.csv' small imported two chosen columns, and the sum of the balance column came out as 136.25 in my test. That is a quick way to import only some columns, because .import itself has no column list. The command runs with the shell's privileges, so never build the string from input you do not control.

Other options worth knowing

.help import in 3.54 lists the rest. --ascii uses the control characters 1F and 1E as separators and needs no quoting, as the hub shows. --colsep and --rowsep set a single character separator, and the help text warns that a multi-character or non-ASCII separator is cut to its first byte, so || quietly becomes |. --esc and --qesc name a backslash style escape character for unquoted and quoted fields, which is how you read the output of programs that escape with \ instead of doubling quotes. A file name that starts with << followed by a label reads the following lines of a script up to the label, so a script can carry its own data. And -v turns on verbose mode.

What .import does not have is a way to say that a column is a date, a way to reject a bad value other than a STRICT table, a column list, or a dry run. For those, import into a temporary table and use SQL, as the hub guide describes.

How to tell it worked

Count the rows and compare with a strict reader on the file, such as qsv count, not wc -l. Look at min and max of the key column. Check .schema once, to see what types exist, and typeof() on a sample of values. And look at standard error: the shell prints one line per problem, and a clean import prints nothing.

What .import does not do

It does not stop on bad rows, and for shape problems it does not change its exit status, which is covered in the errors article. It reads any delimiter you give it but only one at a time, so a file with || between fields needs preprocessing. It does not understand encodings, so a Latin 1 file loads as bytes and shows wrong text, as the encoding guide shows. For a file that needs more than the shell can give, drivers like Python's sqlite3 module read rows with the csv module and insert with executemany, which took 5.7 s instead of 2.1 s for 2,000,000 rows in my measurement and lets you handle every row in code.