pyodbc Excel worksheet specification error in query

[GoogleCodeExporter]

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

Original issue reported on code.google.com by ammanjjot on 13 Aug 2010 at 2:10

[GoogleCodeExporter]

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!

Original comment by mkleehammer on 24 Aug 2010 at 3:59

• Changed state: Invalid

[GoogleCodeExporter]

Thanks!

Original comment by ammanjjot on 26 Aug 2010 at 11:58

[GoogleCodeExporter]

You should enclose table name in square brackets in the sql query.

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

Original comment by omprakas...@gmail.com on 27 Aug 2010 at 6:14

[GoogleCodeExporter]

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)')

Original comment by ammanjjot on 31 Aug 2010 at 8:32

[GoogleCodeExporter]

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'")

Original comment by omprakas...@gmail.com on 1 Sep 2010 at 4:14

[GoogleCodeExporter]

Original comment by mkleehammer on 21 Nov 2010 at 4:43

• Changed state: NoFix