sqlite3 .import: Load CSV Fixtures into SQLite

sqlite3 loads a CSV into a table with .mode csv followed by .import file.csv table, ideal for seeding test data.

For seeding fixtures, .import reads a delimited file straight into a table. The one gotcha is setting the input mode first and deciding whether the file has a header row, which changes how the target table is created or matched.

What it does

.import reads a delimited file and inserts each line as a row. The delimiter follows the current mode (.mode csv for commas, .mode tabs for tabs). If the target table does not exist, .import creates it using the first row as column names; if it exists, the file rows must match its columns. --skip N skips leading rows.

Common usage

# into an existing table (skip the header row)
sqlite3 app.db -cmd ".mode csv" ".import --skip 1 users.csv users"
# create a new table from the CSV header
sqlite3 app.db -cmd ".mode csv" ".import users.csv users"
# scripted form
printf '.mode csv\n.import --skip 1 users.csv users\n' | sqlite3 app.db

Options

In CI

Set .mode csv before .import, or SQLite treats the whole line as a single column and the import silently misaligns. When the CSV has a header, either use --skip 1 against an existing table or let the header define columns for a new table; do not do both. Keep fixtures small and deterministic so tests stay fast and repeatable.

Common errors in CI

"expected 3 columns but found 1" (or the reverse) means the mode does not match the file delimiter; set .mode csv first. "datatype mismatch" or a header row landing as data means you imported the header into an existing table without --skip 1. "no such table" with an existing-table import means a typo in the table name; a new-table import would have created it instead.

Related guides