Shell and SQLite: Build a Validated CSV Report - Yenra

Let the shell arrange files and check execution, and let SQL validate and aggregate records. This exercise imports a fictional order CSV into a fresh SQLite database, calculates totals in integer cents, and replaces a report only after the query succeeds.

You need a Unix-style shell, the SQLite command-line program, mktemp, mv and rm. The exercise was checked using Git Bash 5.2.37 and SQLite 3.53.4. Work in a private practice directory with one running copy of the script. The example has no scheduling or concurrency lock.

Run the supplied report

Download the SQLite reporting kit, extract it, and open a shell in the extracted sample directory. Confirm sqlite3 --version works, then run:

sh report.sh
cat report.csv

The three invented orders are two units at 1,200 cents for Ada, three at 1,000 cents for Ben, and one at 800 cents for Ada. Expected CSV:

customer,total_cents
Ada,3200
Ben,3000

Ada's total is 2 × 1200 + 1 × 800 = 3200 cents. Ben's is 3 × 1000 = 3000 cents. The combined total is 6,200 cents. The example performs no currency conversion, tax calculation or rounding. Integer cents keep the supplied arithmetic exact within the stated numeric limits.

Import, validate, then group

.bail on
CREATE TABLE incoming(order_id TEXT,customer TEXT,quantity TEXT,unit_cents TEXT);
.import --csv --skip 1 orders.csv incoming
BEGIN;
CREATE TABLE orders(
  order_id TEXT PRIMARY KEY NOT NULL CHECK(length(order_id)>0),
  customer TEXT NOT NULL CHECK(length(customer)>0),
  quantity INTEGER NOT NULL CHECK(typeof(quantity)='integer' AND quantity BETWEEN 1 AND 10000),
  unit_cents INTEGER NOT NULL CHECK(typeof(unit_cents)='integer' AND unit_cents BETWEEN 0 AND 100000000)
);
INSERT INTO orders SELECT order_id,customer,quantity,unit_cents FROM incoming;
COMMIT;
.headers on
.mode csv
.separator "," "\n"
SELECT customer,SUM(quantity*unit_cents) AS total_cents
FROM orders GROUP BY customer ORDER BY customer;

The first table holds incoming values as text. The CLI's .import --csv --skip 1 reads the CSV and skips its header. SQLite's command-line documentation explains these dot commands; they are instructions to the CLI and will not run as SQL through a database library.

The second table expresses this exercise's data rules. Each order has a nonempty unique identifier, a nonempty customer name, a positive integer quantity up to 10,000, and integer unit cents from zero through 100,000,000. SQLite's type affinity can convert a numeric text value to an integer; the typeof checks reject values that remain text or a nonintegral number. The CREATE TABLE documentation describes affinity and constraints.

These are deliberately small validation rules. A string of spaces passes a nonempty check, and numeric spellings that convert to the same integer can pass. A production import may need normalization, header validation, exact decimal syntax, row-count limits and a rejected-record report. The sample assumes the supplied four-column header and well-formed CSV; edit values within that format for the exercises.

The insert is transactional. A constraint failure aborts the run through .bail on and the command-line -bail option. The final query groups by customer and sorts the output explicitly, making the result easy to compare between runs. Each invocation uses :memory:, so rerunning the script starts from an empty database and avoids duplicate accumulated records.

Preserve the last successful output

#!/bin/sh
# Run from the extracted sample directory. Uses a fresh in-memory database.
# Article: https://yenra.com/unix-shell-database-programming/
if ! command -v sqlite3 >/dev/null 2>&1; then
  printf 'Install the sqlite3 command-line program first.\n' >&2
  exit 2
fi
if [ ! -r orders.csv ] || [ ! -r report.sql ]; then
  printf 'Run from the folder containing orders.csv and report.sql.\n' >&2
  exit 2
fi
tmp=$(mktemp ./report.XXXXXX) || exit 1
trap 'rm -f "$tmp"' 0
trap 'exit 1' HUP INT TERM
if sqlite3 -batch -bail :memory: < report.sql > "$tmp"; then
  if mv -f "$tmp" report.csv; then
    printf 'Wrote report.csv\n'
  else
    exit 1
  fi
else
  printf 'Report failed; previous report.csv retained.\n' >&2
  exit 1
fi

The temporary file receives the CLI's output. The script promotes it to report.csv only after SQLite returns success. Cleanup removes a leftover temporary file when the script exits. A successful run intentionally replaces the previous report; a SQL failure leaves that previous report available.

The temporary file sits beside the destination, which keeps the final move within the same directory. This is a single-runner teaching example for a trusted folder. Before using the pattern in a scheduled service, define overlapping-run behavior, permissions, disk-full handling, reporting and retention. A database transaction protects database work; the shell still needs its own policy for publishing a file.

Make the failure visible

After generating a good report, edit one quantity in orders.csv to oops, then run sh report.sh again. Expect a constraint error, a nonzero exit status and the previous successful report.csv unchanged. Restore the original CSV and rerun to recover.

For a real reporting job, keep the input batch identifier and row count beside its result so an operator can identify what was processed. Keep credentials out of command arguments and logs when adapting the method to a server database. The shell reference explains status and redirection; the integration planning guide extends the same questions to repeated API deliveries.