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

[vincent_ardiet]

Hi,

Can you give us the pattern used to named your files ?

And what is the rule ? Always full current year and 3 last months of past year ?

Regards,

Vincent

[#3]

Hello Muncho,
you can use a for-each loop, something like this:

for each i in 2010, 2011
set yr=$(i);
invoiceTable:
load /*columns: */ @1,
'$(yr)' AS TheYear
from [\invoice\$(yr)\invoice*.xls]
(biff, no labels, table is Sheet1$);
next ;

Best regards

[#4]

Hi Dear,

Load *,

Date(MonthStart(Date,3),'MMM-YYYY') as MonthYear

from table;

join

load
*,

Date(MonthStart(Date,3),'MMM-YYYY') as MonthYear

from table where MonthYear>'Sep-2010';

hope this is useful..

Regards

[#5]

Hi Vincent.

Invoice file names pattern is like: [monthfullname year.xls]

I need last 6 months invoices. So 3 months from current year, 3 months from last year.

[#6]

Hello Agger.

Thanks for this code. But i need last 6 months invoices. How do i do in your code?

[vincent_ardiet · Accepted solution]

Hi Muncho,

You can use a code like this one :

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

for i = -5 to 0

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

load @1,@2 from $(filename).csv (txt, codepage is 1252, no labels, delimiter is ';', msq);

next

If you don't want the current month just put -6 and -1 if the for range.

Regards,

Vincent

[#8]

Hi Vincent

thank you very much. Your the best 😄

[#9]

Hello Vincent

I need to ask you one more thing. Now i need to join or concatenate them. Also make month field from month in file name .

How to do this?

[vincent_ardiet]

Hi,

Juste add a new field in the load statement like this one :

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

Regards,

Vincent