| Issue 114: | pyodbc Excel worksheet specification error in query | |
| 2 people starred this issue and may be notified of changes. | Back to list |
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
Status:
Invalid
Aug 26, 2010
Thanks!
Aug 26, 2010
You should enclose table name in square brackets in the sql query.
for ex:
rs = cursor.execute("select * from [Sheet1$]")
Aug 31, 2010
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
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
(No comment was entered for this change.)
Status:
NoFix
|