Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nigel987
Creator II
Creator II

Load files from folder with exceptions

Hi,

I want to read all files from a folder, except one file (named AdType.qvd).

First I used the following statement to make sure that loading all file works.

set pathQVDs = '..\QVDsTest\';

for each File in filelist(pathQVDs& '*.qvd')

  load * from [$(File)] (qvd);

next File;

ThenI tried to include an if condition so that all files except AdType.qvd are loaded.

set pathQVDs = '..\QVDsTest\';

for each File in filelist(pathQVDs& '*.qvd')

  if FileName('$(File)') <> 'AdType.qvd' then

  load * from [$(File)] (qvd);

    end if;

next File;

Still, ALL files are loaded (including AdType.qvd).

I tested

if FileSize(vFile) > 500000

to make sure that the if clause is correct. It worked. So I assume that I do not state the FileName correct. I tried several things (I used FileBaseName, FilePath) but couldn't manage to get the syntax right.

Any suggestions? Thanks!

Nigel

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

FileName() will only return a value during the loading of a file (e.g. when a LOAD statement has the file open). It doesn't take any parameters. See the QV Desktop Help article on FileName().

Since inside the loop, variable File will already contain the full path to the current file, you can easily check for the base file name using the SubField() function. Try this:

:

IF (SubField(File, '\', -1) <> 'AdType.qvd') THEN

:

Best,

Peter

View solution in original post

7 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

FileName() will only return a value during the loading of a file (e.g. when a LOAD statement has the file open). It doesn't take any parameters. See the QV Desktop Help article on FileName().

Since inside the loop, variable File will already contain the full path to the current file, you can easily check for the base file name using the SubField() function. Try this:

:

IF (SubField(File, '\', -1) <> 'AdType.qvd') THEN

:

Best,

Peter

florentina_doga
Partner - Creator III
Partner - Creator III

try this

set pathQVDs = '..\folder\';

for each File in filelist(pathQVDs& '*.qvd')

    let fis_compar=subfield(File,'\',-1);

  if '$(fis_compar)' <> 'aa2.qvd' then

  load *,'$(File)' as fis from [$(File)] (qvd);

    end if;

next File;

nigel987
Creator II
Creator II
Author

Thank you for the explanation Peter!

I used the following and it worked!

for each File in filelist(pathQVDs& '*.qvd')

  if (SubField(File, '\', -1) <> 'AdType.qvd') then

  load * from [$(File)] (qvd);

    end if;

next File;

tamilarasu
Champion
Champion

Try like below,

set pathQVDs = '..\QVDsTest\';

for each File in filelist(pathQVDs& '*.qvd')

  if Not Wildmatch('$(File)',  '*AdType.qvd') then

  load * from [$(File)] (qvd);

    end if;

next File;

nigel987
Creator II
Creator II
Author

I tested it and this works too!

Kushal_Chawda

try this

LET pathQVDs = '..\QVDsTest\';

for each vFile in filelist('$(pathQVDs)*.qvd')

Files:

subfield('$(vFile)','\',-1) as FileName

next vFile;


FileNames:

noconcatenate

LOAD FileName

Resident Files

where lower(FileName)<>'adtype.qvd';


drop table Files;


Data:

LOAD 1 as Junk

Autogenerate 1;


for i=1 to fieldvaluecount('FileName')


let vFileName = fieldvalue('FileName','$(i)');


concatenate(Data)

LOAD *

FROM $(pathQVDs) $(vFileName) (qvd);


NEXT i


drop field Junk;


Store Data into Path\Final.qvd;


nigel987
Creator II
Creator II
Author

Hi Kushal,

I already tried the solutions above and that worked fine for me. Thanks for the comment though!