multiple files load from multiple folders

[#1]

Hi all,

I'm building an extract script for my app - the source Data is CSV files from a certain path in the system.

For each run of the system a new folder is created, in this folder there are 6-7 files, each representing a different table.

the folder name is the RUN_ID of this run.

the meta data of the run is an analytics table - in it I have all RUN_ID's and run dates.

I need to load files into existing QVDs. the high level algorithm for the load is known.

What I need is a way to know from the analytics table, which Runs do I need to load from the file system.

then, I need to loop through the root folder but only through the RUN_ID folders who are relevant for this load.

1) How is the save of a list of needed RUN_IDs is made? (the watermark should be date or run_id)

2) How is a loop through a root folder is made when we need to check the sub folders (the RUN_IDs) and stop when we finish with the list     we got from the first question.

Thanks,

Boris

[Gysbert_Wassenaar]

1. If you have a table with records for the RUN_IDs then you can create a variable with a list of those IDs.

Temp:
Load concat(RUN_ID & '@',',') as RunList
from analytics_table;
LET vRunList = peek('RunList');
Drop table Temp;

2. See this discussion: loop through to load all files from a folder and its subfolders?

In the loop you check if the folder name is in the list of RUN_IDs.

for each SubDirectory in dirlist( Root & '\*' )
     if(substringcount('$(SubDirectory)' & '@', '$(vRunList)') then
          call ScanFolder(SubDirectory)
     end if
next SubDirectory

[sundarakumar]

Hi Borris,

Sorry am not clear with your requirement.

I have attached an example that fetches files from different folders as they are listed in a master file.

Can u please explain your requirement?

-Sundar

[#4]

how does Scan folder works? where can I find documentation about it?