Unable to execute Excel SQL Query from Python application

Question

From my Python application, I am trying to open an ADODB connection of Excel. The code is as below:

# Create Connection object and connect to database.
ado_conn = win32com.client.gencache.EnsureDispatch('ADODB.Connection')
ado_conn.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\\Test1.xlsx; Extended Properties ='Excel 12.0 Xml;HDR=YES'";

ado_conn.Open()

# Now create a RecordSet object and open a table
oRS = win32com.client.gencache.EnsureDispatch('ADODB.Recordset')
oRS.ActiveConnection = ado_conn    # Set the recordset to connect thru oConn

oRS.Open("SELECT * FROM [Orders]")

When I debug the application, it throws the error:

com_error(-2147352567, 'Exception occurred.', (0, 'Microsoft Access Database Engine', "The Microsoft Access database engine could not find the object 'Orders'. Make sure the object exists and that you spell its name and the path name correctly. If 'Orders' is not a local object, check your network connection or contact the server administrator.", None, 5003011, -2147217865), None)

In the Excel sheet the connection string looks like this:

Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Orders;Extended Properties=""

The Command Text is:

Select * from [Orders]

Even if the connection is there, it is throwing this error.

How to execute the above Excel query from Python application?

Edit: Excel connection screenshot added below

So you're using Python, to call a SQL query from Excel, that queries an Access database? — artemis
– artemis, Commented Jul 1, 2019 at 15:39

Parfait · Accepted Answer · 2019-07-11 16:10:04Z

1

To use the Jet/ACE SQL Engine to query Excel workbooks, you must use the $ address referencing which can be extended for specific cell ranges:

SELECT * from [Orders$]

SELECT * from [Orders$B4:Y100]

With that said, consider querying Excel workbooks directly from Python with either OLEDB provider or ODBC driver version and avoid the COM interfacing of Window's ADO object:

# OLEDB PROVIDER
import adodbapi
conn = adodbapi.connect("PROVIDER=Microsoft.ACE.OLEDB.12.0;" \
                        "Data Source = C:\\Test1.xlsx;" \
                        "Extended Properties ='Excel 12.0 Xml;HDR=YES'")
cursor = conn.cursor()

cursor.execute("SELECT * FROM [Orders$]")    
for row in cursor.fetchall():
    print(row)

# ODBC DRIVER
import pyodbc
conn = pyodbc.connect("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" \
                      "DBQ=C:\Path\To\Excel.xlsx;")    
cursor = conn.cursor()

cursor.execute("SELECT * FROM [Orders$]")    
for row in cursor.fetchall():
    print(row)

edited Jul 11, 2019 at 16:10

answered Jul 1, 2019 at 17:12

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

RKh Over a year ago

When I give '$' in query, it throws error: Sales_Report$ is an invalid name

Parfait Over a year ago

Make sure Sales_Order exists as actual worksheet. I see Order in your post above. Edit accordingly. And please report exact error as traceback of API (adodbapi or pyodbc) Same as above?

RKh Over a year ago

I have edited original post. Added screenshot. I want to execute the SQL query associated with the connection.

Parfait Over a year ago

I do not understand what you are trying. Did you run oRS.Open("SELECT * FROM [Orders$]")? What was specific error? This solution does not involve doing anything in Excel but make direct SQL query calls. Also, the latter solution avoids the win32 COM layer which is unnecessary as Python can query workbooks without Windows' ADO object using its own standard API (PEP 249) which includes modules like pyodbc. I assure you $ works as above shows.

RKh Over a year ago

Here I am confused. I change the sheet name to Sheet1 and changed to: oRS.Open("SELECT * FROM [Sheet1$]"). It worked. No issues. Let me clarify the issue. In the screenshot in OP, you see an SQL Query: "Select * from Orders". I actually want to execute this. The problem is that there is a PowerQuery. If due to authentication issues, the Power Query fails, I want this exception to be handled in Python. This is still not working.

|

Collectives™ on Stack Overflow

Unable to execute Excel SQL Query from Python application

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related