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:
.header on
.mode csv
.once /Users/quackit/sqlite/dumps/artists.csv
SELECT * FROM Artists;
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 ( .mode csv).
- 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 SELECT statement on the table.).
After running that code, a CSV file is created with the following contents:
ArtistId,ArtistName,Bio1,"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",
Join Queries
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.
.header on
.mode csv
.once /Users/quackit/sqlite/dumps/catalog.csv
SELECT AlbumId, AlbumName, ArtistName
FROM Artists AS a
INNER JOIN Albums AS b
ON a.ArtistId = b.ArtistId
ORDER BY ArtistName;
This results in a CSV file with the following contents:
AlbumId,AlbumName,ArtistName1,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",Primus8,"Pork Soda",Primus9,"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.
Place the .system code on the last line (after the SELECT statement). Like this:
.header on
.mode csv
.once /Users/quackit/sqlite/dumps/catalog.csv
SELECT AlbumId, AlbumName, ArtistName
FROM Artists AS a
INNER JOIN Albums AS b
ON a.ArtistId = b.ArtistId
ORDER BY ArtistName;
.system open /Users/quackit/sqlite/dumps/catalog.csv
The exact code that you use will depend on your operating system.
- On Windows, use .system followed by the file name.
- On Mac, use .system open followed by the file name.
- On Linux and Unix systems, use .system followed 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 .dump command.
