Loop through subfolders and generate a output file for each

[surendra_masupa]

Hi folks,

I've a small requirement to loop through the files in a sub folder and generate a output file for each folder.

As shown above, I need to load all the files under Australia folder , generate a concatenated table and store it into a csv. And it should loop through India, UK, USA folders......

Pls can you suggest a way to achieve this?

Many Thanks in Advance!

[Clever_Anjos]

This should work

for Each dir in DirList('yourpathgoeshere')
  for Each file in FileList('$(dir)\*.csv') // or .txt
      tmp:
      LOAD *
      FROM
      [$(file)](txt, codepage is 1252, no labels, delimiter is ',', msq);
  next;
  store tmp into [$(dir).csv](txt);
  drop table tmp;
next

[hic]

You can use a For-Next loop, e.g.

For each vFileName in Filelist ('C:\Path\*.txt')

      Load *,

         '$(vFileName)' as FileName

      From [$(vFileName)];

Next vFileName

See more on Loops in the Script

HIC

[Kushal_Chawda]

what kind of files you have in folders?. qvd or excels?

[maxgro]

DIRECTORY;

FOR Each Dir in 'Australia', 'India', 'UK', 'USA'

    trace Dir=$(Dir);

    Concat='';

    FOR Each File in filelist ('$(Dir)\*.qvd')

          Trace File=$(File);

          Table_$(Dir):

          $(Concat)

          load * from [$(File)] (qvd);

          Concat=' concatenate ';

      NEXT File;

      STORE Table_$(Dir) into Table_$(Dir).csv (txt);

      DROP Table Table_$(Dir);

NEXT Dir;

edit: added the bold

[surendra_masupa]

Hi all,

I've to load excel (xlsx) files and I modified the codes sent with xlsx and it doesn't work.

Hi Clever,Henric, Maxximo

The script doesn't generate any table.

Thank for your quick response!

[Clever_Anjos]

Would you mind posting your script here?

[Kushal_Chawda]

Do you have single sheet in excel or multiple sheets?

[surendra_masupa]

for Each dir in DirList('C:\Data Uploads') 

  for Each file in FileList('$(dir)\*.xlsx')

      tmp: 

      LOAD * 

      FROM 

      [$(file)](ooxml, embedded labels, table is [Input file]); 

  next; 

  store tmp into [$(dir).csv](txt); 

  drop table tmp; 

next 

[Clever_Anjos]

for Each dir in DirList('C:\Data Uploads\*.*')  // Try this

  for Each file in FileList('$(dir)\*.xlsx')

      tmp:

      LOAD *

      FROM

      [$(file)](ooxml, embedded labels, table is [Input file]);

  next;

  store tmp into [$(dir).csv](txt);

  drop table tmp;

next