SQLite: How do I save the result of a query as a CSV file?

Good answers from gdw2 and d5e5. To make it a little simpler here are the recommendations pulled together in a single series of commands:

sqlite> .mode csv
sqlite> .output test.csv
sqlite> select * from tbl1;
sqlite> .output stdout

From here and d5e5's comment:

You'll have to switch the output to csv-mode and switch to file output.

sqlite> .mode csv
sqlite> .output test.csv
sqlite> select * from tbl1;
sqlite> .output stdout

In addition to the above answers you can also use .once in a similar way to .output. This outputs only the next query to the specified file, so that you don't have to follow with .output stdout.

So in the above example

.mode csv
.headers on
.once test.csv
select * from tbl1;

To include column names to your csv file you can do the following:

sqlite> .headers on
sqlite> .mode csv
sqlite> .output test.csv
sqlite> select * from tbl1;
sqlite> .output stdout

To verify the changes that you have made you can run this command:

sqlite> .show

Output:

echo: off   
explain: off   
headers: on   
mode: csv   
nullvalue: ""  
output: stdout  
separator: "|"   
stats: off   
width: 22 18