SQLite Forum

SQLite3.exe Command shell: Iterate over table of csv files?
I do that with a wee Python script that looks (in part) as follows:

sql_import = '''
drop table if exists rawdata;
create virtual table rawdata using vsv(filename="%s",skip=1,schema="create table rawdata(number,date,zone,gender,age,status,state)",columns=7);
 insert into cases
      select date,
             count(*) as cnt
        from rawdata
    group by date, zone, gender, age, status
 on conflict (date, zone, gender, age, status)
   do update set cnt = max(cnt, excluded.cnt);

for file in reversed(sorted([x for x in os.listdir('.') if x.startswith('data') and x.endswith('.csv')])):
    db.executescript(sql_import % (file,))
    maxdate = db.execute('select max(date) from rawdata;').fetchone()[0].replace('-', '')
    db.execute('drop table if exists rawdata;')
    filename = 'data%s.csv' % maxdate
    print(file, filename, end=' ')
    if file != filename:
        if not os.path.exists(filename):
            os.rename(file, filename)
            print('* RENAMED *')
            print('* CANNOT RENAME *')
        print(file, 'OK')

which looks for all files called "data*.csv" in the current directory and processes them in reverse reverse sort order (assuming the names are dataYYYYMMDD.csv) that would be from oldest to newest) and any file with a badly formed name is renamed to the right name.