loop all files from a folder and its sub folders and add new field using sub folder name?

[anuradhaa]

I have files in below folder strcture.

Main folder : C:\Users\Dell\Desktop\Stats\Data\Years

subfolders : 2016 , 2017 , 2015

each folder has n number of files : member****.csv

My qvw file is in the location C:\Users\Dell\Desktop\Stats

I want to load all the files and get new feild call year based on subfolder name when reloading data

i used below script, but it doesn't work.

what's trhe wrong with this script.

for each SubDirectory in dirlist(C:\Users\Dell\Desktop\Stats\Data\Years & '\*' )

   call ScanFolder(C:\Users\Dell\Desktop\Stats\Data\Years)

next SubDirectory

[EH_temp]:

LOAD @1 as  [Report By Member],

@2 as PT,

'$(SubDirectory)' as tempYear,

if(len(trim(TextBetween(@2, '#', ' ')))=0,trim(TextBetween(@2, '#', '')),trim(TextBetween(@2, '#', ' '))) as tId,

trim(TextBetween(@2, '@', ' ')) as productname,

@3 as Date,

@4 as  Hours

FROM

[$(SubDirectory)\Report By Member*.csv]

(txt, utf8, no labels, delimiter is ',', msq) where @1 <> 'Grand total' and @1 <> 'Report By Member' ;

Thanks

[MK_QSL]

You can get idea from below links..

Loading data from multiple xls-files from folder with subfolder

Loading Excel Files from multiple Folders

loop through to load all files from a folder and its subfolders?

Load excel files from multiple folders

Load multiple excel files from Multiple excel sheetsinto QV

[jagan]

Hi,

Check this link

Re: Create Filename list table from folders and subfolders

Loading data from multiple xls-files from folder with subfolder

Regards,

Jagan.

[anuradhaa]

Thank you all, using above link i was able to do my work

SUB DoDir (Root)

FOR each Dir in dirlist (Root&'\*')

CALL DoDir(Dir)

[EH_temp]:

LOAD @1 as  [Report By Member],

@2 as PT,

'$(Dir)' as tempYear,

   @3 as Date,

   @4 as  Hours

FROM [$(Dir)\*] (txt, utf8, no labels, delimiter is ',', msq) where @1 <> 'Grand total' and @1 <> 'Report By Member' ; //by leaving the part of naming the table it uses all tables of the file

NEXT Dir

END SUB

CALL DoDir('Data\Years')