You can configure the SQLite command line interface to automatically open query results in a text editor.

When you do this, you have two options:

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

This article provides examples of both methods.

Temporary File

When using a temporary file, the temporary file is deleted after it has been opened in the text editor. Use this method if you have no intention of keeping the file.

To do this, use .once -e. You can alternatively use .once '|open -f' if you’re on a Mac.

Here’s an example to demonstrate using the .once -e option.

.once -e
SELECT * FROM Products;

When I ran that on my Mac, the results opened in TextEdit, because that’s currently the default program for opening text files.

On the Mac, I can change the default program by right-clicking on a text file, selecting Get Info, selecting the desired program under Open With, and clicking Change All.

So in my case, TextEdit opened the following file:

Formatting the Text File

The query results in the previous example are formatted in columns with column headers. This is only because I had previously specified those settings.

You can specify a variety of output formats using the .mode dot command. For example, to output the results in CSV format, use .mode csv.

Also, as mentioned, if you’re on a Mac, you can alternatively use .once '|open -f' to do the same thing.

Here’s an example that combines those settings/commands.

.headers on
.mode csv
.once '|open -f'
SELECT * FROM Products;

When the text file opens, it contains the following:

I also specified .headers on again here, merely for demonstration purposes.

Permanent File

When using a permanent file, you save the file in a specified location, then open it using the .system command.

To do this, 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.txt
SELECT * FROM Products;
.system open query_results.txt

This assumes that 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 text files.

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

.system c:/data/query_results.txt

On Linux/Unix:

.system xdg-open query_results.txt