I am using SQLean in a script to process UK NHS waiting times (I'm a surgeon - not an IT professional). This uses 50 large (90+MB) CSV files and I have been using vsv.dll to load each file, extract data, then drop the file, finally dumping the extract.
The main attraction is using SQLite3.exe to run scripts or as a REPL. This neatly avoids using any other programming language i.e. a single script to maintain/share. (I would be reluctant to resort to batch files or another programming language if I can possibly achieve my goals with just SQLite3.exe).
The whole process of reading the CSV and dumping into Excel is brilliant. I guess much work has gone into the CSV reading library - many thanks.
With cut/paste I have made it work, but there is a great deal of repetition making further development difficult.
Googling I suspect there is a way to loop through a table of file names? This would seem to be a likely usage pattern for vsv.dll and I was hoping you might show me an example to follow?
I don't expect you to write the script for me but I have appended below my work so far.
Any guidance welcome.
-- 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")
;
CREATE TABLE waiting(
census, -- Year, 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, -- Excel formula
UNIQUE(census,provider,department,dataset)
);
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
FROM vsv
WHERE [Treatment Function Name]<>'Total'
GROUP BY [census],
[Provider Org Name],
[Treatment Function Name],
[RTT Part Description];
DROP TABLE vsv;
-- Loop ends
.headers on
.excel
SELECT * FROM waiting ORDER BY [provider];