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.