CODEWITHSUNDEEP
pythonexcelpython-3.x
RKh
RKh

Reputation: 14161

Unable to execute Excel SQL Query from Python application

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

enter image description here

Upvotes: 0

Views: 1496

Answers (1)

Parfait
Parfait

Reputation: 107687

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)

Upvotes: 1

Related Questions