Scripting Issue

[#1]

Hi All,

I have a list of filenames such as YYYY.MM Abc.xls, Xyz MM_YYYY.xlsx and many more.

I want the following things o be separated from the filename:

1. Date Format i.e. YYYY.MM for the first file, MM_YYYY for the second and so on.

2. Filename excluding the date format i.e. Abc for the first file and Xyz for the second one.

Is it possible? If yes, then how?

Thanks,

Asma

[anbu1984]

Load *,KeepChar(FileBaseName(),'0123456789._') As Date,PurgeChar(FileBaseName(),'0123456789._') As FileNm

From Excel.xlsx

[marcus_sommer]

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:

table:

Load

     *,

     left(filebasename(), 7) as [YYYY.MM],

     makedate(left(filebasename(), 4), mid(filebasename(6, 2), 1) as Date,

     keepchar(filebasename(), '0123456789_') as [MM_YYYY]

From x;

- Marcus

[#4]

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.

[anbu1984]

Do you have list of formats in which you will get filenames?

[#6]

Yes. The list is maintained in a separate column in the excel. The column has values as ABC MMMM YYYY.xls for a file and the corresponding filename is ABC December 2014.

Every month just the month name section of the filename would change.

[anbu1984]

For the files in format "ABC MMMM YYYY.xls", you can use this

SubField(FileBaseName(),' ',1) As FileNm

TextBetween(FileBaseName() & '$$',' ', '$$') As Date

[#8]

Does this mean that I have to apply different conditions based on the filename?

Can't we just write a single expression which takes care of separating the file name and date format part irrespective of the file name.

Thanks,

Asma

[anbu1984]

Yes you should use different conditions based on filename