Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
MVP
MVP

Re: search inside folders for file load

maybe like this:

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

using a recursive call to DoDir

Re: search inside folders for file load

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

Re: search inside folders for file load

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

Re: search inside folders for file load

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

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

Re: search inside folders for file load

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

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

Re: search inside folders for file load

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

Re: search inside folders for file load

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)

Community Browser