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]
Was this article helpful?
0 out of 0 found this helpful



Please sign in to leave a comment.