Do While Loop

[brunopaulo]

Hello community,

Im trying use do while loop to store in a single qvd the data from multiple excel files.

My excel files has similar names ( Store - April 2015, Store - May 2015, etc)

The excel file are saved in my computer and i dont need any connection to acess them.

I saw several articles but none fully respond to my question.

Thanks in advance

Best Regards

Bruno Paulo

[oscar_ortiz]

Using wild card load you don't need to loop through your tables.

Maybe something like this:

SET vXLSPath = 'YourXLSPathGoesHere';

SET vQVDPath = 'YourQVDPathGoesHere';

StoreData:

LOAD

*

From

[$(vXLSPath)Store*2015.xlsx]

(ooxml, embedded labels, table is SheetName);

OR

StoreData:

LOAD

*

From

[$(vXLSPath)Store*2015.xls]

(biff, embedded labels, table is SheetName$);

Store StoreData into $(vQVDPath)Your.Qvd (qvd);

Good Luck

Oscar

[brunopaulo]

I know i don't need loop but its a requirment.

[oscar_ortiz]

You may try something like this:

SET vQVDPath = 'YourQVDPathGoesHere';

Sub ReadExcel (Root)

       For Each File in filelist (Root&'\*.xlsx')

            YourTable:

            Load  * FROM [$(File)] (ooxml, embedded labels, table is YourSheet)

  ;

       Next File

End Sub

Call ReadExcel ('YourXLSPath')

Store YourTable into $(vQVDPath)Your.Qvd (qvd);

Good luck

Oscar

[maxgro]

Files:

first 1 load

     FileBaseName() as filebasename

FROM

[Store - *.xlsx]  (ooxml, no labels, table is SheetName);

let v = NoOfRows('Files');

let f = 1;

Do while f <= NoOfRows('Files')

     let filename = peek('filebasename', $(f)-1);

     // do something with the file (load, concatenate, etc.....)

     trace $(f) $(filename);

     Let f=f+1;

Loop