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: 
Not applicable

search inside folders for file load

Hi all,

I need to load files from sub folders of sub folders.

I have the syntax for the regular search:

  1. for each SubDirectory in dirlist( Root & '\*'
  2.      if(substringcount('$(SubDirectory)' & '@', '$(vRunList)') then 
  3.           call ScanFolder(SubDirectory) 
  4.      end if 
  5. next SubDirectory


but the files aren't in the SubDirectory - they are in the sub directories of it (multiple directories).

in each sub-sub directory I have files with the same name.

I need to load all this files and concatenate them into one table.


can somebody help me with the script syntax?



Thanks


Boris 

14 Replies
swuehl
MVP
MVP

maybe like this:

http://community.qlik.com/thread/90031

using a recursive call to DoDir

Gysbert_Wassenaar

Try moving the if clause up a bit:

Set vConcatenate = ;

sub ScanFolder(Root)

    if(substringcount(Root & '@', '$(vRunList)') then

        for each FileExtension in 'csv'

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

                Set vConcatenate = Concatenate;

            next FoundFile

        next FileExtension

    end if

    for each SubDirectory in dirlist( Root & '\*' )

        call ScanFolder(SubDirectory)

    next SubDirectory

end sub


talk is cheap, supply exceeds demand
Not applicable
Author

when the if is outside the loop then the Sub directory is irrelevant because the whole purpose is to

scan the sub folders by their names the the vRunList.

This is an example for the directories:

d:/temp/#run_id/#part_id/table_name_X.csv

d:/temp/1/1/table_name_a.csv

d:/temp/1/1/table_name_b.csv

d:/temp/1/1/table_name_c.csv

d:/temp/1/2/table_name_a.csv

d:/temp/1/2/table_name_b.csv

d:/temp/1/2/table_name_c.csv

d:/temp/2/1/table_name_a.csv

d:/temp/2/1/table_name_b.csv

d:/temp/2/1/table_name_c.csv

d:/temp/2/2/table_name_a.csv

d:/temp/2/2/table_name_b.csv

d:/temp/2/2/table_name_c.csv

the vRunList is all the run_id's I need in this load.

the part_id I need all of them each load for each run_id.

also, for each table_name I need a different QVD( concatenated for all sub folders) because after I load all necessary files I concatenate each one to an existing QVD of this table.

Not applicable
Author

when the if is outside the loop then the Sub directory is irrelevant because the whole purpose is to

scan the sub folders by their names the the vRunList.

This is an example for the directories:

d:/temp/#run_id/#part_id/table_name_X.csv

d:/temp/1/1/table_name_a.csv

d:/temp/1/1/table_name_b.csv

d:/temp/1/1/table_name_c.csv

d:/temp/1/2/table_name_a.csv

d:/temp/1/2/table_name_b.csv

d:/temp/1/2/table_name_c.csv

d:/temp/2/1/table_name_a.csv

d:/temp/2/1/table_name_b.csv

d:/temp/2/1/table_name_c.csv

d:/temp/2/2/table_name_a.csv

d:/temp/2/2/table_name_b.csv

d:/temp/2/2/table_name_c.csv

the vRunList is all the run_id's I need in this load.

the part_id I need all of them each load for each run_id.

also, for each table_name I need a different QVD( concatenated for all sub folders) because after I load all necessary files I concatenate each one to an existing QVD of this table.

Gysbert_Wassenaar

Maybe you can parse the filename then:

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

   if( match(subfield(filename('$(FoundFile)'),'\',3) & '@', '$(vRunList)') then

        FileList: 

        $(vConcatenate) 

        LOAD *, '$(FoundFile)' as SourceFile 

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

        Set vConcatenate = Concatenate; 

  end if

next FoundFile


talk is cheap, supply exceeds demand
Not applicable
Author

Where in this script I have the loop on the #part_id?

I understood that the root is the d:\temp

I need for all Relevant run_id's (vRunList) to go to all part_id's and concatenate each table_name to it's matching table names in all other part_id's.

I having difficulty to understand in this script I know that it is the same table and when do I go through all part_id's.

Gysbert_Wassenaar

It doesn't loop on part_id or run_id. It will scan all sub directories and loop through all files. It checks each file name (which includes the file path) to see if the number after the second backslash is a number from your run list. If it does the file is loaded, if not it is skipped.

Set vConcatenate = ; 

sub ScanFolder(Root)      

    for each FileExtension in 'csv' 

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

            if( match(subfield(filename('$(FoundFile)'),'\',3) & '@', '$(vRunList)') then

                FileList: 

                $(vConcatenate) 

                LOAD *, '$(FoundFile)' as SourceFile 

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

                Set vConcatenate = Concatenate; 

            end if

        next FoundFile 

    next FileExtension     

    for each SubDirectory in dirlist( Root & '\*' ) 

        call ScanFolder(SubDirectory) 

    next SubDirectory 

end sub


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks,

one thing to clear up.

I need separate concatenated table for each table_name. (to concatenate it to existing qvd)

do I need to write this script for each different table_name?

Not applicable
Author

Assuming all your CSV files are of same format, you could use the below code. It will automatically concatenate the data to the table Table_Name.

Also please change the load statement accordingly (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

SET ZLoadDirectory = 'd:/temp/';

SUB LoadDirectory (ZLoadDirectory)

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

  Table_Name:

  Load

  *

  FROM [$(File)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

  NEXT File

  //This will make the code to go over all the subdirectories

  FOR each Dir in dirlist (ZLoadDirectory&'\*')

  CALL LoadDirectory(Dir)

  NEXT Dir

END SUB

// Load all the files in the directory

CALL LoadDirectory (ZLoadDirectory)