if excel file exists then load

[#1]

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

[tresesco]

Using filesize() in a loop?

[ThornOfCrowns]

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

[#4]

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]

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]

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]

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.