topic Re: Load Filenames from Folder and Subfolders for specific filenames in QlikView

Load Filenames from Folder and Subfolders for specific filenames

Thu, 03 Mar 2016 19:48:27 GMT

Hello Qlikview Community,

I am trying to load filenames from Multiple folders and subfolders within each into a qlikview dashboard.

My file path is W:\Files\Sales\Data\UK\Archive

I need to load filenames from all market (UK) folders and all archive folders within each market. There are some files in these folders I am not interested in so I have specified that the file name is 'Sales_*.xlsx'

From another thread I have put the script below together. At the moment this script only loads filenames from the Market folders and does not load anything from the Archive folders within each Market Folder.

If someone could please explain how I can write this into the script it would be very helpful.

Thanks in advance,

sub ScanFolder(Root)
for each Market in DirList( 'W:\Files\Sales\Data' & '\*')
for each FoundFile in filelist('$(Market)' & '\Sales_*.xlsx')
FileList:
Load
subfield('$(FoundFile)','\',5) as Filename,
subfield(subfield('$(FoundFile)','\',5),'_',2) as Market,
subfield(subfield('$(FoundFile)','\',6,'_',3) as Unit
Autogenerate(1);
next FoundFile
next Market
end sub

Call ScanFolder('W:\Files\Sales\Data');

Re: Load Filenames from Folder and Subfolders for specific filenames

settu_periasamy — Fri, 04 Mar 2016 03:44:21 GMT

Hi Jake,

Check here.. Re: Loading Multiple Excel Files and Multiple Excel sheets‌

/*--------------------------------------------------------------------------------------------------*/
SUB LoadAllExcelSheets(vFile)
ODBC CONNECT TO [Excel Files;DBQ=$(vFile)];
tables:
SQLtables;
DISCONNECT;

LET vSheetCount = NoOfRows('tables');

FOR i = 0 to $(vSheetCount) -1
LET vSheetName = replace(purgeChar(purgeChar(peek('TABLE_NAME', $(i), 'tables'), chr(39)), chr(36)),'#','.');

[$(vSheetName)]:
LOAD *, '$(vSheetName)' as Sheet, '$(vFile)' as FileName
FROM [$(vFile)] (ooxml, embedded labels, table is [$(vSheetName)]);
NEXT i
DROP TABLE tables;
END SUB

/*--------------------------------------------------------------------------------------------------*/
SUB ScanFolder(Root)
FOR EACH FileExtension in 'xlsx'
FOR EACH FoundFile in filelist( Root & '\*.' & FileExtension)
CALL LoadAllExcelSheets('$(FoundFile)');
NEXT FoundFile
NEXT FileExtension
FOR EACH SubDirectory in dirlist( Root & '\*' )
CALL ScanFolder(SubDirectory)
NEXT SubDirectory
END SUB
CALL ScanFolder('my xl files') ; //Give your File Path

Re: Load Filenames from Folder and Subfolders for specific filenames

tamilarasu — Fri, 04 Mar 2016 04:36:16 GMT

You can also try,

Sub ScanFolder(Root)
          for each FileExtension in 'xlsx'
              for each FoundFile in filelist( Root & '\*.' & FileExtension)
                 FileList:
                 Load  Subfield('$(FoundFile)','\',5) as Filename,
                       Subfield(Subfield('$(FoundFile)','\',5),'_',2) as Market,
                       Subfield(Subfield('$(FoundFile)','\',6),'_',3) as Unit
                              Autogenerate(1);
                    next FoundFile
          next FileExtension
          for each SubDirectory in dirlist( Root & '\*' )
                    call ScanFolder(SubDirectory)
          next SubDirectory
End Sub

Call ScanFolder('C:\Users\Tamil\Desktop\Test') ;