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 -
csv
– comma-separated -
column
– human readable -
html
– self-explanatory -
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.
Edit — also useful: Export SQLite Database To a CSV File