Making Month/Year Date From Source File Name

[#1]

Hi There,

I have a number of source files and they are labeled 01-13_MUV.xlsx.

The 01 is the month and 13 is the year.

I want to extract this month year date to create a listbox filter of month year within my app.

I am wondering if this is possible, or will have to just add the month/year column into the actual source file.

I would greatly appriciate any help I can get on this.

Thanks

[Anonymous · Accepted solution]

You can loop through the files, assigning the filenames to a variable, which you would then need to use in the load statement and can also strip apart to use for making fields. Something like this:

SET Dir = 'C:\ExcelFilesDir';

FOR EACH File IN FileList($(Dir)&'*.xlsx')

   SET vExcelFileMonth = Left($(File),2);

   SET vExcelFileYear = Mid($(File),4,2);

  TableName:

  LOAD

     *,

     MakeDate($(vExcelFileYear),$(vExcelFileMonth)) AS ExcelFileDate

  FROM [$(File)] (biff, embedded labels, table is [Sheet1$]);

NEXT

Jonathan

[richard_pearce6]

Are you loading the tables with a wildcard or are you using a loop?

I'd use a loop and create the filename using variables, checking the file exists and loading each one. From there it's pretty straight forward to add the field into the table using the same variables you used to load it.

[#4]

Im loading the files using *.

I just clicked into the table files and brought in the source file, and the changed the 01-13 to *,

so it looks like this;

From

[MUV analysis\ *.xlsx]

Where when I load one source file at a time it looks like this

From

[MUV analysis\01-13.xlsx].

I need to somehow extract the 01-13 and make it into a MonthYear field in the app, but im not sure can that be done when im using * to load in all the files?

Note: Each source file has the exact same layout and columns so that * can work.

[jonathandienst]

Hi

This should do the trick, whether you are loading in a loop or using a wildcard load as below:

LOAD ....

     Date(Date#(Left(FileBaseName(), 5), 'MM-YY')) As FileDate,

     ....

     other fields

FROM *_MUV.xlsx

Hope that helps

Jonathan

[#6]

Thanks a million, that worked

Really appriciate it.