SQLite3 as a data loader

Before everything else, you should go to SQLite3's official page and review the built-in commands - they will be, if not currently are, your great efficiency tool.

Make sure you understand at least the following:

• .schema and .tables for listing the database's metadata
• .mode for altering output mode
• .header for toggling header
• .output for redirecting output (esp. the result from SELECT) to a file

loading csv file

SQLite has a built-in function to load CSV data into a table.

sqlite3 <<EOF
.mode csv
.import "./data/airports.csv" airports
.schema
EOF

note the usage of EOF (i.e. heredoc) as a handy way to pipe multiple lines of input into a process. The shell command above starts a sqlite3 process which executes the commands.

You should see the schema output as:

CREATE TABLE airports(
  "ICAO" TEXT,
  "Code" TEXT,
  "Name" TEXT,
  "City" TEXT,
  "Country Code" TEXT,
  "Latitude" TEXT,
  "Longitude" TEXT,
  "Elevation" TEXT,
  "Time Zone" TEXT,
  "Region" TEXT
);

Note that when the airports table does not exist, the .import command automatically uses the first line of csv as the headers and use them to create table for you. Each column will be created using TEXT type.

However sqlite3 will behave differently when that airports table already exists in which case it will not use the first line as the header. You can utilize this to specify better types for the table. So you can update that script to be:

# this strips the header
tail -n +2 data/airports.csv > data/airports_without_header.csv
# now we load the data with table pre-created with proper types
sqlite3 <<EOF
CREATE TABLE airports(
  "ICAO" TEXT primary key,
  "Code" TEXT,
  "Name" TEXT,
  "City" TEXT,
  "Country Code" TEXT,
  "Latitude" numeric,
  "Longitude" numeric,
  "Elevation" int,
  "Time Zone" TEXT,
  "Region" TEXT
);
.mode csv
.import "./data/airports_without_header.csv" airports
select count(*) from airports;
EOF

Now you have a table with primary key, proper numeric types and all the data.