I learned some quick, useful tips for working with sqlite that people might be interested in.
Here’s a quick snippet on how to export query results to CSV format:
sqlite> .mode csv sqlite> .output my_data.csv sqlite> SELECT * FROM my_table; sqlite> .output stdout
You can also change the separator with this command:
sqlite> .separator ", "
There are more details on section 5 of this page.
Basically, there are 8 output formats:
list– this is the default, pipe separated output
column– human readable
insert– SQL statements
line– uses equals signs, see the docs
quote– quoted output
tcl– I’m not sure what it does, but it’s probably related to this
You can then write the output to a file with the
.output command as shown in the example at the top of this post.
To reset the ouput location, to the terminal, type
.output by itself.
Another example that is useful for human-readable output:
sqlite> .mode column sqlite> .headers on sqlite> SELECT * FROM my_table LIMIT 10;
There is also a useful reference of SQL commands here.