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:
First, we want to import the
sqlite> .mode csv sqlite> .import airports.csv airports
We set the
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.
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.