topic Re: How to extract date,month,filename from excel file name in QlikView

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

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

tresB — Mon, 29 Jan 2018 13:16:09 GMT

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().

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

sunny_talwar — Mon, 29 Jan 2018 13:21:28 GMT

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 ....;

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

sunny_talwar — Mon, 29 Jan 2018 13:23:03 GMT

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...

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

tresB — Mon, 29 Jan 2018 13:26:42 GMT

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

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

bhavvibudagam — Mon, 29 Jan 2018 13:36:06 GMT

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.

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

tresB — Mon, 29 Jan 2018 13:40:32 GMT

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)

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

sunny_talwar — Mon, 29 Jan 2018 13:42:01 GMT

Check out tresesco‌'s solution above