The Excel driver supports loading data from either XLS or XLSX files.
Connection String Parameters:
|
Keyword |
Value |
Description |
|
ignoreemptyvalues |
true/false |
Sets the flag whether the driver should ignore rows where all numeric values containt 0 and all string values are empty |
|
datasource |
C:\myfile.xlsx |
Filename including path |
|
detectionrowscount |
100 |
Number of rows from file to sample when detecting data type |
Query Parameters:
Retrieving the data from the file is done by performing a SELECT command, e.g:
SELECT * FROM [Case_Sensitive_Name_Of_Sheet]
Please note that the name of the worksheet is CASE SENSITIVE, also the driver does not support retrieving individual columns. If this is required the solution is to load the data into another table after the import finishes. In case it's necessary to load all sheets in a file the list of sheets can be accessed by the sys.sheets function e.g.
SELECT * FROM [sys.sheets]
Example:
LOAD ASSEMBLY 'TARGIT.Excel.dll'
DATASOURCE [ExcelFile] = DOTNET CONNECTION 'TARGIT.Excel.ExcelConnection' 'ignoreemptyvalues=false;datasource=C:\DemoData\Budget.xlsx;detectionrowscount=100;'
IMPORT [ListOfSheetsInBudget] = [ExcelFile].{select * from [sys.sheets]}
IMPORT [Budget] = [ExcelFile].{select * from [My Budget Sheet]}
Comments
Please sign in to leave a comment.