load excel files from different folders

[#1]

hello all.

I have monthly invoice tables in different folders. like this path.

\invoice\2010\

\invoice\2011\

Now i need to load last 3 months invoice tables from 2010 and load all tables from 2011.

How to do this. Any suggest?

Regards. Muncho

[#2]

Thanks for your fast replay.

And how about join them?

[vincent_ardiet]

Do you mean join or concatenate ?

If your excel files have the same structure (and it seems to be the case), all those LOAD statements will by concatenated.

Regards,

Vincent

[#4]

yepp it will concatenate. But it's not. Because my tables structure is not same. That's why im asking here. If it was same, tables must concatenate and no need to ask from here right.

[vincent_ardiet]

Try with this:

SET MonthNames='January;February;March;April;May;Jun;July;August;September;October;November;December';

set firstfile=1;

for i = -5 to 0

let filename = year(addmonths(today(),i)) & '\' & month(addmonths(today(),i)) & ' ' & year(addmonths(today(),i)) ;

if firstfile=1 then

load month(addmonths(today(),$(i))) as month, * from $(filename).csv (txt, codepage is 1252, no labels, delimiter is ';', msq);

set firstfile=0 ;

else

concatenate load month(addmonths(today(),$(i))) as month, * from $(filename).csv (txt, codepage is 1252, no labels, delimiter is ';', msq);

endif

next

Regards,

Vincent

[#6]

Really thank you Vincent.

[stantrolav]

There is a system varible named LongMonthNames. It have all full names of months. You can get month name by

date(today, 'MMMM')

and set you full month name by adding

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';