Workbooks as Tables

The provider models the worksheets and ranges in your workbooks as bidirectional tables. When you connect, the provider retrieves the metadata from Microsoft Excel Online and reflects any changes to the table schemas.

See the Getting Started section for quickstarts to executing SQL to the tables. This section provides additional details on the provider behavior.

Tables

This section provides more detail about how the provider models the Tables. See Columns for details about column discovery.

Stored Procedures

In addition to working with the data in the workbook, you can use the available stored procedures to access functionality in the Microsoft Excel Online API that is not modeled as SELECT, INSERT, UPDATE, or DELETE statements; for example, the AddWorksheet stored procedure.

System Tables

Query the available System Tables to access the available provider metadata, including schema information.

Best Practices

1) If you currently have many workbooks or sheets in your account, it may take a long time to initially retrieve all the data. You may shorten this time by adding the Workbook connection property to your connection string.

2) If you will be running a SELECT operation after INSERT or BATCH INSERT then make sure to wait 3-5 seconds before running it. The API takes a few seconds to register all the data you're adding to the database, so waiting is required to avoid partial results.