ODBC Driver for Excel on macOS

[adamkk]

What happens?

After installing duckdb on macOS with Sonoma I struggle to connect duckdb to MS Excel - the latest Version 16.91 (24111020). Please help and issue a blog article for dummies - how to make MS Excel see duckdb on a Mac (also on Win10/Win11). Many accountants would like to match a fast duckdb with a user friendly Excel on the client side.

Problem - Excel cannot connect to a duckDB ODBC Driver. Thus I cannot use my preferred configuration: duckdb as the main database and Excel as a client.

Actions completed:

1. brew install duckdb

2. isql -v DuckDB shows live duckDB
   myname@user ~ % isql -v DuckDB
   +---------------------------------------+
   | Connected! |
   | |
   | sql-statement |
   | help [tablename] |
   | echo [string] |
   | quit |
   | |
   +---------------------------------------+
   SQL> PRAGMA database_list;
   ------------------------------------------------------+
   | 1148 | memory |

3. The ODBC drivers are in:
   myname@user ~ % odbcinst -q -j
   unixODBC 2.3.12
   DRIVERS............: /opt/homebrew/etc/odbcinst.ini
   SYSTEM DATA SOURCES: /opt/homebrew/etc/odbc.ini
   FILE DATA SOURCES..: /opt/homebrew/etc/ODBCDataSources
   USER DATA SOURCES..: /Users/myname/.odbc.ini

4. odbc.ini is:
   [ODBC]
   Trace=yes
   TraceFile=/tmp/odbctrace

[DuckDB Driver]
Driver=/Users/myname/duckdb_odbc/libduckdb_odbc.dylib

[DuckDB]
Description = ODBC for DuckDB
Driver = /opt/homebrew/lib/duckdb_odbc.so

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.17.dylib
UsageCount=1

5. odbc.ini
   [DuckDB]
   Description = DuckDB ODBC
   Driver = DuckDB
   Database = /Users/myname/quack.db
   allow_unsigned_extensions = true
   User = # Leave User blank
   Password = # Leave Password blank

6. A file .odbc in catalogue /Users/myname/.odbc.ini
   [DuckDB]
   Driver = DuckDB Driver
   Database = /Users/myname/quack.db
   allow_unsigned_extensions = true

[ODBC Data Sources]
DuckODBC = DuckODBC

[ODBC]
Trace = 0
TraceAutoStop = 0
TraceFile = /var/root/sql.log
TraceDLL =

[DuckODBC]
Driver = /opt/homebrew/etc/odbc.ini

7. When checking on the Excel side the OBC files there are 3 options:

A. USER DSN - while adding DuckODBC I see error IM003 [iODBC][Driver Manager]Specified driver could not be loaded
B. SYSTEM DSN - while adding DuckODBC I see error Request failed

Appreciate a fix or a .pkg file to make the instalment & configuration process quick for the end users.

To Reproduce

brew install duckdb
odbcinst -q -j

OS:

macOS Sonoma

DuckDB Version:

duckdb 1.1.3

DuckDB Client:

Excel

Hardware:

MacBook Air M2

Full Name:

Adam Kaliszewski

Affiliation:

BCT

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

Did you include all relevant data sets for reproducing the issue?

No - Other reason (please specify in the issue body)

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have