Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need to load files from sub folders of sub folders.
I have the syntax for the regular search:
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
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
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.
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.
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
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.
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
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?
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)