Looping through files applying stored variable

[#1]

Hi Folks,

I have little experience with Looping.  Your help will be much appreciated.  I need to loop through each excel document and each document has a different ForEx rate.  This script works well for one file but I need it to loop through the others capturing their individual ForEx rates.  Thanks in advance!!

ForEx:

LOAD

ForEx

FROM 'Data_CA.xls';

Let ForEx = num(peek('ForEx',-1,ForEx),'#.######');

Drop Table ForEx; 

Table:

Load

Column1,

Column2 * $(ForEx) as Column2

FROM 'Path\Data_CA.xls';  

But I need to loop through multiple excel files so the FROM would read 'Path\Data*.xls'   

[Colin-Albert]

Table:

Load

Column1,

Column2 * $(ForEx) as Column2

FROM 'Path\Data*.xls'

;  

This should load all spreadsheets, provided the sheet names, header rows and column names etc are the same in all your spreadsheets

[#3]

The columns are the same name.  But I need to loop through each file, capturing and applying their appropriate ForEx rates. 

[Anonymous]

As Colin suggested , load all files to the table and then use your calculation for the column

Table:

Load

Column1,

Column2 ,

ForEx

FROM 'Path\Data*.xls';

Table2:

Load

Column1,

Column2 * ForEx as Column2

Resident Table;

Drop Table Table;

[maxgro]

..........

set vPath='C:\Users\mgrossi\Downloads\Data*.xlsx';               // xlsx

table: load '' as dropme autogenerate 0;

For Each vFile in FileList('$(vPath)')

  concatenate(table)

          LOAD

              Column1,

              Column2 * $(ForEx) as Column2

          From [$(vFile)]

          (ooxml, embedded labels, table is Sheet1);               // change for xls

next;

drop field dropme;