Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Using filesize() in a loop?
Was going to suggest a different method, but tresesco gives a better solution.
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
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...
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
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.