When using the SQLite command line shell, you can export your query results to a CSV file by using the .mode dot command in conjunction with the .output or .once commands.

You can also use the .system command to open that file.

Export One Query

If you only want to export a single query to a CSV file, use .once. This exports the results of the next SQL statement to the specified file, then reverts back to the console.

.headers on
.mode csv
.once query_results.csv
SELECT * FROM Products;

In this case, I enable column headers. I then switch to “csv” mode, then use the .once command to specify that only the next query should be exported to a file called query_results.csv.

I then run the query, the results of which are exported to the specified file.

If I open the file, here’s what it contains:

ProductId,ProductName,Price
1,"Widget Holder",139.5
2,"Widget Opener",89.7
3,"Bob's ""Best"" Widget",374.2
4,"Blue Widget",63.0

Any subsequent queries will be output to the screen.

If you don’t want column headers, use .headers off instead of .headers on.

Export All Queries

If you need to run multiple queries, all of which must be saved/appended to the same CSV file, then use .output.

.output query_results2.csv
SELECT * FROM Products;
SELECT * FROM Products LIMIT 2;

In this case I set the output file, then ran two queries.

Here’s what the file contains:

ProductId,ProductName,Price
1,"Widget Holder",139.5
2,"Widget Opener",89.7
3,"Bob's ""Best"" Widget",374.2
4,"Blue Widget",63.0
ProductId,ProductName,Price
1,"Widget Holder",139.5
2,"Widget Opener",89.7

Again, if you don’t want column headers, use .headers off.

If you want column headers only on the first line, then you can enable them, then disable them after you run the first query. Like this:

.headers on
.output query_results2.csv
SELECT * FROM Products;
.headers off
SELECT * FROM Products LIMIT 2;

Result:

ProductId,ProductName,Price
1,"Widget Holder",139.5
2,"Widget Opener",89.7
3,"Bob's ""Best"" Widget",374.2
4,"Blue Widget",63.0
1,"Widget Holder",139.5
2,"Widget Opener",89.7

Automatically Open the CSV File

You can use the .system command to open your CSV file. The exact syntax you use will depend on your system.

Here’s how to open the previous file on a Mac:

.system open query_results2.csv

This assumes you’re in the same directory as the file. Otherwise you’ll need to use the full path.

This opens the file in the system’s default application for opening CSV files. For example, this could be Microsoft Excel, LibreOffice, your system’s default text editor, etc.

On a Windows system, your code might look more like this:

.system c:/data/query_results2.csv

On Linux/Unix:

.system xdg-open query_results2.csv