Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a LOAD script like:
Concatenate(Sales)
SELECT
aa,
bb
FROM $(vMyPath)file1.xlsx (ooxml, embedded labels, table is INPUT)
;
Concatenate(Sales)
SELECT
aa,
bb
FROM $(vMyPath)file2.xlsx (ooxml, embedded labels, table is INPUT)
;
I.e. 2 blocks of code where only the names of the excel source files differ. (How) Can I reuse the main part of the code to shorten the script and do something like:
Sub MySub(MyExcelFile)
Concatenate(Sales)
SELECT
aa,
bb
FROM $(vMyPath)MyExcelFile (ooxml, embedded labels, table is INPUT)
;
end sub
call MySub(file1.xlsx)
call MySub(file2.xlsx)
In general you could outsource such things into sub-routines. Depending on the number of parameter you specify and/or how expensive check- and/or error-logics are included you could build logics which could be used very often and in many scenarios. But the more you includes the more complex becomes it. Therefore be careful with such approaches to not to get the opposite from making things easier.
In regard to your example you might be able to use wildcards within the filename, like:
load * from path\*.xlsx (ooxml, …);
or using it within a filelist-loop, like:
for each file in filelist('path\*.xlsx')
load * from [$(file)] (ooxml, …);
next
- Marcus
I solved this one myself. The correct syntax is (everything else as is):
FROM $(vMyPath)$(MyExcelFile) (ooxml, embedded labels, table is INPUT)
...
call MySub('file1.xlsx')
call MySub('file2.xlsx')