SQL support inside Excel
Have you ever worked with data in Excel and thought to yourself: “This would be so much easier to do with SQL”?
With the tool I’m about to show you, you can actually use SQL in Excel. It’s convenient, powerful and it even comes with intellisense.
The tool I’m referring to is called QueryStorm. Let me show you what it looks like…
QueryStorm’s SQL engine works with Excel tables as if they were database tables!
Note that QueryStorm works with Excel tables, not sheets. This is because sheets can have merged cells, buttons, images, and other objects which do not belong inside a database.
To start querying an Excel table, simply click the SQL button in the Excel ribbon.
This will open up a new SQL script in the QueryStorm IDE which you can use to start querying your Excel tables.
Under the hood, QueryStorm uses the SQLite database engine. Because SQLite is a full-fledged SQL database engine, all of the features you’d expect from a SQL database are available in QueryStorm. This includes joins, grouping, aggregations, and even window functions.
QueryStorm doesn’t copy your data into the SQLite database. Rather, it exposes Excel tables directly to the SQLite engine through a custom adapter layer.
This means that when you use INSERT/UPDATE/DELETE statements to modify the data in a table, changes are immediately visible in Excel.
This makes it easy to clean and process workbook data in place, without having to import data into a database and export it back into Excel.
Users who are more familiar with the Data tab in the Excel ribbon can certainly use Power Query to perform operations such as joining and merging tables and cleaning data but using SQL this way is often more convenient and much more powerful.
The ability to run SQL queries is great, but for this functionality to be truly useful, the code editing experience must be convenient and pleasant for the user.
This is where QueryStorm’s code editor shines with advanced features such as:
- code completion
- syntax highlighting
- error highlighting
- bracket matching
- code snippets
- code formatting
- star expansion
These features make for an excellent user experience, but they also help non-technical Excel users who are interested in learning SQL. The ability to work with data both from the Excel user interface as well as from code, coupled with IDE features such as code completion makes QueryStorm an excellent tool for teaching and learning SQL.
What about performance?
The performance is impacted by the quantity of data returned and the amount of work the SQL query is doing. Let’s take a look at both aspects…
As an example of data throughput, on my one-year-old laptop (i7, 11850h), a simple select query returning 100k rows and 10 columns takes around 0.6 seconds to execute. This is quick enough that you rarely have to worry about it.
When dealing with large datasets, one important consideration is if the installed version of Excel is 32-bit or 64-bit. The 32-bit version of Excel has a hard limit of ~3GB of memory that is available to it, which can come into play with large datasets. Using the 64-bit version of Excel is preferred to avoid these kinds of issues.
Automatic indexing of columns
Even when the result is not large, a SQL query could still take a long time to execute in case it is doing a lot of work. For example, joining two massive tables would normally be an operation that would take a long time because for each row in table A we’d need to scan the entire table B for matching rows.
However, to make these kinds of queries quick, all columns are automatically indexed. This makes table joins (and filtering tables in general) extremely fast.
It’s still possible to write a query that will perform poorly (e.g. joining large tables by partially matching strings would prevent using the index) but situations when you need to do this are quite rare.
The Query() function in Excel
So far, I’ve discussed the IDE side of QueryStorm. But you can also use SQL in Excel as a function via the Query() function which you can get from the QueryStorm extensions store.
This works similarly to the
query() function in Google sheets, except it’s more powerful in that you can work with multiple tables and it support SQL fully.
It’s a dynamic array function so it requires Excel 365 in order to be able to spill results.
Results can auto-refresh (optionally), so every time you update one of the source Excel tables, the results of the function update automatically.
Here’s a video showing the function in action:
Using the Query() function doesn’t require a license for the QueryStorm IDE. It doesn’t even require the IDE to be installed. All that is required is the free QueryStorm runtime, which you use to install and run the Windy.Query package.
The Query function uses the same SQLite engine under the hood, so the performance is the same as in the IDE.
Connecting to external databases
In addition to using the SQLite engine, QueryStorm also supports connecting to external SQL databases including SQL Server, MySQL, Postgres, Oracle, Access, etc… This makes it easy to query and import database data from Excel.
Two-way data visibility
In addition to reading database data from Excel, we can also do the opposite i.e. make Excel data visible to the database. When connecting, you can select which Excel tables will be pulled into the database in the form of temp tables. This lets you query Excel tables alongside database tables, making both importing and exporting data between Excel and databases a trivial task.
Excel database functions
Another neat thing you can do is use SQL to write custom Excel functions that return data from databases.
You can use these functions yourself but you can also publish them to make them available to your colleagues and clients.
To installs and use them, they just need to have the QueryStorm runtime installed.
The QueryStorm IDE requires a license to use. However, a free community version is available for personal use and for small companies. Companies with annual revenue of over $1M require a paid license, however, a free 14-day trial that unlocks all features is available for evaluation in larger companies. See the pricing page for more details.
The QueryStorm runtime, however, is completely free. It does not require a license to work. It’s a small 4MB installer and once installed, it will allow users to download and run packages such as Windy.Query which contains the Query(sql) function.
QueryStorm is an add-in for Excel that lets you run SQL queries directly against your Excel tables. It comes with an SQLite engine that can work with Excel tables and an IDE that makes code editing quick and enjoyable.
It’s a great tool for data processing and teaching/learning SQL.
Whether you’re a software developer, data analyst, data scientist, or business user, QueryStorm is a great tool to have in your toolbox. Go ahead and give it a try, it’s free!