TDV Adapter for Microsoft SharePoint Excel

Build 22.0.8462

Data Model

The adapter 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 adapter for Excel Online for future access to your SharePoint data.

The adapter 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 SharePoint 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:

SharePoint VersionAvailable APIs
SharePoint OnlineREST or OData
SharePoint On-Premises 2016REST or OData
SharePoint On-Premises 2013REST or OData
SharePoint On-Premises 2010REST

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462