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:
- Turn on the header of the result queryset using the
- Set the output mode to CSV.
- Send the output to a CSV file.
- 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
bookstable and export it to the
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.sqlthat 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):