topic Re: Right Function with File Name - Detect file extention in QlikView

Right Function with File Name - Detect file extention

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

I'm struggling detecting when a file is xlsx or xls.

For some reason the first line also selects the *.xlsx files so I thought I would fix it by adding a IF ELSE statement.

But my IF statement below is always FALSE.

It probably have something to do with my use of the right() function

Thanks for this forum - Really learning a lot!

QlikView Script Code:

let path_Alles = 'C:\Users\xxx\Desktop\xxx\*.xls';


for each File in filelist (path_Alles)


SET sFile = '$(File)';


IF Right($(sFile),4)="xlsx" THEN


FindHeaderTemp:
LOAD RecNo() as DataStart
FROM $(File)
(ooxml, no labels)
WHERE A = 'Earning Year';


ELSE
FindHeaderTemp:
LOAD RecNo() as DataStart
FROM $(File)
(biff, no labels)
WHERE @1 = 'Earning Year';


EndIf

Re: Right Function with File Name - Detect file extention

sunny_talwar — Wed, 08 Mar 2017 16:41:16 GMT

May be try this:

IF Right('$(sFile)', 4) = 'xlsx' then

IF SubField('$(sFile)', '.', -1) = 'xlsx' then

Re: Right Function with File Name - Detect file extention

pjn123pjn123 — Thu, 09 Mar 2017 07:15:11 GMT

Thanks Sunny - Worked perfectly. Will need to restudy my understanding of the ' & ".

Thanks for the quick help!