Use SQLite from Excel. Think of it as the
sqlite3 command line shell on steroids.
Clone xll_sqlite in Visual Studio 2022,
open xll_sqlite.sln and press F5 to build and open Excel with the add-in loaded.
Open or create a sqlite database using =\SQL.DB(file, options).
If no arguments are specified a temporary in-memory database is created.
SQLITE_OPEN_XXX()
enumerations are provided for options. Add them to get the mask you want.
Get the big picture with =SQL.SCHEMA(db).
The common pragmas =SQL.TABLE_LIST(db)
and =SQL.TABLE_INFO(db, name)
are provided in addition to =SQL.PRAGMA(db, pragma)
that calls PRAGMA pragma.
Call =SQL.QUERY(db, sql) to return
the result of executing sql including headers. Use DROP(query,1) to remove the headers.
You can create a sqlite statement with =SQL.STMT(db)
and use the result as the first argument to
=SQL.PREPARE(stmt, sql).
Bind parameter values with =SQL.BIND(stmd, range)
where range is one-dimensional to specify positional parameters or a two column
range of key-value pairs to bind based on the key name. The binding type is
based on each value's Excel type.
Statements are executed with =SQL.EXEC(stmt).
Sqlite tables are created using
=SQL.CREATE_TABLE(db, name, data, columns, types).
The the columns and types are used for the schema in CREATE TABLE and data
are INSERT INTO to the table. If data is missing then the table is created with 0 rows.
If columns are not specified then the first row of data is used for column names.
The allowed _types are those specified
in the Affinity Name Examples.
If types are not specified the data is inspected to guess the type.
If table name exists it is dropped before being recreated.
It is also possible to create tables from a query using
=SQL.CREATE_TABLE_AS(db, name, stmt).
The new table will contain the result of executing the statement.
#include "fms_sqlite.h"
...
sqlite::db db(""); // in-memory database
sqlite::stmt stmt(::db);
stmt.exec("DROP TABLE IF EXISTS t");
stmt.exec("CREATE TABLE t (a INT, b FLOAT, c TEXT)");
stmt.prepare("INSERT INTO t VALUES (?, ?, :c)");
stmt[0] = 123; // calls sqlite3_bind_int(stmt, 0 + 1, 123);
stmt[1] = 1.23;
stmt[":c"] = "str"; // bind parameter name
assert(SQLITE_DONE == stmt.step());
stmt.prepare("SELECT * FROM t");
stmt.step();
assert(stmt[0] == 123);
assert(stmt["b"] == 1.23); // lookup by name
assert(stmt[2] == "str");
assert(SQLITE_DONE == stmt.step());
- Do I have to to the sweep out ranges, F2, Ctrl-Shift-Enter, rinse and repeat, then press Ctrl-Z when I see `#VALUE!`s to get the actual output of range-valued functions?
- Not if you use a modern version of Excel with dynamic arrays
- What are those funny numbers coming out of commands that start with a backslash (`\`)?
- They are 64-bit floating point C++ doubles with the same bits as the address in memory of the underlying C++ object. See handles
- My SQL queries are too big to put in one cell.
- You can spread the query over a range of cells. The values are concatenated using a space character before being sent to sqlite.
- Why is it so fast?
- Because it uses memory mapped files. Things will break when you try to return over 8GB of data.
- How did you create this add-in?
- Using my xll library. You can use it to embed C++ (or C, or Fortran, ...) in Excel.