How to export data from sqlite to CSV, HTML, text files, and other formats

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

I didn’t look at it closely yet, but I just saw that sqlite supports JSON with an extension: