While Loop with Variable

Assume that a budget is spread across multiple sheets. While we can read these individual sheets as individual data sources, we would rather consolidate them all together as a single data source containing budget for all years.

These are the steps to take in relation to the While Loop:

• Declare a variable outside the While Loop.
• Inside the While Loop, set the variable to the name of the current Excel sheet.
• Import all sheets into a temporary table.
• Import the temporary data, plus year information, to a single data source.
• Drop the temporary data.

First we need to declare a Variable that we will use to hold the names of the Excel sheets, one at a time.

When you configure this new task, we want to add a new variable called ‘CurrentSheet’. This will show up as @CurrentSheet, once added.

With the variable already declared, we will now insert a While task that will loop through all the sheets of the Excel files until there are no more sheets.

Inside the While loop we will use a number of scripts (corresponding to one line each, line 20-23, in the code) to eventually import the consolidated budget data.

Script Code:

WHILE [ExcelSheetsList].EOF = false
set@CurrentSheet = ExcelSheetsList.sheet
IMPORT [tmpBudgetData] = [ExcelBudgetData].{SELECT * FROM [@@CurrentSheet]}
IMPORT [BudgetData] = [ME].{SELECT *, @@CurrentSheet as YearName FROM [tmpBudgetData]}
DROP [tmpBudgetData]
MOVENEXT [ExcelSheetsList]
LOOP