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

**URL:** https://forum.codeselfstudy.com/t/how-to-export-data-from-sqlite-to-csv-html-text-files-and-other-formats/1423
**Category:** General Discussion
**Tags:** sqlite
**Created:** [February 26, 2019, 6:32pm UTC](https://forum.codeselfstudy.com/t/how-to-export-data-from-sqlite-to-csv-html-text-files-and-other-formats/1423 "2019-02-26T18:32:09Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![Josh](https://forum.codeselfstudy.com/user_avatar/forum.codeselfstudy.com/josh/32/4_2.png) [@Josh](https://forum.codeselfstudy.com/u/Josh)
#### Post date: [February 26, 2019, 6:32pm UTC](https://forum.codeselfstudy.com/t/how-to-export-data-from-sqlite-to-csv-html-text-files-and-other-formats/1423/1 "2019-02-26T18:32:09Z")

</div>

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:

```plaintext
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:

```plaintext
sqlite> .separator ", "

```

There are more details on section 5 of [this page](https://www.sqlite.org/cli.html).

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](https://www.sqlite.org/tclsqlite.html)

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:

```plaintext
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM my_table LIMIT 10;

```

There is also a useful reference of SQL commands [here](http://www.sqlitetutorial.net/sqlite-commands/).

Edit — also useful: [Export SQLite Database To a CSV File](https://www.sqlitetutorial.net/sqlite-export-csv/)

---

<div class="post-metadata">

### Author: ![Josh](https://forum.codeselfstudy.com/user_avatar/forum.codeselfstudy.com/josh/32/4_2.png) [@Josh](https://forum.codeselfstudy.com/u/Josh)
#### Post date: [March 1, 2019, 4:41pm UTC](https://forum.codeselfstudy.com/t/how-to-export-data-from-sqlite-to-csv-html-text-files-and-other-formats/1423/2 "2019-03-01T16:41:05Z")

</div>

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

- [https://www.sqlite.org/json1.html](https://www.sqlite.org/json1.html)