topic Re: search inside folders for file load in QlikView

search inside folders for file load

Tue, 24 Dec 2013 12:02:32 GMT

Hi all,

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

I have the syntax for the regular search:

for each SubDirectory in dirlist( Root & '\*' )
if(substringcount('$(SubDirectory)' & '@', '$(vRunList)') then
call ScanFolder(SubDirectory)
end if
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

swuehl — Tue, 24 Dec 2013 12:21:29 GMT

maybe like this:

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

using a recursive call to DoDir

Re: search inside folders for file load

Gysbert_Wassenaar — Tue, 24 Dec 2013 12:34:50 GMT

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

Tue, 24 Dec 2013 12:58:18 GMT

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

Tue, 24 Dec 2013 12:58:33 GMT

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 — Tue, 24 Dec 2013 13:11:33 GMT

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

Tue, 24 Dec 2013 14:28:24 GMT

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

Gysbert_Wassenaar — Tue, 24 Dec 2013 14:32:22 GMT

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

Re: search inside folders for file load

Tue, 24 Dec 2013 14:39:46 GMT

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?

Re: search inside folders for file load

Tue, 24 Dec 2013 14:43:11 GMT

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

Gysbert_Wassenaar — Tue, 24 Dec 2013 14:48:35 GMT

You could add a parameter to the sub function for the table name so it will take two arguments: a start directory and a name for the table that will be created.

Re: search inside folders for file load

Fri, 27 Dec 2013 03:16:47 GMT

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

Tue, 31 Dec 2013 11:50:40 GMT

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

Re: search inside folders for file load

Tue, 31 Dec 2013 12:34:10 GMT

but the file name should be the same as the parameter I will pass.

how can I check it during the loop?

Re: search inside folders for file load

Thu, 02 Jan 2014 09:50:00 GMT

filename doesn't work in regular script.