Export SQLite Database to a CSV file using sqlite3 command line tool (Ubuntu 16.04)

This page provides instructions on how to how to export SQLite database to a CSV file.

There are several ways to dump data from an SQLite database to a CSV file. Here we use sqlite3 command line tool on Ubuntu 16.04.

By using the sqlite3 tool, we can interact with the SQLite database with SQL statements and dot-commands.

(This instruction assumes that you have sqlite3 installed on your Ubuntu, if not, check out my page at here to install it before preceding.)

To export selected data from a SQLite database to a CSV file, see the steps below:

  1. Turn on the header of the result queryset using the .header on command.
  2. Set the output mode to CSV.
  3. Send the output to a CSV file.
  4. Issue the query to select data from the table to which we want to export.

Note: before doing the following, remember to cd to the folder where your database is located, and issue the following command

 $ sqlite3 your_db_name
  • The following commands select data from the books table and export it to the selected_data.csv file.
sqlite> .headers on
sqlite> .mode csv
sqlite> .output selected_data.csv
sqlite> SELECT book_id,
 ...> title,
 ...> authors,
 ...> year
 ...> FROM books;
sqlite> .quit
  • Besides using the dot-commands, you can use the options of the sqlite3 tool to export data from the SQLite database to a CSV file.

For example, the following command exports all the records from the books table to a CSV file named books.csv (before issuing the following commands, first cd into the folder where you database file  is located).

$ sqlite3 -header -csv your_database_name "select * from books;" > books.csv
$ sqlite3 -header -csv your_database_name "select * from books where year = 2017;" > books_2017.csv
$ sqlite3 -header -csv your_database_name "select * from books where year = 2017 and authors = 'J. K. Rowling';" > JKRowling_books_2017.csv
  • If you have a file named your_query.sql that contains the SQL statements to query the database, you can execute the statements in the file and export data to a CSV file (before issuing the following command, first cd into the folder where you database file  and your sql query file are located). 
$ sqlite3 -header -csv your_database_name < your_query.sql > selected_data.csv

Note: your_query.sql should look like the example shown in the picture below (no double quotes around the SQL statements):

 

References:

sqlite3 – A command line interface for SQLite version 3 (pdf)