Loading from multiple files - one for each month/year

[mhassinger]

I'm not sure how to go about this the right way.

I will need to load from a variable number of files that are named something like "QV Data - Jan 2012.xls." There are no fields in the files for the month and year (it's just in the file name), so I will need to be create new fields during load and assign the proper values. Something like (but obviously not static like this!):

LOAD

   'Jan' as Month,

   '2012' As Year,

    X,

    Y,

    Z

FROM [QV Data - Jan 2012.xls];

If it was just looping through files with the same data structure I'd be ok, but I'm not sure how to correctly create the month and year fields - parsing the filename seems like it could be messy. I'm not confident they wouldn't change the filename structure in some way (but they will always have the 'Month Year' convention at the end).

Any ideas?

[jemancilla · Accepted solution]

 LOAD
      subfield(Subfield(FileBaseName(),' - ',-1),' ',1)  AS Month
     ,subfield(Subfield(FileBaseName(),' - ',-1),' ',2)  AS Year
     ,Date(Date#(Subfield(FileBaseName(),' - ',-1),'MMM YYYY'))  AS Date
     ,X
     ,Y
     ,Z
FROM
     [QV Data*.xls]
     (biff, embedded labels, table is Hoja1$);