Importing and analyzing CSV data using SQLite

Aug 27, 2021

As SQLite is a lightweight database and allows using SQL to query data in a more flexible way than Google Sheets or MS Excel, I recently found out that it’s possible to import CSV data and run SQL queries on it.

This is how to do it:

# Create & open database
sqlite3 csv.db

# Change SQLite mode to CSV
.mode csv

# Import example CSV data from file system to SQLite database to `example` table
.import example.csv example

# Use .schema to show the DB schema & run queries on CSV data
SELECT * FROM example LIMIT 20;

Bonus:
TSV imports seem to work similarly by switching the mode to .mode tabs.

.mode tabs
.import example.tsv example