You can use the .output or .once dot commands to save your query results to a text file when using the SQLite CLI.

The .once Command

If you only want to save a single query to a file, use the .once command.

The .once command specifies that the next query will be sent to the specified file. Any further queries will revert to the standard output (the console).

.headers on
.mode column
.once query_results.txt
SELECT * FROM Products;

The above code outputs the results to a text file called query_results.txt in the current directory. Use the full path if you need to specify another directory.

In this case I also enabled column headers and set the mode to “column”.

Here’s the contents of the text file:

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

The fact that I used .once means that any further queries will be output to the console.

If you need all further queries to be saved to the text file, use .output.

The .output Command

The .output command specifies that all further output will be sent to the specified file.

Any subsequent query results will be appended to the existing contents.

Take the following code for example:

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

In this case I ran two queries.

Here’s what the resulting text file contains:

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

So it has included the results from both queries. We can see that the second query was appended.

Note that I didn’t need to specify .headers on or .mode column in this example because I’d already specified that in my previous example. Those settings remain in the current session until they’re explicitly changed.

Reset Output to the Screen

You can use the following command to revert output to the standard output (the console):

.output stdout

Alternatively, you can simply omit any argument:

.output

Automatically Open the Text File

You can use the .system command to open your text 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.txt

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 text files.

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

.system c:/data/query_results2.txt

On Linux/Unix:

.system xdg-open query_results2.txt