xlDuckDb

Use DuckDB within Excel with the xlDuckDb addin.

DuckDB is an amazing tool with deep integration with Python and R, but sometimes you just need data in Excel. xlDuckDb allows DuckDB SQL to be run within Excel. Query results are returned as regular Excel cells.

Installation

Download the xlduckdb.zip file from the latest release on the right.

Use the "Extract All" option on the right-click menu in Windows Explorer to extract contents of the zip file on your local PC.

Right-click on the xlDuckDb64.xll file and unblock it:

Double click on the xlDuckDb64.xll file to open the addin in Excel. The function DuckDbQuery will be registered automatically.

Requirements

xlDuckDb only runs on 64-bit Excel 365 released after Sept 2018. The dynamic array resizing functionality is required.

xlDuckDb v0.6.0 and above require the .Net 8.0 or later runtime. Usually a compatible version of .Net will already be installed, however the RunTime can also be downloaded from .Net RunTime

Usage

Any DuckDB SQL can be run and the results will be returned to Excel.

A note on copying queries into Excel

When copying text such as SQL commands into Excel cells, add a ' at the start so that Excel treats the input as a string.

Querying Excel Ranges

Excel ranges can be queried as if the range was a regular DuckDb table. The keyword xlRange is used in queries, internally xlDuckDb substitutes the Excel range data.

SELECT * FROM xlRange

The range is passed as the third argument to the DuckDbQuery() function.

Here we copy in the 2024 F1 Drivers Points and query the range:

The data is in the range A1:E25

The range is passed as the third argument:

=DuckDbQuery(A27,,A1:E25)

And the query is run against the Excel range:

Named Ranges

Queries can be run against named ranges.

Here we pass the named range DriverPoints:

=DuckDbQuery(A27,,DriverPoints)

Table Data

Queries can be run against Excel table data.

Here we pass the table name PointsTable[All]:

=DuckDbQuery(A27,,PointsTable[All])

Note: Multiple Excel ranges can be passed to the DuckDbQuery() function. Any number of ranges can be passed as the 3rd, 4th, 5th..nth argument. These ranges can then be referenced in the SQL query as xlRange[1], xlRange[2]..xlRange[n].

Querying JSON files

Reading data from JSON files can be difficult, particularly if the data is nested. DuckDB allows the JSON file to be treated just like a regular database table.

SELECT firstname, surname, bornCountry FROM 'laureate.json' LIMIT 5

We can now easily find the countries with the most Nobel prize winners:

SELECT bornCountry AS Country, COUNT(*) AS Number FROM 'laureate.json' GROUP BY bornCountry ORDER BY COUNT(*) DESC LIMIT 5

DuckDB supports the use of JSONPath to extract values from nested JSON fields. This allows us to extract the category and motivation of the first prize awarded to each person:

SELECT firstname, surname, prizes->>'$[0].category' AS Category, prizes->>'$[0].motivation'AS Motivation FROM 'laureate.json' LIMIT 5

Querying CSV files

DuckDB is able to handle most CSV files automatically, detecting the column delimiters, data types, and so on. DuckDB allows the CSV file to be treated as a regular database table:

SELECT Region, "Item Type", "Total Revenue" FROM '10000SalesRecords.csv' LIMIT 5

DuckDB has a user-friendly PIVOT statement that allows us to view the revenues in Asia and Europe broken down by item type:

PIVOT '10000SalesRecords.csv' ON Region IN ("Europe", "Asia") USING sum("Total Revenue") GROUP BY "Item Type" ORDER BY "Item Type"

Data from multiple sources can be combined in a single SQL query. We can combine the JSON and CSV data to show the total sales in the countries with the most Nobel laureates:

WITH CountrySales AS (SELECT CASE WHEN Country='United States of America' THEN 'USA' ELSE Country END AS Country, Sum("Total Revenue") AS Sales FROM '10000SalesRecords.csv' GROUP BY Country) SELECT cs.Country, SUM(cs.Sales) AS Sales, COUNT(*) AS "Nobel Laureates" FROM CountrySales cs INNER JOIN 'laureate.json' l ON cs.Country = l.bornCountry GROUP BY cs.Country ORDER BY "Nobel Laureates" DESC LIMIT 5

Querying Parquet files

Apache Parquet is an open source, column-oriented data file format designed for efficient data storage and retrieval. DuckDB has extensive support for efficient querying of Parquet files.

SELECT Survived, Pclass, Sex, Age FROM 'titanic.parquet' LIMIT 5

We can now contrast the ages across cabin classes for survivors versus non-survivors:

WITH Survivors AS (SELECT Pclass, Age, Sex FROM 'titanic.parquet' WHERE Survived=1) PIVOT Survivors ON Pclass USING AVG(Age) GROUP BY Sex

Querying from remote locations

DuckDB has functionality to directly query data located on https and in AWS S3.

SELECT * FROM 'https://duckdb.org/data/holdings.parquet';

Similarly we can attach to the DuckDB stations database in S3 and query the number of stations in each country.

Thanks

xlDuckDb would not have been possible without the ExcelDNA and DuckDB.NET projects.

The DuckDB query in Excel functionality was extracted from the commercial product xlSlim. If you need the same DuckDB functionality within a Digicert EV certificate signed addin then xlSlim is what you are looking for.