Sure, no prob.
To be able to loop on the different sheets, then we have to know all of their labels.
This can be done by reading the excel document with ODBC and then calling SQLTABLES.
The connection requires to specify the path of the excel document which I'm putting in variable vTargetPath (used also in the load statement).
You can add an "exit script" after the disconnect, reload and check the tableviewer (ctrl+t) to actually see the content of the created table.
Now that we have all the labels in a table, and since our sheets all have same structure, we only have to write a load statement once within a for..next and provide the sheet label in a variable instead of it being hardcoded, and the data will be concatenated automatically.
So in each iteration i, vSheet will contain the sheet label in the correct format (without the $, that's what the PurgeChar function does, it removes the characters that we specify, here it's the $).
To read the sheet label from the table, I use the peek function for which you provide the field name (TABLE_NAME), the index (i, so 0 is the first row, 1 the second, and so on) and the table from which we're reading (sheets that we loaded previously).
Now in this case, we also have to create a date from the sheet label that is formatted as MMMYY.
So I use the Date# function to provide the input format then the Date function with the needed output format (omit the output format and it takes it from the environment variable DateFormat specified at the very start of the script).
Actually it'll directly be treated as a start of month, so we can omit the MonthStart function in this case.
The Floor function removes the time part in a date (like DayStart function) and stores the value as a number (for example 41832 for July 12, 2014).
I personally always use it for storing dates as their format can be adjusted later on the UI and this saves from handling formats within expressions.
But you can omit this too if you want the values to appear as dates directly, in which case you just need to:
- redefine vDate as: let vDate = Date(Date#('$(vSheet)', 'MMMYY'));
- surround the $(vDate) in the load with single quotes like this '$(vDate)'.
The Load part is normal, in which we put our fields created from vDate (year, month, date) and the ones from excel (*).
vSheet and vDate are adjusted dynamically on each iteration.
Hope this clarified the steps.
If you feel something's still not clear and you need further detail, let me know.
