Skip to Content
Author's profile photo Akhilesh Kiran

Excel Multi Sheet Extraction

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.

Open Whileloop ‘WL_EXT_SHEET’
Write if the global variable is not null then perform while loop as shown in the below.


‘S_LOAD_DATA1’ script same as ‘S_LOAD_DATA’


Open Dataflow ‘DF_EXT_SHEET’. Drag Excel workbook ‘EXT_SHEET’ to Dataflow workspace map to Query Transform and Query Transform to Target Table ‘MULTISHEET_EXT.dbo’.


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.

DEL_TAB_DATA Script as


Execute the Job again.

Output:

Akhileshkiran

Assigned tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mohammad Ansari
      Mohammad Ansari

      Good one, is there a possibility to automate reading the sheet names as well instead of manually entering and then reading? Consider database as SQL Server.

      Author's profile photo Akhilesh Kiran
      Akhilesh Kiran
      Blog Post Author

      Hi Mohd Shahanshah Ansari,

      I don't think so there is an option of extracting all the sheet names automatically .

      I will once check and update you.

       

      Regards,

      Akhileshkiran

      Author's profile photo Kousik Das
      Kousik Das

      Nice Document.

      Author's profile photo Former Member
      Former Member

      Hi ,

       

      Very usefull document.

       

      Thanks,

      Deepa

      Author's profile photo Former Member
      Former Member

      Nice Document. Very helpful.

       

      Thanks,

      Sriram.