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

[#1]

Hi all,

This is my folder structure

MainFolder

       SubFolderr1

                      File1

                      File2

       SubFolder2

                      File3

                      File4

       SubFolder3

                      File5

                      File6

How to load all the files dynamically if i give only "MainFolder" Name.

Thanks in advance

Regards

Kumar

[Tyler_Waterfall]

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]

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')

[Tyler_Waterfall]

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]

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

[hic]

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]

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!

[Anonymous]

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]

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

[nlaughton]

I'm having a problem with the script not closing out/ending.  Below is my script. I get as far as the  picture below, it seems to have all the data, however the script never advances and the script execution progress just stalls out. 

Any idea what i am missing?

SET vConcatenate =;
SUB ScanFolder(ROOT)
FOR EACH FileExtension in 'xlsx'
FOR each FoundFile in FileList( ROOT & '\*.' & FileExtension )

TEMP_HEADER:

First 1
LOAD A
FROM [$(FoundFile)]
(ooxml, no labels, header is 1 lines, table is Summary)
WHERE(not IsNull(A));

HEADER:
First 1
LOAD
SubField("A",' (',1) AS SITE_NUMBER
RESIDENT TEMP_HEADER;
LET vSITENUMBER = Peek('SITE_NUMBER');

DROP TABLE HEADER;
DROP Table TEMP_HEADER;

TEMP_HEADER:

FIRST 1
LOAD A
FROM [$(FoundFile)]
(ooxml, no labels, table is Summary)
WHERE(not IsNull(A));

HEADER:
LOAD
SUBFIELD(SUBFIELD("A",'(',2),' ',1) AS BEGIN_DATE
, SUBFIELD(SUBFIELD("A",'Through ',2),' ',1) AS END_DATE

RESIDENT TEMP_HEADER;

LET vSTARTDATE = Peek('BEGIN_DATE');
LET vENDDATE = Peek('END_DATE');

DROP TABLE TEMP_HEADER;
DROP TABLE HEADER;

CMOP_DETAILS:
LOAD
$(vSITENUMBER) AS SITENUMBER
,Date($(vSTARTDATE),'M/D/YYYY') AS STARTDATE
,Date($(vENDDATE),'M/D/YYYY') AS ENDDATE
,CMOPID
,NDC
,VAPrintName
,UnitCost
,NumberRX
,QTY
,TotalCost
FROM [$(FoundFile)]
(ooxml, embedded labels, header is 5 lines, table is [$(vSITENUMBER)]);

SET vConcatenate = Concatenate;
NEXT FoundFile
Next FileExtension

for each SubDirectory in dirlist( Root & '\*' )
call ScanFolder(SubDirectory)
next SubDirectory
end sub

Call ScanFolder ('D:\cmop data1') ;

[UDave]

Thank you so much this is very helpful for me & works beautifully.