ShellProven
Lesson 05 · tested on Debian 13

SQLite in the terminal: query a CSV file in 5 minutes (and the text-number trap)

Turn a CSV file into a database with one sqlite3 command, then answer real questions with SQL: totals per region, sales in a date range, monthly sums, and an exported report. Along the way, see why imported numbers sort wrong (they arrive as text) and how to fix it with a typed table.

Video coming soon — @ShellProven

Commands

Exactly as run in the video. Full folder with setup script and log: terminal-tutorials/sqlite-csv.

# The CSV file
$ cat sales.csv
date,region,product,amount
2026-01-14,north,keyboard,49.90
2026-01-20,south,monitor,189.00
2026-02-03,north,mouse,9.50
2026-02-11,west,monitor,179.00
2026-02-27,south,keyboard,52.00
2026-03-05,west,mouse,12.00
2026-03-09,north,monitor,199.00
2026-03-18,south,mouse,8.75
2026-03-30,west,keyboard,47.50

# Import with one command
$ sqlite3 shop.db ".import --csv sales.csv sales"
$ sqlite3 shop.db ".schema sales"
CREATE TABLE IF NOT EXISTS "sales"(
"date" TEXT, "region" TEXT, "product" TEXT, "amount" TEXT);

# The trap: numbers stored as text
$ sqlite3 shop.db "SELECT product, amount FROM sales ORDER BY amount DESC LIMIT 3"
mouse|9.50
mouse|8.75
keyboard|52.00
$ sqlite3 shop.db "SELECT product, amount FROM sales ORDER BY CAST(amount AS REAL) DESC LIMIT 3"
monitor|199.00
monitor|189.00
monitor|179.00

# The real fix: a typed table
$ rm shop.db && sqlite3 shop.db "CREATE TABLE sales(date TEXT, region TEXT, product TEXT, amount REAL)"
$ sqlite3 shop.db ".import --csv --skip 1 sales.csv sales"
$ sqlite3 shop.db "SELECT typeof(amount), COUNT(*) FROM sales GROUP BY 1"
real|9

# Answering questions with GROUP BY
$ sqlite3 -header -column shop.db "SELECT region, COUNT(*) AS orders, ROUND(SUM(amount), 2) AS total FROM sales GROUP BY region ORDER BY total DESC"
region  orders  total 
------  ------  ------
north   3       258.4 
south   3       249.75
west    3       238.5 

# Dates and months
$ sqlite3 -header -column shop.db "SELECT date, product, amount FROM sales WHERE date >= '2026-03-01' ORDER BY date"
date        product   amount
----------  --------  ------
2026-03-05  mouse     12.0  
2026-03-09  monitor   199.0 
2026-03-18  mouse     8.75  
2026-03-30  keyboard  47.5  
$ sqlite3 -header -column shop.db "SELECT substr(date, 1, 7) AS month, ROUND(SUM(amount), 2) AS total FROM sales GROUP BY month"
month    total 
-------  ------
2026-01  238.9 
2026-02  240.5 
2026-03  267.25

# Export the result back to CSV
$ sqlite3 -header -csv shop.db "SELECT product, ROUND(AVG(amount), 2) AS avg_price FROM sales GROUP BY product" > report.csv
$ cat report.csv
product,avg_price
keyboard,49.8
monitor,189.0
mouse,10.08

Quick check

Same two questions as in the video. Checked here, in your browser — nothing is sent anywhere.

Transparency. Script and narration made with AI, human-reviewed. The robot and the dog are AI-generated cartoon characters. The commands and their output are real runs in a container.