topic Re: if excel file exists then load in QlikView

if excel file exists then load

Thu, 22 May 2014 08:56:14 GMT

Hello

I have some excel files i have to load from a folder they may be many of them differentiated by a date like

file-20140522.xlsx, file-20140521.xlsx....etc

i can load all of them using "file-*" in from statement of the load. I want to add a condition before the load that would load only if at least one file exists , else it doesn't load . this is to skip the load error if no file exists.

thanks for your help

Re: if excel file exists then load

tresB — Thu, 22 May 2014 08:58:28 GMT

Using filesize() in a loop?

Re: if excel file exists then load

ThornOfCrowns — Thu, 22 May 2014 09:13:15 GMT

Was going to suggest a different method, but tresesco gives a better solution.

Re: if excel file exists then load

Thu, 22 May 2014 12:04:50 GMT

Hello james

I was going going to recommend your answer too ... can you put it back on this discussion please.

Thank you very much for your help

Re: if excel file exists then load

Peter_Cammaert — Thu, 22 May 2014 12:31:42 GMT

filetime() can be used as well.

Note that both functions return NULL if the specified file doesn't exist. FileSize can return 0 if the file exists and is empty. So you can use something like

IF NOT IsNull(FileTime('yourfilespec.xlsx')) THEN

// increase a counter or set a flag or just do a load with a wildcard

END IF

Note that you don't need a loop at all for your situation. If you use a wildcard spec (e.g. 'file-*.xlsx') then QlikView will return a non-NULL value if there is at least one file that matches your specification. I can't seem to figure out which file will be chosen. It looks like the first one will do...

Re: if excel file exists then load

jptneumann — Fri, 18 Nov 2016 18:29:12 GMT

To me works very well...

LET vListfileexists=0;

for each vSpedFile in filelist ('file-*.xlsx')

LET vListfileexists=-1;

EXIT FOR when vListfileexists;

NEXT vSpedFile

IF vListfileexists THEN

LOAD ......

ENDIF

Re: if excel file exists then load

eniemenm — Mon, 09 Jan 2017 17:02:20 GMT

You could actually just use "For each vFile in filelist()" like this:

FOR EACH vFile IN FILELIST('myFolder\file-*.xlsx')

Load *
From
[$(vFile)]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

next vFile;

This is the same as

Load *
From
[myFolder\file-*.xlsx]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

but it doesn't crush even if there is zero files to load.