How to import and export CSV data with the help of SQLite

Published Apr 8, 20212 min read

If you're working with clients, sooner or later, they will send you some data in CSV format that you'll have to import into an existing table. Sometimes, it will be the whole CSV, and sometimes, it will be only a part of it.

Let's see how we can deal with that kind of situation using only SQLite to help us out.

Importing data to SQLite

SQLite makes it extremely easy to import CSV data and create a table from it. As an example, we'll show how to import a CSV file that we used for our latest showcase. The CSV file airports.csv contains almost 10k rows of data with all the world's airports.

Starting the SQLite shell:

> sqlite3

First, we want to import the CSV file.

sqlite> .mode csv
sqlite> .import airports.csv airports

We set the mode to CSV and then run an .import command that takes the CSV file as the first argument and a table as the second.

To check if everything was imported, you can run something like:

sqlite> select count(*) from airports;

This will return the number of records in the airports table. Since this is a SQL table now, you can do various kinds of queries.

Exporting data

The first thing we want to do is to specify an output file. This is going to be a .sql file that contains all the data.

sqlite> .output airports.sql
sqlite> .dump

Running those two commands will create an airports.sql file, the table schema and insert statements. You can now easily create a migration and import it into your database.

But sometimes, this is not what you want; you want only a specific part of the data. Let's see how to do this.

Exporting partial data

We need to switch to insert mode and define an output file. After that, all the queries we run will be saved as an insert statement in the output file.

sqlite> .mode insert
sqlite> .output partial-data.sql
sqlite> select * from airports where country_name_en = "Germany";

In this case, it will output all the rows that have the country name as Germany.

I hope you find this tutorial useful and that it will help you deal with CSV data in a better way.