>_ IzzyLab

Hands-on task

This task is step 21 of the course “SQL with SQLite”

SQLite: import data from CSV

medium~7 min

The file /root/data.csv contains a CSV with the header name,population and three rows of city data. Create a table cities(name TEXT, population INTEGER) in the database /root/geo.db and import the data from this CSV into it (the header row should not go into the table). In the end the table must contain exactly 3 rows with correct values.

You can look at the CSV in the Editor tab. Put the import script /root/import.sql together there too — CREATE TABLE plus sqlite3 dot commands — and run it.

What you'll practise:
  • csv import
  • dot-commands
  • sqlite3
  • .import
  • create table

Topic breakdown

Besides SQL, the sqlite3 utility has dot-commands (they start with a dot) — commands of the utility itself. To load data you need .mode csv (switches the input parser to CSV) and .import (loads the file row by row into an already-existing table), with the mode set before the import. Create the table with the right columns and types beforehand using a plain CREATE TABLE — .import only fills a ready structure. A separate nuance is the CSV header row: it must not be entered as data, and modern .import can skip the first lines of the file with the corresponding flag.

What's checked

  1. exactly 3 rows imported
  2. values imported correctly
  3. import script /root/import.sql with .import

What it looks like