Code block reusability in LOAD script

[RobinB1]

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)

[marcus_sommer]

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

[RobinB1]

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')