Thanks for the quick reply!!
File names are getting separated properly.
Bu the files with filenames as MM.YYYY YTD_ABC Test_XY.xls, is storing MM.YYYY_ in Date Format and YTDABC TestXY in filename.
Also, some files are in the format ABC December 2014.xls. So in this case, the file name should be just ABC and File Date Format should be December 2014.
You could use string-functions like left/mid/right to extract date-informations from filebasename() and/or removed all unwanted chars with keepchar, see here various possibilities:
left(filebasename(), 7) as [YYYY.MM],
makedate(left(filebasename(), 4), mid(filebasename(6, 2), 1) as Date,
keepchar(filebasename(), '0123456789_') as [MM_YYYY]