SQLite - Export Data to a CSV File
You can export data from an SQLite database to a CSV file. You can export a whole table or the results of a query.
To export data to a CSV file, use the
.once command, followed by the file path/name that you'd like the file to be written to.
Table to CSV File
This example exports the contents of the Artists table to a CSV file:
Explanation of the code:
- First, we enable headers. This will result in the column names being used as headers in the CSV file. This is optional. If you disable headers, the CSV file simply won't contain any.
- Then we enable csv mode (
- The next line (beginning with
.once) causes the output to be written to the CSV file.
- The last thing we write is the actual query (in this case, a
SELECTstatement on the table.).
After running that code, a CSV file is created with the following contents:
ArtistId,ArtistName,Bio 1,"Joe Satriani", 2,"Steve Vai", 3,"The Tea Party", 4,Noiseworks, 5,"Wayne Jury", 6,"Mr Percival", 7,"Iron Maiden", 8,Atmasphere,"Australian jazz band centred around polyrhythms." 9,"Ian Moss", 10,Magnum, 13,Primus, 14,"Pat Metheny", 15,"Frank Gambale", 16,"Mothers of Invention",
You're not limited to just outputting a single table. You can output any query. Here's a query that selects data from two tables and outputs the result into one CSV file.
This results in a CSV file with the following contents:
AlbumId,AlbumName,ArtistName 1,Killers,"Iron Maiden" 2,Powerslave,"Iron Maiden" 12,"Somewhere in Time","Iron Maiden" 3,"Surfing with the Alien","Joe Satriani" 10,"Flying in a Blue Dream","Joe Satriani" 11,"Black Swans and Wormhole Wizards","Joe Satriani" 6,"Out of the Loop","Mr Percival" 7,"Suck on This",Primus 8,"Pork Soda",Primus 9,"Sailing the Seas of Cheese",Primus
Open the CSV file Automatically
You can use the
.system command to automatically open the file in your computer's spreadsheet program, such as Excel, OpenOffice, LibreOffice, etc.
.system code on the last line (after the
SELECT statement). Like this:
The exact code that you use will depend on your operating system.
- On Windows, use
.systemfollowed by the file name.
- On Mac, use
.system openfollowed by the file name.
- On Linux and Unix systems, use
.systemfollowed by the name of the program to open the file, followed by the file name. For example,
.system libreoffice /dumps/file.csv
Dump a Table or Database
You can also dump a table or even the whole database into an ASCII text file using the