fail to make pyodbc-4.0 64bit find excel driver (windows10-64bit)

Question

I don't catch how to connect to my excel file with pyodbc4, on windows 10 64 bit (python-3.6). As anybody a clue on my error in this code ?

import pyodbc
import os
sources = pyodbc.dataSources()
dsns = list(sources.keys())
dsns.sort()
sl = []
for dsn in dsns:
    sl.append('%s [%s]' % (dsn, sources[dsn]))
print('\n'.join(sl))

# DATABASE CONNECTION
filename = 'C:\\WinPython\\basedir36\\buildZero\\winpython-64bit-3.6.x.1\\notebooks\\test.xls'
assert os.path.exists(filename)

# was wrong: CNXNSTRING = 'Driver={Microsoft Excel Driver (*.xls)};DBQ=%s;READONLY=FALSE' % filename
CNXNSTRING = 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=%s;READONLY=FALSE' % filename
print ('CNXNSTRING is ', CNXNSTRING)
cnxn = pyodbc.connect(CNXNSTRING, autocommit=True)

result-in-64bit:

 >>> 
 RESTART: C:/WinPython/basedir36/buildZero/winpython-64bit3.6.x.1/notebooks/TEST6.py 
 Excel Files [Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)]
 MS Access Database [Microsoft Access Driver (*.mdb, *.accdb)]
 dBASE Files [Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)]
 CNXNSTRING is  Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\WinPython\basedir36\buildZero\winpython-64bit-3.6.x.1\notebooks\test.xls;READONLY=FALSE
 Traceback (most recent call last): File "C:/WinPython/basedir36/buildZero/winpython-64bit-3.6.x.1/notebooks/TEST6.py", line 17, in <module>
cnxn = pyodbc.connect(CNXNSTRING, autocommit=True)
 pyodbc.Error: ('IM002', '[IM002] [Microsoft][Gestionnaire de pilotes ODBC]     Source de donn�es introuvable et nom de pilote non sp�cifi� (0) (SQLDriverConnect)')
 >>> 

NEW findings: it works if I use pyodbc4- 32 bit and python-3.6 32 bit... so how to make it work in 64 bit ?

result-in-32bit:

>>> 
 RESTART: C:\WinPython\basedir36\buildZero\winpython-32bit-3.6.x.1\notebooks\TEST6.py 
Excel Files [Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)]
MS Access Database [Microsoft Access Driver (*.mdb, *.accdb)]
dBASE Files [Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)]
CNXNSTRING is  Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\WinPython\basedir36\buildZero\winpython-64bit-3.6.x.1\notebooks\test.xls;READONLY=FALSE
>>>

Answer 1

it works if I use pyodbc4- 32 bit and python-3.6 32 bit... so how to make it work in 64 bit ?

If the script is running under 64-bit Python then it needs to have the 64-bit version of the Excel ODBC driver installed.

There is an old office 2007 32 bit on this pc

It is possible to do a "forced install" of the 64-bit Access Database Engine (which includes the Access and Excel ODBC drivers) alongside a 32-bit version of Office, but that is not recommended because in some circumstances it can break the Office install. (It is called a "forced install" because the default behaviour of the Access Database Engine installer is to prevent it from happening.)

If the "old office 2007" is no longer being used then you can uninstall it and then install the 64-bit version of the Access Database Engine. If it is still being used then you probably should just stick with running the Python script as 32-bit.