DSN is not resolved when called from Excel

[staticlibs]

Until now, I have not been using python ODBC. Let me install it on my windows box...

done...

Trying now with my zzz DSN which for me is defined as

print(pyodbc.connect("DSN=zzz;").cursor().execute("select current_catalog()").fetchone())

... succeeds and reports the database

So, pyodbc is "playing nice" with the windows ODBC driver.

But, on the same windows box, "Excel > Data > Get Data > Other Source > ODBC", as follows:

fails, returning:

@staticlibs I thank you for your effort in recreating my situation with db on network path. Would you be willing to try querying your duck_user_2 connection from Excel?

FWIW, even trying @shwivel 's workaround, like this:

continues to use :memory:

Note: everything under Excel works when I use a DSN defined to use sqlite ODBC driver to connect with sqlite database exported from duckdb. So, it does not seem to be an Excel problem, rather DuckDB seems to provide incomplete support for connection options.

Originally posted by @malcook in #29

[staticlibs]

@malcook

Thanks for the screenshots, I am able to reproduce this in Excel 2016. Moving to the new issue, because it looks to be specific to Excel/Query Editor.

> python
Python 3.9.2 (tags/v3.9.2:1a79785, Feb 19 2021, 13:44:55) [MSC v.1928 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> print(pyodbc.connect("DSN=user_test_3;").cursor().execute("select current_catalog()").fetchone())

('test3',)

[staticlibs]

@malcook

In my environment it appeared to be caused by the credentials that are being appended to the ODBC connection string by Excel. When credentials are included - this confuses the DuckDB ODBC driver and it cannot find the DSN:

>>> import pyodbc
>>> print(pyodbc.connect("DSN=user_test_3;").cursor().execute("select current_catalog()").fetchone())
('test3',)
>>> print(pyodbc.connect("DSN=user_test_3;UID=foo;PWD=bar;").cursor().execute("select current_catalog()").fetchone())
('memory',)

This needs to be fixed in the DuckDB ODBC Driver, meanwhile the workaround for Excel is to delete the default credentials for DuckDB DSNs:

After that, when new Query Editor is being opened, it is necessary to choose "Default or Custom" instead of "Database" in the corresponding dialog:

[malcook]

Thanks. This "workaround" works around for me.