Excel

EDIS Online User Guide – Excel

EDIS has built in support to import and export Excel files, as well as do more advanced features such as bolding headers, adding auto filters, and renaming worksheets.

Excel Import Task
This tasks imports a spreadsheet to SQL Server from Excel. It supports importing xls, xlsx, and xlsm files.

Procedure: SSISDB.EDIS.usp_excel_import_data

Parameters

Name Description Default Value
@file_path File path to the Excel Spreadsheet None; Required
@sheet_nm The name of the Excel sheet to import None; Required
@include_headers Flag indicating if the first row contains headers None; Required
@dest_tbl_nm The name of the target destination table to import the data to. If the target is not a temporary table, it needs to be 3-part qualified i.e. [database].[schema].[table] None; Required
@crt_dest_tbl Flag indicating if you want to have EDIS create the destination table for you if it does not exist. 0 (False)
@load_on_ordinal Flag indicating if you want to import the spreadsheet to the destination table based on the column order instead of the column name. The default is that EDIS will map columns from the spreadsheet to the table based on the column name 0 (False)
@import_all_as_text Flag indicating if you want to import all the columns as text and not have EDIS try to guess the data type. This flag only takes effect if you are using the @crt_dest_tbl = 1 flag to create the destination table. If this flag is set to true, each imported column will be treated as nvarchar(4000) for the data type. Otherwise, EDIS will use the @batch_size count to sample the rows and guess the data types. 0 (False)
@batch_size Batch size for importing rows. 10,000
@header_rows_to_skip Use this parameter to specify a number of rows to skip before reading the data. This is useful when Excel files contain header information on the first few rows and you don’t need to import the information. 0
@include_row_id Flag indicating if you want to include a column for the physical row number from the source. If this flag is set to 1 (True), a column called “EDIS_ROW_ID” will be added to the import table. 0 (False)

Example: This example imports a tab called “data” to temp table ##sales_raw_import. Since we are using the @crt_dest_tbl flag, EDIS, will create the table for us.

EXEC SSISDB.EDIS.usp_excel_import_data

 @file_path = 'C:\temp\sales.xlsx'

,@sheet_nm = 'data'

,@include_headers = 1

,@dest_tbl_nm = '##sales_raw_import'

,@crt_dest_tbl = 1;

 

Excel Export Task
This tasks exports data from SQL Server to a spreadsheet. If the Excel file does not exist, EDIS will create it for you on the fly. This task supports exporting to xls, xlsx, and xlsm files.

Procedure: SSISDB.EDIS.usp_excel_export_data

Parameters

Name Description Default Value
@file_path File path to the Excel Spreadsheet None; Required
@sheet_nm The name of the Excel sheet to export to None; Required
@src_qry The source query that will be used to export the data to the excel file None; Required
@bold_headers Flag indicating if you want to bold the headers on the sheet 0 (False)
@autofit_cols Flag indicating if you want to autofit the columns in the spreadsheet 1 (True)
@use_autofilter Flag indicating if you want the spreadsheet to have an autofilter applied after the data is exported. 0 (False)
@include_headers Flag indicating if you want to include headers on the export to the spreadsheet as the first row 1 (True)

Example: This example exports data from the adventureworks database to a spreadsheet and bolds the headers. If the spreadsheet does not exist, EDIS will create it on the fly.

EXEC SSISDB.EDIS.usp_excel_export_data

 @file_path = 'C:\temp\sales.xlsx'

,@sheet_nm = 'data'

,@src_qry = 'select top 1000 * from adventureworks.person.person'

,@bold_headers = 1

 

Excel Add Sheet Task
This tasks adds a new worksheet to an existing workbook

Procedure: SSISDB.EDIS.usp_excel_add_worksheet

Parameters

Name Description Default Value
@file_path File path to the Excel Spreadsheet None; Required
@sheet_nm The name of the Excel sheet to add None; Required

Example: This example adds a new sheet called “data transformed” to an Excel workbook.

EXEC SSISDB.EDIS.usp_excel_add_worksheet

 @file_path = 'C:\temp\sales.xlsx'

,@sheet_nm = 'data transformed'

 

Excel Rename Sheet Task
This tasks renames a worksheet in an Excel workbook

Procedure: SSISDB.EDIS.usp_excel_rename_worksheet

Parameters

Name Description Default Value
@file_path File path to the Excel Spreadsheet None; Required
@sheet_nm The name of the existing Excel sheet None; Required
@new_sheet_nm The name you want to rename the existing sheet to None; Required

Example: This example renames worksheet “data” to “data_old”.

EXEC SSISDB.EDIS.usp_excel_rename_worksheet

 @file_path = 'C:\temp\sales.xlsx'

,@sheet_nm = 'data'

,@new_sheet_nm = 'data_old'

 

Excel Clear Sheet Task
This tasks deletes all rows in an existing spreadsheet

Procedure: SSISDB.EDIS.usp_excel_clear_worksheet

Parameters

Name Description Default Value
@file_path File path to the Excel Spreadsheet None; Required
@sheet_nm The name of the Excel sheet to clear None; Required

Example: This example clears all rows in sheet “data”.

EXEC SSISDB.EDIS.usp_excel_clear_worksheet

 @file_path = 'C:\temp\sales.xlsx'

,@sheet_nm = 'data'

 

Excel Sheet List Task
This task gets a list of all sheets in an excel workbook and loads them to a temp table. This is helpful when you need to import excel files that contain multiple sheets, of which the names can change over time.

Procedure: SSISDB.EDIS.usp_excel_get_sheet_list

Parameters

Name Description Default Value
@file_path File path to the Excel Spreadsheet None; Required
@output_tbl_nm The name of the global temporary table you want to output the list of sheets to i.e. ‘##myXLsheet_list’. If the global temporary table exists prior to running this task, EDIS will drop it and recreate it. None; Required

Example: This example loads all sheets in workbook Sales.xlsx to global temp table ##xl_sheet_list.

EXEC SSISDB.EDIS.usp_excel_get_sheet_list

 @file_path = 'C:\temp\sales.xlsx'

,@output_tbl_nm = '##xl_sheet_list'