Worksheet_To_Table
Extracts data from an Excel Worksheet
- Stored Procedure
- SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017
EXEC SQLHTTP.net.Worksheet_To_Table @WorkbookPath, [@SheetName], [@CellRange], [@FirstRowContainsHeaderNames], [@DecimalPrecision], [@DecimalScale], [@DisplayTableDefinition]
| Name | Type | Description |
|---|---|---|
| @WorkbookPath | nvarchar(4000) | Required. The path to a Microsoft Excel Workbook |
| @SheetName | nvarchar(4000) | Optional. Name of an existing Worksheet contained in the above Workbook. Default: Sheet1 |
| @CellRange | nvarchar(4000) | Optional. The range of cell to be extracted in a format such as A1:AB99. If no CellRange is provided, the whole Worksheet is selected. |
| @FirstRowContainsHeaderNames | bit | Optional. Indicates whether the first row consists of column headers. Default: 1 (true)
0 = First row does NOT contain column names and will therefore be generated automatically (ex. F1, F2, F3, etc.) |
| @DecimalPrecision | tinyint | Optional. The decimal precision that that non-integer values will be retrieved as. Default: 18 |
| @DecimalScale | tinyint | Optional. The decimal scale that that non-integer values will be retrieved as. Default: 4 |
| @DisplayTableDefinition | bit | Optional. Indicates whether to output a table definition of the returned table. Create table definition will be viewable in the Messages tab of SQL Server Management Studio’s Query Results Pane. See the last example below. Default: 0 (false) |
SQL Server 64 Bit Instances: This function requires the “Microsoft Access Database Engine 2010 Redistributable.” Please see the following for more information: https://www.microsoft.com/en-us/download/details.aspx?id=13255
Extracting data from a Worksheet cell range where column names are present
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @WorkbookPath varchar(MAX) DECLARE @SheetName varchar(4000) DECLARE @CellRange varchar(4000) DECLARE @FirstRowContainsHeaderNames bit SET @WorkbookPath = 'C:\ThisFolderExists\OLEDBProviders.xlsx' SET @SheetName = 'SampleSheet' SET @CellRange = 'A1:C3' SET @FirstRowContainsHeaderNames = 1 --Only two rows will display as the first row contains column names EXEC SQLHTTP.net.Worksheet_To_Table @WorkbookPath, @SheetName, @CellRange, @FirstRowContainsHeaderNames |
|
1 2 3 4 5 6 |
Provider ProgId Is32Bit ----------------------------------------------- --------------- --------- Microsoft OLE DB Provider for SQL Server SQLOLEDB.1 0 SQL Server Native Client 11.0 SQLNCLI11.1 0 |
Extracting data from a Worksheet cell range where column names are missing
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @WorkbookPath varchar(MAX) DECLARE @SheetName varchar(4000) DECLARE @CellRange varchar(4000) DECLARE @FirstRowContainsHeaderNames bit SET @WorkbookPath = 'C:\ThisFolderExists\OLEDBProviders.xlsx' SET @SheetName = 'SampleSheet' SET @CellRange = 'A1:C3' SET @FirstRowContainsHeaderNames = 0 --All three rows will display with genric column names EXEC SQLHTTP.net.Worksheet_To_Table @WorkbookPath, @SheetName, @CellRange, @FirstRowContainsHeaderNames |
|
1 2 3 4 5 6 7 |
A B C ----------------------------------------------- --------------- --------- Provider ProgId Is32Bit Microsoft OLE DB Provider for SQL Server SQLOLEDB.1 0 SQL Server Native Client 11.0 SQLNCLI11.1 0 |
This example demonstrates obtaining table definition of data retrieved from Excel
|
1 2 3 4 5 6 |
EXEC SQLHTTP.net.Worksheet_To_Table @WorkbookPath = 'C:\ThisFolderExists\OLEDBProviders.xlsx', @SheetName = 'SampleSheet', @FirstRowContainsHeaderNames = 1, @DisplayTableDefinition = 1 |
|
1 2 3 4 5 6 |
CREATE TABLE [ExcelTable]( [Provider] nvarchar(MAX) NULL , [ProgId] nvarchar(MAX) NULL , [Is32Bit] nvarchar(MAX) NULL ) |