Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
nigel987
Contributor 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

Re: Load files from folder with exceptions

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

7 Replies

Re: Load files from folder with exceptions

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
Contributor III

Re: Load files from folder with exceptions

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
Contributor II

Re: Load files from folder with exceptions

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;

Re: Load files from folder with exceptions

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
Contributor II

Re: Load files from folder with exceptions

I tested it and this works too!

Re: Load files from folder with exceptions

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
Contributor II

Re: Load files from folder with exceptions

Hi Kushal,

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

Community Browser