Load and stack data from an excel file

[#1]

Hi,

I would like load data from several excel tables. All the tables have the same structure (fields) but each one is in one tag. I would like to stack all the rows from all the tables into only one table adding a field to distinguish from which original table tag were the row loaded.

To make it short, I don't know how to load from excel neither how to stack several excel tags in one.

Any clue please?

Thanks

[Gysbert_Wassenaar · Accepted solution]

See here. Post 2 and 8 are probably what you're looking for. If you need to loop through excel files in a directory tree, see here.

You can add $(vSheet) as SheetName to create a field to store the sheet name:

for vSheetNo = 1 to $(vMaxSheet)

     Data:

     LOAD *, $(vSheet) as SheetName

     FROM $(vFileName)

     (biff, embedded labels, header is 1 lines, table is $(vSheet));

next