iLie iLie - 1 month ago 4
SQL Question

How to import sqlite from plain text dump?

This excellent XKCD article about color-poll data at the very end mentions:

If you have ideas and want to analyze these results further, I’ve posted the raw data as an SQLite dump here (84 MB .tar.gz file).

In the archive you will find plain .TXT file
which is SQLite dump. I work on MAC and would like to turn this dump into a database. How can I do it and what friendly software can I use? I am very beginner at SQLite and DB. I tried DB Browser for SQLite (at
) but could not figure out how to import the dump (what I tried runs forever).

Thank you very much for any tips.


sqlite comes with a command line tool to manage databases that is called sqlite3. You can use it to create a new database and import a dump in it. The following command will do both of these things:

sqlite3 /path/to/database.sqlite < /path/to/mainsurvey_sqldump.txt

After that, you can find the database in /path/to/database.sqlite (it will be a single file). After that, if you don't mind working in the terminal, you can open the database as follows:

sqlite3 /path/to/database.sqlite

You can then type sql queries in the sqlite prompt.