14 Replies Latest reply: Jan 2, 2014 4:50 AM by Boris Kacevich RSS

    search inside folders for file load

    Boris Kacevich

      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 

        • Re: search inside folders for file load
          Stefan Wühl

          maybe like this:

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

           

          using a recursive call to DoDir

          • Re: search inside folders for file load
            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
            
            
            
            
              • Re: search inside folders for file load
                Boris Kacevich

                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.

                  • Re: search inside folders for file load
                    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

                      • Re: search inside folders for file load
                        Boris Kacevich

                        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.

                  • Re: search inside folders for file load
                    Boris Kacevich

                    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.

                      • Re: search inside folders for file load
                        Sudheer Guttikonda

                        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)

                      • Re: search inside folders for file load
                        Arun Goel

                        Hi,

                        Try This:

                         

                         

                         

                        SUB DoDir (Root)

                         

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

                         

                        Raw_Data_New:

                        LOAD

                        *

                        FROM [$(File)]

                        (biff, no labels, table is Sheet1$)

                        ;

                         

                        NEXT File

                         

                         

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

                         

                        CALL DoDir(Dir)

                         

                        NEXT Dir

                         

                        END SUB

                         

                        CALL DoDir('C:\Users\AGoel\Desktop\Shared Apps\Voter List\ERoll')

                         

                        STORE Raw_Data_New into Raw_Data_New.qvd;

                        DROP table Raw_Data_New;

                          • Re: search inside folders for file load
                            Boris Kacevich

                            So, Just to understand.

                            This is my Path: d:/temp/#run_id/#part_id/table_name_X.csv

                             

                            root = d:/temp

                            my run_id is the Dir in Dir List, am I right?

                            and the part_id is transparent, it will load all files in the run_id directory?

                            Now,  one last issue, the table names in each part id are the same, I need to concatenate all the tables with same name. as I understand this script, it will create multiple tables, and because we have same named files it will

                            overwrite the tables, how can I overcome this?


                            Thanks a lot!!!


                            Boris