0

I am looking for a way to query an Excel table (https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c) using pyodbc. As a test I created an Excel file with some named ranges and an Excel table:

Name Manager in Excel

I created a connection using the the Microsoft Excel Driver. Then I used the tables method of the Cursor object that listed the tables within the workbook. However, I can only see the worksheets and named ranges but not the Excel table that I named MyTable.

Is there a way to query the Excel table MyTable?

Thanks

import pyodbc
for driver in pyodbc.drivers():
    print(driver)
    if '.xlsx' in driver:
        xlsxDriver = driver

# OUTPUT:
# SQL Server  
# ODBC Driver 17 for SQL Server  
# Microsoft Access Driver (*.mdb, *.accdb)  
# Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)  
# Microsoft Access Text Driver (*.txt, *.csv)  

conn_str=  (r'DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};'
            r'DBQ=C:\Users\ondre\OneDrive\Documents\openpyxlTEST.xlsx;'
            r'ReadOnly=1')

cnxn = pyodbc.connect(conn_str, autocommit = True)
crsr = cnxn.cursor()

[worksheet for worksheet in crsr.tables()]

# OUTPUT:
# [('C:\\Users\\ondre\\OneDrive\\Documents\\openpyxlTEST.xlsx', None, 'Sheet1$', 'SYSTEM TABLE', None),
#  ('C:\\Users\\ondre\\OneDrive\\Documents\\openpyxlTEST.xlsx', None, 'Sheet2$', 'SYSTEM TABLE', None),
#  ('C:\\Users\\ondre\\OneDrive\\Documents\\openpyxlTEST.xlsx', None, 'MyTable2', 'TABLE', None),
#  ('C:\\Users\\ondre\\OneDrive\\Documents\\openpyxlTEST.xlsx', None, 'MyTable3', 'TABLE', None),
#  ('C:\\Users\\ondre\\OneDrive\\Documents\\openpyxlTEST.xlsx', None, 'MyTable4', 'TABLE', None)]

So for example I can query the MyTable3 named range but not MyTable:

crsr.execute("SELECT * FROM MyTable3")
[row for row in crsr]

# OUTPUT:
# [(datetime.datetime(1939, 3, 8, 0, 0), '1939-03-08 00:00:0', 2.0),  
#  (datetime.datetime(2063, 5, 13, 0, 0), '2063-05-13 00:00:0', 3.0),  
#  (datetime.datetime(2186, 1, 17, 0, 0), '2186-01-17 00:00:0', 5.0),  
#  (datetime.datetime(2210, 8, 13, 0, 0), '2210-08-13 00:00:0', 7.0),  
#  (datetime.datetime(2397, 12, 5, 0, 0), '2397-12-05 00:00:0', 8.0),  
#  (datetime.datetime(2420, 12, 9, 0, 0), '2420-12-09 00:00:0', 5.0),  
#  (datetime.datetime(2524, 7, 22, 0, 0), '2524-07-22 00:00:0', 1.0),  
#  (datetime.datetime(2623, 7, 19, 0, 0), '2623-07-19 00:00:0', 5.0),  
#  (datetime.datetime(2746, 7, 1, 0, 0), '2746-07-01 00:00:0', 10.0),  
#  (datetime.datetime(2836, 12, 22, 0, 0), '2836-12-22 00:00:0', 3.0)]
Ondrej
  • 43
  • 3
  • Show the error when attempting to query *MyTable* which appears to not have column names. Try adjusting range to start at row 1. – Parfait May 07 '23 at 13:49
  • The header of _MyTable_ is on the 1st row, the data is in Sheet1!$D$2:$F$11. The error when attempting to query _MyTable_: **ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'MyTable'. Make sure the object exists and that you spell its name and the path name correctly. If 'MyTable' is not a local object, check your network connection or contact the server administrator. (-1305) (SQLExecDirectW)")** – Ondrej May 07 '23 at 15:26
  • Why does MyTable not start its reference at $D$1? All other tables like MyTable3 which works in query do reference first row and the Value for these tables show column names. Carefully see your first screenshot. Again, please adjust named range of MyTable to start at D1 and try again in Python. – Parfait May 07 '23 at 20:39
  • 1
    I suspect that what you desire is simply not possible. The "Microsoft Excel Driver" is based on the Microsoft Access ODBC driver (ACEODBC.DLL) and the driver probably has not been updated to recognize an "Excel Table" (previously known as an "Excel List", ref: [here](https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c)) as a "table" in ODBC. – Gord Thompson May 07 '23 at 23:01

0 Answers0