Hi,
I love minimal software solutions and am trying to use SQLite3.exe in a lightweight data processing workflow. The command shell is fantastic, I am especially impressed with the error messages when reading saved scripts.
My script processes UK NHS treatment waiting times from 50 large (90+MB) CSV files. I set up a table to hold extracted data, use Nalgeon's vsv library to load each file, extract data, then drop the file, finally dumping the extract. Below is part of my script:
-- Saved SQLite3.exe script
-- This returns too many rows for Excel 2000!
.load vsv
-- Create a table for waiting_times
CREATE TABLE waiting(
census, -- Year, fieldname/format varies from file to file!
provider, -- [Provider Org Name]
department, -- [Treatment Function Code]
dataset, -- [RTT Part Description]
months_sum int, -- Area under the curve
upto52_count int, -- [Total] to get average months
unknown_count int, -- [Patients with unknown clock start date
over52_count int, -- [Gt 52 Weeks SUM 1]
case_count int, -- [Total All]
mean_wait,
UNIQUE(census,provider,department,dataset)
);
-- for file 201704.csv
CREATE virtual TABLE temp.vsv
USING vsv(filename="/MyProjects/#NHSWaitingTimes/201704.csv", header=yes );
INSERT or IGNORE INTO waiting
SELECT
'2017-04-30' AS census, -- [census]
[Provider Org Name], -- [provider]
[Treatment Function Name], -- [department]
[RTT Part Description], -- [dataset]
sum(
([Gt 00 to 01 Weeks SUM 1]*1)+
([Gt 01 to 02 Weeks SUM 1]*2)+
([Gt 02 to 03 Weeks SUM 1]*3)+
([Gt 03 to 04 Weeks SUM 1]*4)+
([Gt 04 to 05 Weeks SUM 1]*5)+
([Gt 05 to 06 Weeks SUM 1]*6)+
([Gt 06 to 07 Weeks SUM 1]*7)+
([Gt 07 to 08 Weeks SUM 1]*8)+
([Gt 08 to 09 Weeks SUM 1]*9)+
([Gt 09 to 10 Weeks SUM 1]*10)+
([Gt 10 to 11 Weeks SUM 1]*11)+
([Gt 11 to 12 Weeks SUM 1]*12)+
([Gt 12 to 13 Weeks SUM 1]*13)+
([Gt 13 to 14 Weeks SUM 1]*14)+
([Gt 14 to 15 Weeks SUM 1]*15)+
([Gt 15 to 16 Weeks SUM 1]*16)+
([Gt 16 to 17 Weeks SUM 1]*17)+
([Gt 17 to 18 Weeks SUM 1]*18)+
([Gt 18 to 19 Weeks SUM 1]*19)+
([Gt 19 to 20 Weeks SUM 1]*20)+
([Gt 20 to 21 Weeks SUM 1]*21)+
([Gt 21 to 22 Weeks SUM 1]*22)+
([Gt 22 to 23 Weeks SUM 1]*23)+
([Gt 23 to 24 Weeks SUM 1]*24)+
([Gt 24 to 25 Weeks SUM 1]*25)+
([Gt 25 to 26 Weeks SUM 1]*26)+
([Gt 26 to 27 Weeks SUM 1]*27)+
([Gt 27 to 28 Weeks SUM 1]*28)+
([Gt 28 to 29 Weeks SUM 1]*29)+
([Gt 29 to 30 Weeks SUM 1]*30)+
([Gt 30 to 31 Weeks SUM 1]*31)+
([Gt 31 to 32 Weeks SUM 1]*32)+
([Gt 32 to 33 Weeks SUM 1]*33)+
([Gt 33 to 34 Weeks SUM 1]*34)+
([Gt 34 to 35 Weeks SUM 1]*35)+
([Gt 35 to 36 Weeks SUM 1]*36)+
([Gt 36 to 37 Weeks SUM 1]*37)+
([Gt 37 to 38 Weeks SUM 1]*38)+
([Gt 38 to 39 Weeks SUM 1]*39)+
([Gt 39 to 40 Weeks SUM 1]*40)+
([Gt 40 to 41 Weeks SUM 1]*41)+
([Gt 41 to 42 Weeks SUM 1]*42)+
([Gt 42 to 43 Weeks SUM 1]*43)+
([Gt 43 to 44 Weeks SUM 1]*44)+
([Gt 44 to 45 Weeks SUM 1]*45)+
([Gt 45 to 46 Weeks SUM 1]*46)+
([Gt 46 to 47 Weeks SUM 1]*47)+
([Gt 47 to 48 Weeks SUM 1]*48)+
([Gt 48 to 49 Weeks SUM 1]*49)+
([Gt 49 to 50 Weeks SUM 1]*50)+
([Gt 50 to 51 Weeks SUM 1]*51)+
([Gt 51 to 52 Weeks SUM 1]*52)+
([Gt 52 Weeks SUM 1]*52) ), -- [months_sum]
sum([Total]), -- [upto52_coubt]
sum([Patients with unknown clock start date]), -- [unknown_count]
sum([Gt 52 Weeks SUM 1]), -- [over52_count]
sum([Total All]), -- [all_count]
'=INT(IF(INDIRECT("F" & ROW())>0,INDIRECT("E" & ROW())/INDIRECT("F" & ROW()),INDIRECT("E" & ROW())/INDIRECT("I" & ROW())))' AS mean_wait -- This is an Excel formula
FROM vsv
WHERE [Treatment Function Name]<>'Total'
GROUP BY [census],
[Provider Org Name],
[Treatment Function Name],
[RTT Part Description];
DROP TABLE vsv;
-- !! Repeated for all 50 files !!
.headers on
.excel
SELECT * FROM waiting ORDER BY [provider];
This runs successfully and Excel pops up with the summary data - brilliant! I am really impressed with what can be done with this single tool.
However, I am uncomfortable with the length of the script and the amount of repetitive code. I know SQLite is designed to be embedded and would rely upon the host language to drive logic/loops. In the context of a saved CLI script it would be really nice to have some mechanism for iteration based upon a table of inputs e.g.
-- Create a table of files to process
CREATE TABLE filelist(
filename, -- UNIX path from USB root
census, -- Date as 2021-10-31
UNIQUE(filename,census)
);
INSERT INTO filelist (filename,census)
VALUES
("/MyProjects/#NHSWaitingTimes/201704.csv","2017-04-30"),
("/MyProjects/#NHSWaitingTimes/201705.csv","2017-05-31"),
("/MyProjects/#NHSWaitingTimes/201706.csv","2017-06-30"),
("/MyProjects/#NHSWaitingTimes/201707.csv","2017-07-31"),
("/MyProjects/#NHSWaitingTimes/201708.csv","2017-08-31"),
("/MyProjects/#NHSWaitingTimes/201709.csv","2017-09-30"),
("/MyProjects/#NHSWaitingTimes/201710.csv","2017-10-31"),
("/MyProjects/#NHSWaitingTimes/201711.csv","2017-11-30"),
("/MyProjects/#NHSWaitingTimes/201712.csv","2017-12-31"),
("/MyProjects/#NHSWaitingTimes/201801.csv","2018-01-31"),
("/MyProjects/#NHSWaitingTimes/201802.csv","2018-02-27"),
("/MyProjects/#NHSWaitingTimes/201803.csv","2018-03-31"),
("/MyProjects/#NHSWaitingTimes/201804.csv","2018-04-30"),
("/MyProjects/#NHSWaitingTimes/201805.csv","2018-05-31"),
("/MyProjects/#NHSWaitingTimes/201806.csv","2018-06-30"),
("/MyProjects/#NHSWaitingTimes/201807.csv","2018-07-31"),
("/MyProjects/#NHSWaitingTimes/201808.csv","2018-08-31"),
("/MyProjects/#NHSWaitingTimes/201809.csv","2018-09-30"),
("/MyProjects/#NHSWaitingTimes/201810.csv","2018-10-31"),
("/MyProjects/#NHSWaitingTimes/201811.csv","2018-11-30"),
("/MyProjects/#NHSWaitingTimes/201812.csv","2018-12-31"),
("/MyProjects/#NHSWaitingTimes/201901.csv","2019-01-31"),
("/MyProjects/#NHSWaitingTimes/201902.csv","2019-02-27"),
("/MyProjects/#NHSWaitingTimes/201903.csv","2019-03-31"),
("/MyProjects/#NHSWaitingTimes/201904.csv","2019-04-30"),
("/MyProjects/#NHSWaitingTimes/201905.csv","2019-05-31"),
("/MyProjects/#NHSWaitingTimes/201906.csv","2019-06-30"),
("/MyProjects/#NHSWaitingTimes/201907.csv","2019-07-31"),
("/MyProjects/#NHSWaitingTimes/201908.csv","2019-08-31"),
("/MyProjects/#NHSWaitingTimes/201909.csv","2019-09-30"),
("/MyProjects/#NHSWaitingTimes/201910.csv","2019-10-31"),
("/MyProjects/#NHSWaitingTimes/201911.csv","2019-11-30"),
("/MyProjects/#NHSWaitingTimes/201912.csv","2019-12-31"),
("/MyProjects/#NHSWaitingTimes/202001.csv","2020-01-31"),
("/MyProjects/#NHSWaitingTimes/202002.csv","2020-02-27"),
("/MyProjects/#NHSWaitingTimes/202003.csv","2020-03-31"),
("/MyProjects/#NHSWaitingTimes/202004.csv","2020-04-30"),
("/MyProjects/#NHSWaitingTimes/202005.csv","2020-05-31"),
("/MyProjects/#NHSWaitingTimes/202006.csv","2020-06-30"),
("/MyProjects/#NHSWaitingTimes/202007.csv","2020-07-31"),
("/MyProjects/#NHSWaitingTimes/202008.csv","2020-08-31"),
("/MyProjects/#NHSWaitingTimes/202009.csv","2020-09-30"),
("/MyProjects/#NHSWaitingTimes/202010.csv","2020-10-31"),
("/MyProjects/#NHSWaitingTimes/202011.csv","2020-11-30"),
("/MyProjects/#NHSWaitingTimes/202012.csv","2020-12-31"),
("/MyProjects/#NHSWaitingTimes/202101.csv","2021-01-31"),
("/MyProjects/#NHSWaitingTimes/202102.csv","2021-02-27"),
("/MyProjects/#NHSWaitingTimes/202103.csv","2021-03-31")
;
Q1. Using only SQLite3.exe - is there any way to repeat my CREATE virtual TABLE statement and my INSERT statement with parameters for "filename" and "census"?
I guess the processing of numerous files with SQLite is common, perhaps my request to do so without a host language is uncommon. The advantages of a single script file for maintenance/distribution are so attractive I would accept any solution even if a "quick" python/batch script seems more appropriate.
Any guidance welcome.
Kind Regards Gavin Holt
NB. Workflow Details
Batch file to load my saved scripts:
@start "sqlite" \MyProfile\sqlite\sqlite3.exe "" ".read %1:\=/"
The executable used is the 64bit version kindly supplied by Nalgeon,
I am also using his vsv.dll library to parse csv files:
https://github.com/nalgeon/sqlite (Version 3.35.5 2021-04-19)
https://github.com/nalgeon/sqlean
Raw data was hand edited to remove top lines and renamed to consistent filenames:
https://www.england.nhs.uk/statistics/statistical-work-areas/rtt-waiting-times/