Importing and Exporting CSV Data in SQLite

Importing CSV Data in SQLite

In SQLite, and using the command line, you can use the .import command to import CSV data into a table. Before you run the .import command, set the mode to csv (this might seem odd, given we've used .mode to control output, but this prevents SQLite from trying to interpret the CSV file as commands).

The .import command takes two arguments: the location of the CSV file, and the name of the table where the data should be inserted.

sqlite> .mode csv
sqlite> .import table_name.csv table_name

When you import from a CSV file, there are two cases to consider:

1. The table doesn't already exist.
2. The table already exists.

For the first case (the table table_name doesn't exist), the first row of the CSV file is interpreted as column names, and the actual data starts on the second row of the CSV file.

For the second case, when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table, and that is not what you intended.

Exporting CSV Data in SQLite

Using the command line to export data, run the following commands:

sqlite> .headers on
sqlite> .mode csv
sqlite> .once output.csv
sqlite> SELECT * FROM newly_added_table;

Note that the .headers on command is used to print column labels as the first row of the output. Also, the command .once FILENAME instructs SQLite to send all query outputs into the named file rather than printing it on screen.