Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Employee
Employee

Re: loop through to load all files from a folder and its subfolders?

Rungano - I'm not sure I fully understand your question, but if you have instances where you do not want to execute the subroutine if it finds a specific folder name or filename, then yes, you can use standard if statements and Exit Sub; if needed.

Consider something like -- I want to load all the files within the subfolders of the PARENT folder except subfolder "jirafa". You can check the folder name before you call the next subroutine.

If substringcount(Root,'jirafa')>0 then

TRACE I am skipping this folder;

// Or

Exit Sub;

Else

Call SubFolder(....)

EndIF

ngrunoz
New Contributor II

Re: loop through to load all files from a folder and its subfolders?

Hi Tyler.

That is correct. But I have this instance where I have many different Subs. Therefore on testing my script I would like to execute other Subs and Skipping others. Therefore I would like.to set Variable LoadFolder = 'Y'. So that the different Subs I want to execute and Load Data I will Set the parameter to Y ten they will b executed and when I Change it then it will skip it

Set LoadFolder = 'Y';

If $(LoadFolder) = 'Y' Then

SUB DoDir(Root

FOR each File in filelist( Root & '\*.csv')

TableA:

 
LOAD

'$(File)'
as SourceFile,
FileName() as FileName,
*
FROM
[$(File)]
(txt, codepage is 1252, no labels, delimiter is ',');



NEXT File 

FOR each Dir in Dirlist (Root&'\*') 
CALL DoDir(Dir
NEXT Dir 

END SUB

CALL DoDir ('..\Source Data')

Highlighted
Employee
Employee

Re: loop through to load all files from a folder and its subfolders?

I would just put the "CALL <subroutine>" in the If clause, rather than putting the whole subroutine in the If statement.

SUB DoDir(Root

FOR each File in filelist( Root & '\*.csv')

TableA:

 
LOAD

'$(File)'
as SourceFile,
FileName() as FileName,
*
FROM
[$(File)]
(txt, codepage is 1252, no labels, delimiter is ',');



NEXT File 

FOR each Dir in Dirlist (Root&'\*') 
CALL DoDir(Dir
NEXT Dir 

END SUB


Set LoadFolder = 'Y';

If $(LoadFolder) = 'Y' Then

     CALL DoDir ('..\Source Data')

Endif

mgarcmun
New Contributor II

Re: loop through to load all files from a folder and its subfolders?

Hi hic‌,

I'm trying to load some QVD'S from my folder, but I want to know how I could Load my QVD'S with 'WHERE EXISTS(field)' condition when I concatenate the QVD's:

I have this:

Set vG.Path.QVD.Layer1= 'MyFolder'; //the folder

Set vConcatenate = ;

Set vL.Tabla= 'Table'; // The name of all my QVD's begins with 'Table'. Ex: Table_1; Table_2; etc...


for each File in FileList('$(vG.Path.QVD.Layer1)\M_$(vL.Tabla)_*.qvd')


M_$(vL.Tabla):

  $(vConcatenate)


  LOAD *, FileBaseName() as _FileName


  FROM [$(File)] (qvd);


  Set vConcatenate = Concatenate;


NEXT File;


I want something like this but doesn't work:

Set vG.Path.QVD.Layer1= 'MyFolder'; //the folder

Set vL.Tabla= 'Table'; // The name of all my QVD's begins with 'Table'. Ex: Table_1; Table_2; etc...

Set vConcatenate  =  ;

Set vWhere    =  ; //Controls if you want to concatenate with condition when the value is null or not

Set vNOT    =  ;

Set vL.FieldConcat =  ;


for each File in FileList('$(vG.Path.QVD.Layer1)\M_$(vL.Tabla)*.qvd')


M_$(vL.Tabla):


  IF isNULL(vWhere) then

      $(vConcatenate)

    LOAD *,

      FileBaseName() as _FileName

    FROM [$(File)] (qvd);


  ELSE

      $(vConcatenate)

      LOAD *,

      FileBaseName() as _FileName

      FROM [$(File)] (qvd)

      WHERE $(vNOT) EXISTS($(vL.FieldConcat));

  END IF;


  Set vConcatenate = Concatenate;


NEXT File;


Could you help me?


Thank you! T

Re: loop through to load all files from a folder and its subfolders?

I suggest you open a separate thread with this question. You will get more answers then. Tag me in it, and I will try to answer.


Further, you need to explain how you want to assign values to the variables vWhere, vNOT and vL.FieldConcat. E.g. on what information would you base the decision to assign 'NOT' or an empty string to vNOT? And where would you find this information?


HIC

mgarcmun
New Contributor II

Re: loop through to load all files from a folder and its subfolders?

Hi HIC,

Sorry for the delay, my Question was how could load some QVD'S from the same folder, the developer will assign value to the variables if the developer wants to concatenate or not the information in QVD'S and if they want to load with the condition WHERE EXISTS() or WHERE NOT EXISTS().

vNOT only has the value 'NOT' or null;

vWhere any value that isn't null;

vL.FieldConcat the fieldname that we want concatenate with the condition EXISTS();

I found the solution changing the order of table name with IF/ELSE conditions:

for each File in FileList('$(vG.Path.QVD.Layer1)\M_$(vL.Tabla)*.qvd')


  IF isNULL(vWhere) then

      M_$(vL.Tabla):

      $(vConcatenate)

    LOAD *,

      FileBaseName() as _FileName

    FROM [$(File)] (qvd);


  ELSE

     M_$(vL.Tabla):

     $(vConcatenate)

      LOAD *,

      FileBaseName() as _FileName

      FROM [$(File)] (qvd)

      WHERE $(vNOT) EXISTS($(vL.FieldConcat));

  END IF;


  Set vConcatenate = Concatenate;


NEXT File;


Thank you very much!

T!

a_lopesneto
New Contributor

Re: loop through to load all files from a folder and its subfolders?

HIC,

Thank you so much for your teachings... this script helped me a lot.

However, my table "FileList" is getting too big. Instead of loading everything in my CSV files, I tried to load only some fields (columns), but it seems that it doens't work inside the Sub.

"for each FoundFile in filelist( Root & '\*.' & FileExtension)

                              FileList:

                              $(vConcatenate)

                              LOAD *, '$(FoundFile)' as SourceFile

                              FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);"

So, as I said, I tried to load just some specific fields in the LOAD command:

Example:

LOAD

     [Admitidos/Desligados],

     [Competência Declarada],

     Município as cod_mun,

     [Salário Mensal]

But it does not work, it just work if I use the *. Is there a way to do that?

Thanks in advance.

Regards,

Acacio

prees959
Contributor II

Re: loop through to load all files from a folder and its subfolders?

Hi Henric,

Im using this to loop through all subdirectories which is working fine....  However, I need to remove the first 10 characters from each field name during the load - can this be added to your solution?

Regards,

Phil