Issue 114: pyodbc Excel worksheet specification error in query
Status:  NoFix
Owner: ----
Closed:  Aug 2010
Reported by ammanjjot, Aug 13, 2010
What steps will reproduce the problem?
1. Connect to the Ms Excel ODBC driver
2. Execute a query on the cursor to get data from an Excel Worksheet.
3.

What is the expected output? What do you see instead?
Expected Excel file data. Getting an error:

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Excel Driver] Syntax error in FROM clause. (-3506) (SQLExecDirectW)')


What version of the product are you using? On what operating system?
Python 2.5, Pyodbc 2.1.7, OS Windows XP Service pack 3

Please provide any additional information below.
I'm not able to specify an Excel worksheet as a table in the SQL query to read from. The connection works fine:

conn = pyodbc.connect('Driver={Microsoft Excel Driver (*.xls)};FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB="%s; DBQ= %s' % (db_file, db_file), autocommit=True)

cursor = conn.cursor()

The Excel file has following worksheets:
Sheet1
Sheet2
Sheet3

I can read the existing tables in the Excel file using the following code:
for row in cursor.tables():
   print "Tables = ", row.table_name


This returns the following tables:
Sheet1$
Sheet2$
Sheet3$

The '$' sign here is expected because the Excel ODBC driver uses this to denote the worksheets.

The SQL query using this information however results in an exception.

Query:
SQL = u"SELECT b FROM %s" % "Sheet1$"
or
SQL = u"SELECT b FROM `%s`" % "Sheet1$"

Error:
ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Excel Driver] Syntax error in FROM clause. (-3506) (SQLExecDirectW)')

Query:
SQL = u"SELECT b FROM %s" % "Sheet1"

Error:
ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'Sheet1'.  Make sure the object exists and that you spell its name and the path name correctly. (-1305) (SQLExecDirectW)")

Thanks!
AJ
Aug 24, 2010
Project Member #1 mkleehammer
pyodbc never modifies your SQL, so this is a problem finding the right specification for the Excel driver.

It is *possible* that there is a problem with a Unicode query string, but I don't think so.  I use them quite often.  There are some issues open regarding that, however, which I'll try to address in the next week or so and make a new release.

Since this really isn't a pyodbc issue, I'm going to close it, but I recommend you discuss this on the group if you haven't already.  Maybe someone already using Excel will know a trick or two.

Also, I'll try to create a set of unit tests for Excel like I have for some other data sources, but it is a bit difficult to maintain.

Good luck!
Status: Invalid
Aug 26, 2010
#2 ammanjjot
Thanks!
Aug 26, 2010
#3 omprakas...@gmail.com
You should enclose table name in square brackets in the sql query.

for ex:
rs = cursor.execute("select * from [Sheet1$]")


Aug 31, 2010
#4 ammanjjot
Thanks, but I already tried the square brackets and get the following error:

Error: ('07002', '[07002] [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1. (-3010) (SQLExecDirectW)')
Aug 31, 2010
#5 omprakas...@gmail.com
Can you please post the code for the example you have tried with square brackets or back quotes. I will check and give you feedback on that.

This is the code that worked for me:
conn = pyodbc.connect("Driver={Microsoft Excel Driver (*.xls)}; Dbq=c:\TestXLS.xls", autocommit=True)

cursor = conn.cursor()
rs = cursor.execute("select * from [Basic Sheet$] where Solution = 'test'")


Nov 21, 2010
Project Member #6 mkleehammer
(No comment was entered for this change.)
Status: NoFix