How to read data from multiple excel sheets in SAP Data Service (BODS)

By Imran, Mohammad January 24, 2022 under BODS

If you have multiple sheets within an Excel file and want to extract their data into a table using a single file format, BODS can facilitate this process. You will need to create a file format under the Excel Workbook option. This post will guide you through extracting data from multiple sheets using a while loop. For your information, BODS reads Excel columns based on their position, not their header names. As long as the column positions in the file format align with those in the Excel sheets, you will be able to extract the correct data into your target table, regardless of the column names.

Source File

In the example below, the Excel file contains three sheets named Sheet1, Sheet2, and Sheet3.

Declare Variables

$G_Last_Sheet is declared to accept the sheet count as input at runtime—3 in this example. $G_Sheet_No holds the current sheet number and is incremented with each loop iteration; it starts at 1, as initialized at the global level. $G_Sheets stores the name of the current sheet being processed.

Create a File Format

Let's create a file format using the standard approach, similar to any other file format creation. In the Worksheet dropdown, select the variable that holds the sheet name—this will allow the format to dynamically read from different sheets.

Query Design

Let's design the query. A while loop will be used to control the process. Within the loop, include a script to initialize and update the necessary variables, and a dataflow to read data from the Excel file. This dataflow will be executed multiple times, once for each sheet, as driven by the loop.

Script Initialization

$G_Sheets = 'Sheet' || $G_Sheet_No assigns the sheet name dynamically based on the current value of $G_Sheet_No. The second statement, $G_Sheet_No = $G_Sheet_No + 1, increments the sheet number to prepare for the next iteration of the loop.

Design Dataflow

Let's design the dataflow. In the example below, note that the target table is set as temporary. Please ensure the dataflow has been executed at least once so that the table structure is created in the database. Once the structure is in place, make sure the Drop and re-create table checkbox is unchecked. Otherwise, data from previous sheets may be overwritten, and you will not see records loaded from all sheets.

Execute the job

Lets execute the job. During the execution on the Global Variable tab put a number on the variable ($G_Last_Sheet). In this case we have put 3 because we have 3 sheets.

Output

We have successfully extracted the data from all the sheets.

Conclusion

I have tried list down all the steps required to extract the data from multiple sheet of an excel sheet. This will help when you have N number of sheets in an excel sheets and you want to extract all of them using single file format. if you need more help on this or any other topic related to data migration, do connect with me.