Data Model

The connector supports retrieving Excel data from SharePoint On-Premises through the REST API and through the OData API introduced in SharePoint 2013. Excel files must have the .xlsx format used in Excel 2007 and later.

Note: Microsoft has stated that they plan to sunset the Excel Services API for SharePoint Online in February 2022. Please consider migrating to the CData connector for Excel Online for future access to your SharePoint data.

The connector can detect the tables in your workbook based on the following:

Spreadsheets
Excel tables
Ranges

The following sections provide more details on the available API objects.

Dynamic Metadata Querying

Through dynamic metadata querying, any changes you make in Excel, such as adding an Excel table or spreadsheet or modifying columns, are reflected when you reconnect.

Configuring Table Detection

The OData API and REST API (the default) each surface different API objects. Select which API to use based on how your workbook is organized and your Excel Services version.

Connecting to Excel Tables

The OData API surfaces only the Excel tables you defined in your spreadsheets with Insert -> Table.

Connecting to Spreadsheets, Ranges, and Excel Tables

If you need to access data outside of Excel tables, set UseRESTAPI to true; the REST API enables access to spreadsheets, ranges, and Excel tables as database tables.

See REST API for more information on executing SQL to spreadsheets and ranges.

Supported APIs by SharePoint Version

The following table shows which APIs are available for each SharePoint version: