SQLite Forum

SQLite3.exe Command shell: Iterate over table of csv files?
Login
Hi Gavin,

You can script out your repetitive SQL staments by generating them dynamically via SQL statements, and invoking them via [.read](https://sqlite.org/cli.html#reading_sql_from_a_file).
The [generate_series()](https://www.sqlite.org/series.html) table function helps to create "loops".

Something like this:

```
.cd /MyProjects/#NHSWaitingTimes/

create table nhs_filenames as select
strftime('%Y%m', start_date) || '.csv' as file_name,
date(start_date, '+1 months', '-1 days') as census
from
(select date('2017-04-01', '+' || value || ' months') as start_date 
from generate_series(0,48));

create table nhs_sum as select
'([Gt ' || printf('%02d', value) || ' to ' || printf('%02d', value+1) || ' Weeks SUM 1]*' || printf('%02d', value+1) || ')+' as sum_line
from generate_series(0,51);

.headers off
.mode tabs
.separator "\r\n"
.output load_csv.sql

select 
'drop table if exists temp.vsv;',
'.import ' || file_name || ' temp.vsv',
'INSERT or IGNORE INTO waiting',
'    SELECT',
'    census,         -- [census]',
'    [Provider Org Name],            -- [provider]',
'    [Treatment Function Name],      -- [department]',
'    [RTT Part Description],         -- [dataset]',
'    sum(',

(select group_concat(sum_line, char(13) || char(10)) from nhs_sum),

'    ([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];'

from nhs_filenames;

.headers on
.mode csv
.read load_csv.sql
```