Import a CSV file into SQLite

sqlite3 CLI

Using the sqlite3 CLI allows you to import CSV files into a new or existing database file. The following multi-line command will import the CSV file, disruptions.csv into the disruptions table. All columns will be of type TEXT if created by the import command.

sqlite3 data.db <<EOS
.mode csv
.import disruptions.csv disruptions
EOS

• The command infers the input file type by the output mode (.mode csv), otherwise use --csv in the import command.

• For new tables created by the import the first row is taken as the column names, otherwise it’s treated as a data row. To skip reading the first row on existing tables use --skip 1 in the import command.

• Use the .separator command to specify a separator other than a comma. For example, if using a #:

sqlite3 data.db <<EOS
.mode csv
.separator #
.import disruptions.csv disruptions
EOS

Credit to Simon Willison’s TIL for using <<EOS to be able to copy and paste the command in one go.

DB Browser for SQLite

The DB Browser for SQLite also provides the ability to import from CSV. In a new project go to File > Import > Table from CSV file.... Select the CSV file and adjust the properties based on your input. After the import is complete write changes to commit the table to the database file.