Are each of the tabs identical, or is each one different? Is there a logical naming of the tabs - or is it ad-hoc? Are each of the tabs relatively simple (eg. titles on Row 1 and data below) or are there complexities?
These things will determine to quite an extent what the best solution will be.
One approach is to create an ODBC connection to the spreadsheet, and there is then a syntax will allows you to enumerate from 1 to the count of 'tables' in the ODBC connection. Another is to create a list of tabs and load that into a temporary table and then enumerate around this table.
This code does the later:
Temp_BudgetSheets:
LOAD
[Budget Sheet]
FROM $(vDataFolder)2012Budgets.xlsx
(ooxml, embedded labels, table is [Budget Sheets])
;
for vSheetNo = 0 to NoOfRows('Temp_BudgetSheets') -1
let vBudgetSheet = peek('Budget Sheet', vSheetNo, 'Temp_BudgetSheets');
Temp_BudgetValues:
LOAD
A as GLCode,
B as M1,
C as M2,
D as M3,
E as M4,
F as M5,
G as M6,
H as M7,
I as M8,
J as M9,
K as M10,
L as M11,
M as M12
FROM [$(vDataFolder)2012Budgets.xlsx]
(ooxml, no labels, header is 6 lines, table is [$(vBudgetSheet)]);
next
In the instance above a CROSS TABLE load was then required on the temporary table.
If each of the sheets do not have exactly the same columns you will need to be quite careful about how each of the tables join or concatenate.
