Converting a SQLite database to CSV

From One-Eyed Man Wiki
Jump to navigation Jump to search


Export SQLite Database to a CSV file using sqlite3 tool

SQLite project provides you with a command-line program called sqlite3 or sqlite3.exe on Windows. By using the sqlite3 tool, you can use the SQL statements and dot-commands to interact with the SQLite database.

To export data from the SQLite database to a CSV file, you use these steps:

  1. Turn on the header of the result set using the .header on command.
  2. Set the output mode to CSV to instruct the sqlite3 tool to issue the result in the CSV mode.
  3. Send the output to a CSV file.
  4. Issue the query to select data from the table to which you want to export.

The following commands select data from the customers table and export it to the data.csv file.

>sqlite3 c:/sqlite/chinook.db
sqlite> .headers on
sqlite> .mode csv
sqlite> .output data.csv
sqlite> SELECT customerid,
   ...>        firstname,
   ...>        lastname,
   ...>        company
   ...>   FROM customers;
sqlite> .quit