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.
Design
I have used 1 WF & 2 DF, 2 script files (Start Script & EndScript) and 1 temporary table which stores sheet names information.
First Dataflow: Will collects all the sheet names from excel workbook and stores in the Sheetnames table.
2nd Dataflow in the workflow: Extracts the data from all the worksheets, maintaining work sheet names in the control table and assigning sheet names dynamically to global variable will be manipulated in StartScript and EndScript
The Job comprises of
The prerequisites are
Design Steps
The below diagram shows the overall job
JOB_DynamicExcelExtraction
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.
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 a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
8 | |
7 | |
6 | |
5 | |
4 | |
4 | |
4 | |
3 | |
3 |