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

By Imran, Mohammad January 24, 2022 under BODS

You have some sheets in an excel file and you want them to be extracted in a table using single file format. BODS can help extract the data from all of the, you will need to create a file format under Excel Workbook. This post is going to help you extracting the data from multiple sheets with the help of while loop. For your knowledge, BODS reads columns of the excel by the its position not by its name. Name of the column does not matter as long as position in the file format vs excel sheet matches, you will be able to extract right data in your table.

Source File

In the example below, we have three sheets by the name Sheet1, Sheet2 and Sheet3.

Declare variables

$G_Last_Sheet is declared to take input during run time, in our case the last sheet is 3. $G_Sheet_No will hold a number which will be incremented on each loop, it will start from 1 as 1 is already initialized on global level. $G_Sheets will hold the name of the sheet.

Create a file format

Lets create a file format, the approach is same as normal file format. Under the Worksheet combobox, choose the variable which holds the sheet name.

Query Design

Lets design the query, we will need a while loop, under this a script to initialize the variables and a dataflow to read the data from excel, it will be executed multiple times by while loop.

Script initialization

$G_Sheets = 'Sheet'||$G_Sheet_No will store the name of the sheet based on value available in $G_Sheet_No. Second statement $G_Sheet_No = $G_Sheet_No + 1 will increment the value sheet number so that loop jumps to next loop after 1st loop is complete.

Design dataflow

Lets design dataflow, in the example below if you notice target table is left as temporary, please ensure you have executed it once so that structure of the table is created in database. Once structure is created ensure Drop and re-create table checkbox is unchecked otherwise you will not see data loaded from all the 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.