How to extract date,month,filename from excel file name

[bhavvibudagam]

Hi Experts,

can any one please help me on below requirement.

I have the file names with different number of characters like below.

The order is like filename>Year>Month>Day

I have to extract the filename,Year,Month,Day as separate fields.

Here filenames contains different number of characters.

Filenames:

Inconsistent 20180102

Economics 20180229

Earlier I have extracted the date,month from file name like below. But now i need to extract the file name as well here there no fixed characters in the filename. Please help me on this.

makedate(mid(FileName(),19,4),mid(FileName(),23,2),mid(FileName(),25,2)) as Date,

    Date(MonthStart(makedate(mid(FileName(),19,4),mid(FileName(),23,2),mid(FileName(),25,2))), 'MMM') as Month,

    mid(FileName(),19,4) as Year,

   mid(FileName(),25,2) as Day,

   Date(MonthStart(makedate(mid(FileName(),19,4),mid(FileName(),23,2),mid(FileName(),25,2))), 'MMM YYYY') as MonthName

Thanks in advance.

[tresesco]

May be like this:

Load

    Date,

    Month(Date) as Month,

    Year(Date) as Year;

Load

    Date(Date#(Right(FileBaseName(),8), 'YYYYMMDD')) as Date

From <>;

Edit: Used, FileBaseName() instead of filename().

[sunny_talwar]

Try this

LOAD Year(Date) as Year,

     Month(Date) as Month,

     MonthName(Date) as MonthYear,

     *;

LOAD Date(Date#(SubField(FileBaseName(), ' ', -1), 'YYYYMMDD')) as Date,

     ....

FROM ....;

[sunny_talwar]

I think FileName will include the File Extension also, like .qvd, so right might not work alone... will have to probably use Text between or Right and then outer left...

[tresesco]

You are absolutely correct. I went too lazy and just followed OP's filename samples. .

[bhavvibudagam]

Hi Sunny,

Files are .xlsx files.

I need to extract the file name also as a separate filed.

Yes I am looking for the solution with Text between .

Please help me on this.

Thanks in advance.

[tresesco · Accepted solution]

Try this:

Load

    *,

    Month(Date) as Month,

    Year(Date) as Year;

Load

    FileBaseName() as FileName,          // Without extension

    Filename() as FileNameFull,            // With extension

    Date(Date#(Right(FileBaseName(),8), 'YYYYMMDD')) as Date

From <>;

For more information you could check help here: File functions ‒ QlikView for related functions and resolve yourself (since, you have got the idea I believe)

[sunny_talwar]

Check out tresesco‌'s solution above