The SQLite command line interface has a handy little feature where you can open your SQL query results in an Excel file.

When you run a query, instead of the query being output to your console as it normally would, it instead opens as an Excel spreadsheet (or LibreOffice, or whatever program your system uses to open CSV files).

You have two options when doing this:

• Use a temporary file
• Use a permanent file (i.e. save it for later)

This article covers both options.

Temporary File

When using the temporary file option, SQLite does this by writing a temporary file, then deleting that file once it has invoked the handler for CSV files (eg, Excel, LibreOffice, etc).

To do this, either use the .excel or .once -x commands (.excel is an alias for .once -x).

Here’s an example to demonstrate using the temporary file method using the .excel command.

.excel
SELECT * FROM Products;

My system currently uses LibreOffice to open CSV files, so when I ran that code, it automatically opened the file in LibreOffice. Given it was opening it as a spreadsheet, I got the usual “Import” prompt where I can select the separators to use, etc.

These are the default settings and they look fine. So once I clicked OK, I got this:

Add Column Headers

You can use .headers on to add the column names.

Here’s another example where I add the column headers. In this case, I use .once -x instead of .excel (as mentioned, .excel is an alias for .once -x).

.headers on
.once -x
SELECT * FROM Products;

Result:

And once I click OK, I get this:

So it’s the same thing, except this time I specified column headers.

Permanent File

When using the permanent file option, you first save the file to a specified location, then you use the .system command to open that file.

You can use .output or .once to save the results to a file, then use the .system command to open your text file.

Here’s how to do this on a Mac:

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

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

The .system command opens the file using the default application for opening CSV files.

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

.system c:/data/query_results.csv

On Linux/Unix:

.system xdg-open query_results.csv