1

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
>>>
stonebig
  • 1,193
  • 1
  • 9
  • 13

1 Answers1

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • ok, that indeed was wrong... but it continue to fail if i do CNXNSTRING = 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=%s;READONLY=FALSE' % filename with exactly the same message... I'm lost in thoughts – stonebig Jan 07 '17 at 19:09
  • question updated to current situation, still clueless – stonebig Jan 07 '17 at 19:16
  • NEW finding: it works if I use pyodbc4- 32 bit and python-3.6 32 bit... so how to make it work in 64 bit ? – stonebig Jan 07 '17 at 19:25
  • @stonebig - Do you have 32-bit Office installed? If so, then it would probably be best to just use 32-bit Python. – Gord Thompson Jan 07 '17 at 19:41
  • There is an old office 2007 32 bit on this pc... that is my problem ? no Workaround to remain in 64 bit python ? – stonebig Jan 07 '17 at 19:46
  • ok... so my problem has no easy solution. thanks for the insight, Gord Thompson – stonebig Jan 07 '17 at 22:43