Hi Today we will see how to extract data from multiple excel sheets in Excel Work Book using SAP Data Services.
Open source excel sheet. Insert one new excel worksheet name it as ‘SHEET_LIST’ and fill all the worksheet names by ‘SHEET_NAMES’.
Note: All the worksheets must have same schema.
Login to SAP Business Objects Data Services Designer.
Create a new Excel workbooks file format for your source Excel sheet ‘SHEET_LIST’.
Create Project ‘PROJ_MULTI_SHEET_EXTRACTION’ in Project Area
Create Batch Job ‘JOB_MULTI_SHEET’
Create Global Variable ‘$G_LIST_SHEET’ as show below
Creating another new Excel workbooks file format for your source Excel sheet ‘EXT_SHEET’.
After Importing the schema using worksheet as shown below.
Assign Global Variable ‘$G_LIST_SHEET’ to worksheet and click on OK Button.
Create two Workflows ‘ WF_SHEET_LIST’ and ‘ WF_EXT_SHEETS’
Open ‘WF_SHEET_LIST’ Workflow and Add new Dataflow ‘DF_SHEET_LIST’
Open Dataflow Map ‘SHEET_LIST’ Source to Target Table using Query Transform.
Open WF_EXT_SHEETS. Add new script ‘S_LOAD_DATA’ to load sheet names from ‘SHEET_LIST.dbo’ and print the sheet name in log.
Map ‘S_LOAD_SCRIPT’ to Whlieloop ‘WL_EXT_SHEET’.
Open Whileloop ‘WL_EXT_SHEET’
Write if the global variable is not null then perform while loop as shown in the below.
In Loop workspace Add new Dataflow ‘DF_EXT_SHEET’.
Create two scripts ‘S_DEL_DATA’ and ‘S_LOCAL_DATA’
‘S_DEL_DATA’ to delete data from .
‘S_LOAD_DATA1’ to load again data from ‘SHEET_LIST.dbo’.
Delete script ‘S_DEL_SCRIPT’ as follows.
‘S_LOAD_DATA1’ script same as ‘S_LOAD_DATA’
First Run the Job. You will not see all the data because in target table options by default drop and recreate the table option is enable so you can see only one sheet data.
Then go to your target table and make it as import table. Double click on target table and in options
Uncheck Delete data from table before loading.
Click on job and Add a delete script ‘DEL_TAB_DATA’ before two Workflows which you created earlier as shown below. This script is to delete target table data before loading the data from source.
DEL_TAB_DATA Script as
Execute the Job again.