Exporting and Importing Data

As well as querying data contained within a database, we may also need to be able to get data into and out of the database in bulk.

Exporting Data

Often you don’t want to have the data you just analyzed live in the SQL database only – you might want to export it so you can work with it. Sqlite supports exporting very nicely.

Using the command .mode helps you change the format of the result of your queries. By default, it is list, so our table will appear like this:

Michael|Grumpy|33
Anders|Happy|35
Zara|Happy|28

But you can change it to other formats like CSV (comma separated values), so our table will look like this:

Michael,Grumpy,33
Anders,Happy,35
Zara,Happy,28

If you use the command .header on you will also get a header row:

name,mood,age
Michael,Grumpy,33
Anders,Happy,35
Zara,Happy,28

The command .output allows you to choose where the results of your queries will be sent. By default, they are simply saved in memory in displayed on your screen. If you want to save your results in a CSV file, you should write:

.output yourfile.csv

and now any query you will be doing will be written to the file. Note that the file is saved in the folder where you terminal was when you launched sqlite. By default, the terminal operates in your Home folder.

If you don’t want your queries to be written in the file anymore, .output stdout reverts back to printing them on your screen.

If you decide to export your filtered data to a file using .mode, a good practice is to prepare your query until you like it and then execute it with a file it is written to.

Importing Data

Importing data works similarly. However, we have to prepare our table first. If you do have a .csv file, find out what columns you have and what data is in there. Create your table accordingly. Then set your .mode to csv as described above and use .import myfile.csv mytable

Check whether the header row has actually been imported as data and delete if necessary.