Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

if excel file exists then load

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

6 Replies
tresesco
MVP
MVP

Using filesize() in a loop?

ThornOfCrowns
Specialist II
Specialist II

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

Not applicable
Author


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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

jptneumann
Partner Ambassador
Partner Ambassador

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

eniemenm
Partner - Contributor II
Partner - Contributor II

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.