
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
loop all files from a folder and its sub folders and add new field using sub folder name?
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')
