Comparing CSV Data Files Using SQLite

Whenever I have to compare large datasets generated by two different environments, such as Production and QA, I tend to load the data into a SQLite database first and then run SQL queries to diff the data.

The code below shows how you can import CSV files into SQLite:

-- create the tables to hold the data
CREATE TABLE dataProd (id text, price numeric);
CREATE TABLE dataQA   (id text, price numeric);

-- import the data
.separator ","
.import /path/prod/data.csv dataProd
.import /path/qa/data.csv dataQA

.headers ON

-- find differences between data
SELECT p.id,
       p.price as prodPrice,
       q.price as qaPrice,
       abs(p.price-q.price) as diff
FROM dataProd p, dataQA q
WHERE p.id = q.id
AND p.price <> q.price