Here is an example:
Loading sheets.zip (20.0 KB)
It has 3 projects and 3 worksheets:
Before your read further please make sure you read these two tutorial articles:
Tutorial:Parameters
Tutorial:Iterations
Let’s start from the lowest level and will be moving up.
Level 3: Loading a sheet
This project just loads a sheet from a spreadsheet. It has one transformation (Import from Excel) and two parameters: {File name} and {Sheet name}. The name of the spreadsheet and sheet name to load are specified using these two parameters (respectively).
Now let’s go one level up:
Level 2: Match sheet name
This project has 1 parameter: {File name}. Note that wrapping braces are not part of the parameter name, I’m using them to denote a parameter.
The project does 3 things:
- Obtain a list of sheets in spreadsheet which name is specified by project parameter {File name}.
- Do matching with sheet names in the list and produce the name of the sheet that we need to load. In this example it’s always “Sheet1” but you can put whatever matching logic you need .
- Use iterations to call project “Level 3 …” and return the loaded data from it. Since we have only 1 sheet name to load, there will be only 1 iteration.
In a bit more details:
Loading sheet names from a spreadsheet (see the screenshot below): you can see that the file name is defined by a parameter.
Matching, and loading the matched sheet using iterations:
The “Iterate” transformation properties:
Level 1: Generate a list of worksheets, and iterate
Now the main project to run which is Level 1. This project has no parameters.
It generates a list of Excel files in a given folder, then for each of the files it runs the Level 2 project.
The result is sheets named “Sheet1” loaded from all the spreadsheets in the folder.
