nalgeon / sqlean Goto Github PK

Are you still accepting public votes on incubator projects (#27)?

I see the issue was locked recently.

Repository owner locked and limited conversation to collaborators 8 days ago

I tried to add my vote for a candiddate but I don't see buttons on those without votes and clicking the buttons I do see doesn't add my vote.

Here is extension that implements Protocol Buffers that you might want to add to the existing set of extensions

I tried to load one of extensions and got this error. Do you have a plan to support M1? Thanks.

sqlite> .load ./re
Error: dlopen(./re.dylib, 0x000A): tried: './re.dylib' (mach-o file, but is an incompatible architecture (have 'x86_64', need 'arm64e')), '/Users/xx/Downloads/re.dylib' (mach-o file, but is an incompatible architecture (have 'x86_64', need 'arm64e'))

Hello,

In the latest release 0.15.1, on the top you stated with message:

json1 extension in SQLite 3.37 is buggy, switched to 3.36.

I tried to find the case but failed, could you please point to the bug in JSON1 ?

If you wish to change long double to double, then you must also get rid of the part that checks if a long double is larger than a double since there is no way to correctly convert a number that is larger than the number of bits in the floating point mantissa into an integer.

https://github.com/mayflower/sqlite-reverse-string was useful to me once, it has no dependencies, but could be obsoleted by a library encompassing more string functions

The sqlite tools package provides only a 32bit windows version of sqlite3.exe. The currently provided dll's look to be 64 bit. Is it possible to have a 32 bit precompiled package for windows?

SQLite version 3.35.4 2021-04-02 15:20:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load ./vsv
Error: %1 is not a valid Win32 application.

sqlite>

Hi

Could it be possible to add the symmetric percentile functions : 01, 05 and 10 (same logic as 90,95 and 99)?

Or even better a percentile function where the percentile is a parameter.

There is something wrong with some extensions...
They work in SQLite3 shell, but won't load or work correctly in sqlite3.dll and sqlite-jdbc (java sqlite driver).
test.zip

I have attached a java example.
wget https://github.com/xerial/sqlite-jdbc/releases/download/3.36.0.3/sqlite-jdbc-3.36.0.3.jar
wget https://jsoup.org/packages/jsoup-1.14.3.jar
javac -encoding utf8 -cp .:sqlite-jdbc-3.36.0.3.jar:jsoup-1.14.3.jar Extension.java

; with extensions downloading
java -cp .:sqlite-jdbc-3.36.0.3.jar:jsoup-1.14.3.jar Extension

; without extensions downloading
java -cp .:sqlite-jdbc-3.36.0.3.jar Extension

If you use Mac, you probably have to edit Extension.java and change
String ext = (File.separatorChar == '\') ? ".dll" : ".so";
to
String ext = ".dylib";

On my computer:
json1, stats can't be loaded
unicode can be loaded*, but doesn't work at all

I've been using the regexp extension prior to finding the re extension here. The regexp extension only implements the REGEXP statement but includes additional syntax. Would it be possible to update re to support the same syntax?

X*      zero or more occurrences of X
X+      one or more occurrences of X
X?      zero or one occurrences of X
X{p,q}  between p and q occurrences of X
(X)     match X
X|Y     X or Y
^X      X occurring at the beginning of the string
X$      X occurring at the end of the string
.       Match any single character
\c      Character c where c is one of \{}()[]|*+?.
\c      C-language escapes for c in afnrtv.  ex: \t or \n
\uXXXX  Where XXXX is exactly 4 hex digits, unicode value XXXX
\xXX    Where XX is exactly 2 hex digits, unicode value XX
[abc]   Any single character from the set abc
[^abc]  Any single character not in the set abc
[a-z]   Any single character in the range a-z
[^a-z]  Any single character not in the range a-z
\b      Word boundary
\w      Word character.  [A-Za-z0-9_]
\W      Non-word character
\d      Digit
\D      Non-digit
\s      Whitespace character
\S      Non-whitespace character

Hi,

thanks for your project again, and the incubator parts!
I've come across this extension for reading Apache parquet files sqlite-parquet-vtable which could be an incubator candidate, if license and author allows for it. There's no release of it apart from source.

best regards

I am trying to fix a bug in that library and add some other functions for my personal use, but I can't find the source code anywhere. Any pointers?

First off, thanks for putting this collection of extensions together. It is really useful to have them in one repo with consistent naming schemes and builds.

I noticed a problem earlier today when building the define extension with MSVC. I build some of the extensions from source with MSVC because I want them to be usable by a custom pysqlite3 (from https://github.com/coleifer/pysqlite3) which will run on an embedded Python client (PyXLL, a Python interpreter hosted within Excel) on Windows 64-bit. I have not done anything particularly special and built the extensions by hand and I tried the same today. It turns out that I had been using the 32-bit compiler that targets 64-bit code and everything seems to work fine for the other extensions except for define.
cl src\sqlite3-define.c -I%AMALGAMATION% -link -dll -out:dist\define.dll

The error I got (from testing loading the DLL in Python via ctypes) was interesting
OSError: [WinError 193] %1 is not a valid Win32 application

I was able to get it to work by using the 64-bit compiler (I am not a Windows C developer in general so I tend to use the most simple command-line rather than using projects and solution files in Visual Studio)

In digging into compilation a bit more, I found that there are some compilation issues with soundex (MSVC is not c99 compliant so some variable sized arrays had to be changed to malloc/free) and fileio (linker complained about undefined _symlink because -- presumably -- the compiler was not removing the unreachable code after the return 0)

Let me know if you are interested in having MSVC as a supported compiler and if so, how you would like it to be supported (I have a batch file that consists of a bunch of cl invocations similar to the one above).

regards,
pjjH

Unaccent is a nice feature, but fails with denormalized ordered sequences, and on all non-mark sequences, such as all non-european languages. There really should be normalization step added, like NFD, and maybe even add a field to cache this NFD string and a flag if already done (and equal as in 95% of all cases).

Hello!
Documentation about sqlite3unicode extension doesn't say about collations (it has no deal with functions like upper(), lower() etc). If you look at the source code, it overrides standard nocase collation:
https://github.com/nalgeon/sqlite-plus/blob/1ecfe980a1b430023cfb8edb1ae675d10ead485e/src/sqlite3unicode.c#L45

SELECT value 
FROM unnest(array(0,1,2,3,4,5))
WHERE value < 3;

Parse error: no query solution
(not sure what the behavior should be. I hoped for an other result)

SELECT value
FROM unnest (1);

crashes SQLite without error

Hello Anton,

Just read your article about SQLite is not a toy database and found that comments were not enabled, and I'm wondering if I can take this chance to ask do you happen to know how to implement the offsets function for FTS5?

Thanks!

Hi,

thanks for this GREAT initiative!
I haven't been able to load Windows dll extension in SQLite3 shell. I installed sqlite-tools-win32-x86-<version>.zip for having the console.

sqlite> .load ./math
Error: %1 is not a valid Win32 application

I tried to compile with gcc and cl on my own side without success (I'm not proficient in C and less on Windows C toolchain)

> C:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\VC\Auxiliary\Build\vcvars32.bat
> cl.exe sqlite3-math.c -link -dll "-out:sqlite3-math.dll"
sqlite3> .load ./sqlite3-math
Error: %1 is not a valid win32 application

Same result with gcc (gcc.exe -s -O3 -m32 -c -g -shared ./sqlite3-math.c -o ./sqlite3-math.dll). Compiles but cannot load.
Any help on my mistake?

1. split_part doesn't support non-ansi separator

   select split_part('привет-ы-мир-ы-привет-мир', 'ы', 2); --> got �иве, but expected: -мир-
   

2. split_part should support a negative part number

   select split_part('D:/MyDocs/Book1.xls', '/.', -2); --> Book1
   

3. Current text.dll can't be loaded to sqlite3 cli (Win7 x64, sqlte3cli x32): Error: Не найден указанный модуль
   Perhaps, -static flag is missed.
4. Use -s-flag to reduce dll size.

The incubator contains SQLite extensions which haven't yet made their way to the main set. They may be untested, poorly documented, too broad, too narrow, or without a well-thought API. Think of them as candidates for the standard library.

Here is a extension for making PostScript tokens (public domain):

jar:http://www.dessus.com/files/sqlite3extensions.zip!/pstoken.c

Note, that PostScript does not distinguish text/blobs; they are the same type in PostScript. However, this extension uses a different syntax for them.

Hi guys,

first of all, I would to thank you for this lib. It really saved my day(s) when I needed to calculate median on group results :)
I used it on a nodejs project on (a bit) primitive way: adding your precompiled binary into my source code and loading it via sqlite3 nodejs module. I came to idea to pack them into npm module to ease usage for other NodeJs developers (at least those that are not so eager to build it from source, like me :) ).
I just wanted to check with you are you ok with that idea? Off-course, If I do it, I plan to reference your original repo from readme of node wrapper project?

BR
Zoran

Kindly requesting as it does come in handy when developing PoCs with Grafana or many other technologies. Thank you in advance!

If you'd like to add new extension that implements rot47 cypher for the SQLite3 in addition to existing rot13, you can find it here

The reason for rot47 is - some communication messages are obfuscated with rot47 but keeping it in database as obfuscated messages make them non-searchable, so the proposed extension bring very light function that can encrypt/decrypt messages on the fly.

This is a just an FYI about something I found cool and interesting. No action is required.

The define extension is a great addition to the list. Really awesome.

While doing some experimentation with lifting of functions between Excel and SQLite and vice versa, I see that it is easy to box a table-valued parameter as JSON and then unbox it in the 'sproc'.

sqlite> create virtual table [L.FUNNY] using define((WITH T(n) AS (SELECT E.value->>0 as n FROM JSON_EACH(:j) AS E) SELECT n FROM T));
sqlite> select * FROM [L.FUNNY]("[[1],[2],[3]]") as F;
1
2
3

sqlite> WITH A AS (
  SELECT json_group_array(json_array(s.value)) as arr 
  FROM generate_series(1,5) as s
) 
SELECT F.* FROM A, [L.FUNNY](A.arr) as F; 
n                                                                                                                                                 
1                                                                                                                                                 
2                                                                                                                                                 
3                                                                                                                                                 
4                                                                                                                                                 
5                                                                                                                                                 

Can you guide me how to make this support for Arabic ?

Hello, thank you for this repo! I've been trying to compile the following library, but having no success, if you guys are able to compile and include in this repo, that would be wonderful.

https://github.com/cldellow/sqlite-parquet-vtable/

If you add -Wall gcc complains about a few things, see below. Might be good to fix?

$ gcc -Wall -fPIC -shared src/sqlite3-unicode.c -o dist/unicode.so
src/sqlite3-unicode.c:22: warning: ignoring #pragma warning  [-Wunknown-pragmas]
   22 | #pragma warning(disable : 4305 13 90)
      | 
src/sqlite3-unicode.c:31:32: warning: multi-line comment [-Wcomment]
   31 | #define SQLITE3_UNICODE_UNACC  // ~ 30KB increase \
      |                                ^
src/sqlite3-unicode.c: In function ‘sqlite3StrNICmp’:
src/sqlite3-unicode.c:5278:12: warning: operation on ‘ua’ may be undefined [-Wsequence-point]
 5278 |         ua = GlogUpperToLower(ua);
      |            ^
src/sqlite3-unicode.c:5279:12: warning: operation on ‘ub’ may be undefined [-Wsequence-point]
 5279 |         ub = GlogUpperToLower(ub);
      |            ^
src/sqlite3-unicode.c: In function ‘sqlite3StrNICmp16’:
src/sqlite3-unicode.c:5292:12: warning: operation on ‘ua’ may be undefined [-Wsequence-point]
 5292 |         ua = GlogUpperToLower(ua);
      |            ^
src/sqlite3-unicode.c:5293:12: warning: operation on ‘ub’ may be undefined [-Wsequence-point]
 5293 |         ub = GlogUpperToLower(ub);
      |            ^
At top level:
src/sqlite3-unicode.c:4917:33: warning: ‘likeInfoAlt’ defined but not used [-Wunused-const-variable=]
 4917 | static const struct compareInfo likeInfoAlt = {'%', '_', 0, 0};
      |                                 ^~~~~~~~~~~
src/sqlite3-unicode.c:4911:33: warning: ‘globInfo’ defined but not used [-Wunused-const-variable=]
 4911 | static const struct compareInfo globInfo = {'*', '?', '[', 0};
      |                                 ^~~~~~~~
src/sqlite3-unicode.c:4340:36: warning: ‘sqlite3UpperToLower’ defined but not used [-Wunused-const-variable=]
 4340 | SQLITE_PRIVATE const unsigned char sqlite3UpperToLower[] = {
      |                                    ^~~~~~~~~~~~~~~~~~~

sqlean is awesome, why would anyone want to download just one module?

It would be great to have tar or zip file to download all of the objects from one URL.
This would make it far easier to include in docker containers, reference from pip installs, etc.

Trying to compile the array extension on FreeBSD gives me an error on loading it. Some guidance would be welcome.

$ cc -g -fPIC -shared -I./array/*.c array.c -o array.so

drwxr-xr-x  3       11 Nov  8 19:26 ./
drwxr-xr-x  4        4 Nov  8 14:51 ../
drwxr-xr-x  2        8 Nov  8 14:42 array/
-rw-r--r--  1      620 Sep 20 14:20 array.c
-rwxr-xr-x  1    25512 Nov  8 19:26 array.so*
-rw-r--r--  1   734131 Sep 29 18:01 shell.c
-rw-r--r--  1  8550097 Sep 29 18:01 sqlite3.c
-rw-r--r--  1   613416 Sep 29 18:01 sqlite3.h
-rw-r--r--  1    37310 Sep 29 18:01 sqlite3ext.h

sqlite> .load ./array
Error: ./array.so: Undefined symbol "arrayscalar_init"

Dear sqlean team,

working with sqlite dates is a real pain because date modifiers don't take into account leap years making the output of them unreliable.

The users of my app would consider this a serious bug.

Example:

select datetime('1993-06-30 23:59', '+20 months') as dt;

should return 1995-02-28 23:59 but it returns 1995-03-02 23:59:00 because sqlite does not take into account leap years.

1. Do you know of any plugin with proper date and time functions which work bug free?
2. If not, do you know any hack which can solve this?

I believe a plugin for better date and time handling including convenience functions for formatting, parsing and modifying dates would very much help the community.

Best regards

Sqlite supports full-text search with fts5 extension, but out of the box there is only english tokenizer.

fts5-snowball extension adds support for the snowball stemmer for a variety of languages, which could serve as a good addition to the sqlite3unicode module.

Look at this PR, which fixes some crashes and adds support to sqlite < 3.20.0 (at this release fts5 extension api slightly changed).

SELECT load_extension('./re.so'); 
-- .load ./re
SELECT REGEXP_REPLACE('this year is 2021', '[0-9]+', '2050');

This returns 205 instead of 2050.

Linux. SQLite version 3.22.0

I'm using unicode.dll with mine sqlite engine as loadable extension.
nlower() function works as it suppose to, but lower() still does not lowercase unicode strings.

I guess lower() still uses original implementation.
How to make lower to use implementation provided by extension?

Hello,
I am wondering why the regexp functions are not flagged with SQLITE_INNOCUOUS and SQLITE_DETERMINISTIC.
It is giving me an error when trying to use regexp_replace in a generated column.

sqlite> .load ./re
sqlite> create table test as select "name" as name;
sqlite> alter table test add column new_name generated always as (regexp_replace(name,'.','N'));
Runtime error: error in table test after add column: non-deterministic functions prohibited in generated columns

This query in SQLiteSpy and the equivalent in Notepad++
SELECT regexp_replace('abcde','[bd]','_')
yields the result:
a_c_e
as I would expect.

The extension when added to SQLite Expert Personal 64 gives a different result:
a_cde

Is the app wrong or the extension?

When downloading from GitHub releases I can get the version number from the releases API in the name and tag_name fields.

Unless I explicitly save that info in a text file next to the .so files, I don't see how to detect the version of of sqlean after installation.

Is there another way to decode those files that I've missed?

Hi,

not a real issue, rather a question (SQLite-3.36.0).

I tried using crypto to hash some file paths automatically, but it doesn't seem possible to use it likewise in generated columns:

sqlite> create table t(pth, sha generated always as (hex(sha256(pth))) stored);  -- or any md5, sha384, or sha512 functions of course
Error: non-deterministic functions prohibited in generated columns

I thought hashing functions were determisitic (see). Why is that?
Using SQLITE_DETERMINISTIC (https://www.sqlite.org/deterministic.html) should be used at compile time of the extension, right?

Thanks for any tip if ever you happen to have time!

Would patches to SQLite be possible, too? Many things are possible by extensions but some things may need patching the code of SQLite itself, too, or are working better by patches. One patch is below but others are also possible too.

I made up a patch to improve SQLite by excluding the Unicode capabilities. (Hopefully can also be tested if combined with other patches too.)

• Non-unicode patch

• Program to apply patches

• Fossil repository with patches

Any chance to get the math.dylib extension work on Macs with Apple Silicon chip?

When I follow the first example in the README, I get an error:

sqlite> .load /opt/sqlean/stats
sqlite> select median(value) from generate_series(1, 100);
Error: no such table: generate_series

The median function does work.

sqlite> WITH v(n) AS (VALUES (1), (2), (3), (4)) SELECT median(n) FROM v;
2.5

I'm using release 0.6.1.

Is generate_series supposed to behave like PostgreSQL's function?

That's what I expected: so the example should return 50.5.

Am I missing something?

When using .*? in regexp_replace it fails with regexp(3): nested *?.

It would be much useful for me to benefit from an equivalent function to the CONV() one in MySQL, which converts numbers from one numeric base number system to another.

Hope you'll consider adding such function, and in any case, thank you very much for investing your time and efforts into this nice project !
Cheers

array — One-dimensional arrays for SQLite.

Originally posted by @nalgeon in #27 (comment)

Hello, I am trying to use the re extension for RegEx (on Windows, compiling for 32-bit). Functions all work, except for the regexp_replace function. It returns correctly when it doesn't find anything to replace, but there is a strange effect when something is replaced:

My Test case is
select regexp_replace('The current year is AD 2025', '[0-9]+', '2050')

The result varies on each call. For example, here are ten calls:
mainThe current year is AD
sqlThe current year is AD 2
The current year is AD 2050
The current year is AD 2050
sqlite_masterThe current ye
tbl_name='AAA' AND type!='t
mainThe current year is AD
sqlite_masterThe current ye
The current year is AD 2050
TEXTThe current year is AD

It behaves as if the call I make to sqlite_column_text() returns a pointer that is a variable number of bytes smaller than the actual start of the result string. I thus read some other bit of memory before the actual value starts, and loss the end of the value (I copy a number of bytes returned by sqlite3_column_bytes()).

Any ideas of what might be wrong?

-- James Powell

The "sqlite" prefix is dropped from the README, but is still present in the latest release (0.5.0).

b9e61df

https://github.com/nalgeon/sqlean/releases/tag/0.5.0

This tripped me up just now when trying the library for the first time.

Can you make a new release with the new names?

SELECT regexp_replace('2020-04-17 08:15:00-04', '-[0-9][0-9]$', '');

yields:

2020-17 08:15:00

.... whereas

SELECT regexp_replace('2020-05-17 08:15:00-04', '-[0-9][0-9]$', '');

yields

2020-05-17 08:15:00

Hi,
first of all thank you, this is a great repo, a great idea.

What about to add the REGEXP extension you have here:
https://www.sqlite.org/src/dir?ci=tip&name=ext/misc

Best regards

Hi,

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.

Kind Regards Gavin Holt

Raw data from:
https://www.england.nhs.uk/statistics/statistical-work-areas/rtt-waiting-times/
I hand edited these to remove top lines and create consistent filenames

Load vsv

.load vsv

My table of files:

-- 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 a table to hold summary

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)
);

My analysis for each file:

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

Export to excel:

.headers on
.excel
SELECT * FROM waiting ORDER BY [provider];

End: