Connecting to a Workbook
The ExcelFile, under the Authentication section, must be set to a valid Excel File (including the file path). The provider supports the Office Open XML format used by Excel 2007 and later.
Querying a Workbook
You can then execute SELECT, INSERT, UPDATE, and DELETE statements to spreadsheets and ranges in the workbook. See Excel Operations for details on querying spreadsheet data as tables.
Advanced Data Modeling Settings
Use the following properties to control the tables the provider discovers, return values, and other aspects of data access.
Schema Discovery
- Orientation: Model tables that match the orientation of your spreadsheet: Set this to Horizontal or Vertical.
- DefineTables: Explicitly specify the ranges you want to access as tables, using the standard Excel range format: [Table Name]=[Sheet Name]![Range]. Specify multiple tables in a semicolon-separated list:
DefineTables="Table1=Sheet1!A1:N25,Table2=Sheet2!C3:M53"
Note that the provider automatically detects the named ranges in a sheet and reports them as tables. You can also specify a range in the FROM clause:
SELECT * FROM Excel_Sheet#A5:F*
Column Discovery
- Header: Detect column names from the first row if true. Otherwise, the pseudo column names A, B, C, etc. will be used.
- TypeDetectionScheme: Determines how the provider detects the data types of columns.
- RowScanDepth: When using the row-scan type detection scheme, set this property to the number of rows to scan to determine column data types.
Advanced Excel Integration
Formulas
- AllowFormula: Whether to insert or update the formula result or the formula expression.
- Recalculate: Recalculate formulas when the data is changed.
- IgnoreCalcError: Ignore errors that occurred when formulas are calculated.
- HasCrossSheetReferences: Recalculate formulas across all worksheets when the data is changed; the provider will follow cross sheet references, but there is a performance cost.
Miscellaneous
- EmptyValueMode: Configure how to handle cells that are empty. An empty cell is a cell that contains an empty string (e.g., =""). You can return an empty cell as an empty string or a null string (default: return an empty string.)
- NullValueMode: Configure how to handle cells that have not been set. An unset cell is different from an empty cell. Unset cells contain a null string. You can return an unset cell as an empty string or a null string (default: return NULL).
- BufferChanges: Indicates whether to hold changes to the data in memory until the connection is closed.