Skip to Content

Data extraction from multiple sheets of excel work book

Business Requirement: I have a requirement where we will receive an excel workbook comprises of all cost center information (area wise) sheets in a single excel workbook. We should extract the data of all work sheets (CC1, CC2, and CC3) and load it in the target table.

We recommended customer to create one more sheet by name SHEET_NAMES in the same workbook and list all the sheet names as shown below.

  • During creation of CC_DATA_INP excel file format in the Designer , mention the Worksheet with Global variable $GSheetName, the sheet name will be assigned dynamically in the Start Script.


Design Steps

The below diagram shows the overall job


ExtractSheetNames_DF will collect all the sheet names from the work sheet SHEET_NAMES and stores it in Sheetnames DB table

In the below CC_DATA_EXTRACT_WF place while loop and put a condition that $GSheetName is not equals to null.

  1. In the StartScript, the first sheet name will be retrieved from Sheetnames table and will be assigned dynamically to the global variable.
  2. The CC_DATA_DF data flow will take the assigned sheet name from the global variable and extract the appropriate data

  3. In the EndScript, the assigned sheet name [from which the data is extracted in the step 2] will be deleted from Sheetnames table

While loop will execute the above 1,2,3 steps until the data extraction of all the sheets completed.

Start Script code

print(‘ Sheet Name is after delete in First Script’|| $GSheetName );

$GSheetName = sql(‘Practice’, ‘select top 1 SHEET_NAMES from Sheetnames’);

print(‘ Sheet Name is after Selecting top 1 in First Script’|| $GSheetName );

End Script code

print(‘ Sheet Name is ‘|| $GSheetName );

sql(‘Practice’, ‘delete from Sheetnames where SHEET_NAMES = {$GSheetName}’ );

print(‘ Sheet Name is after delete’|| $GSheetName );

$GSheetName = sql(‘Practice’, ‘select top 1 SHEET_NAMES from Sheetnames’);

You must be Logged on to comment or reply to a post.