topic Re: Multiple Subfolder Scan - Excel Files in QlikView

Multiple Subfolder Scan - Excel Files

wcilliers — Tue, 04 Apr 2017 08:44:07 GMT

Hi Experts,

I have been trying to load multiple Excel files from more than one subfolder with no success. I have read pretty much all the posts regarding this topic, but I am not having any luck...

I found this script below with a qvw (attached) which works great but only if the files are in the main directory. As I understand, it should scan through ALL subfolders and load all xls files that is found...

Sub ScanFolder(Root)

For each FileExtension in 'xlsx'

For each FoundFile in filelist( Root & '\*.' & FileExtension)

ODBC CONNECT32 TO [Excel Files;DBQ=$(FoundFile)];
Temp:
LOAD *;
SQLtables;
DISCONNECT;

Data:
Load '' as Temp AutoGenerate 0;

FOR i = 0 TO NoOfRows('Temp')-1

LET vSheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', i, 'Temp'), Chr(39)), Chr(36));

Concatenate(Data)
LOAD *,
FileBaseName() AS FileName,
'$(vSheetName)' AS Sheet_name
FROM [$(FoundFile)]
(ooxml, embedded labels, table is [$(vSheetName)]);

NEXT i

Drop Table Temp;

Next FoundFile
Next FileExtension

end sub

Call ScanFolder('C:\Users\wynan\Desktop\Test_2') ; // Folder Path

Drop Field Temp;

Please can you help me with this, I am sure that I am missing something small...

Thanks,

Wynand

Re: Multiple Subfolder Scan - Excel Files

zhadrakas — Tue, 04 Apr 2017 08:58:47 GMT

I'm using this code to process subdirectories

SUB doDir (dir)
FOR EACH file in filelist('$(dir)' & '\*.xlsx') ;
//Your Code you want to execute in loop

//ALL_XLSX:

//Load * FROM ‘$(file)’ (ooxml, no labels, table is [Tabelle1]);
NEXT file

// Process subdirectories
FOR EACH subdir in dirlist( '$(dir)' & '\*' )
CALL doDir('$(subdir)')
NEXT subdir

SET file=;
SET subdir=;
END SUB

SUB doRoot (root)
FOR EACH subdir in dirlist( '$(root)' )
CALL doDir('$(subdir)')
NEXT subdir
END SUB

LET vFolder = 'C:\Temp\Test';
Call doRoot('$(vFolder)')

Re: Multiple Subfolder Scan - Excel Files

wcilliers — Tue, 04 Apr 2017 09:09:59 GMT

Thanks Tim,

However, I need to incorporate the sheetnames into the load. I am struggling to combine the two, any suggestions perhaps?

Thanks,

Wynand

Re: Multiple Subfolder Scan - Excel Files

zhadrakas — Tue, 04 Apr 2017 09:22:12 GMT

SUB doDir (dir)
FOR EACH file in filelist('$(dir)' & '\*.xlsx') ;
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
SheetNames:
SQLtables;
FOR i = 0 to NoOfRows(‘SheetNames’)-1
LET vSheetName = purgeChar(peek(‘TABLE_NAME’, i, ‘SheetNames’), chr(39));
ALL_XLSX:
LOAD * FROM '$(file)' (ooxml, embedded labels, table is [$(vSheetName)]);
NEXT
drop table SheetNames;
NEXT file
// Process subdirectories
FOR EACH subdir in dirlist( '$(dir)' & '\*' )
CALL doDir('$(subdir)')
NEXT subdir

SET file=;
SET subdir=;
END SUB

SUB doRoot (root)
FOR EACH subdir in dirlist( '$(root)' )
CALL doDir('$(subdir)')
NEXT subdir
END SUB

LET vFolder = 'C:\Temp\Test';
Call doRoot('$(vFolder)')

Re: Multiple Subfolder Scan - Excel Files

tamilarasu — Tue, 04 Apr 2017 09:47:59 GMT

Hi Wynand,

Try this,

for each SubDirectory in dirlist( Root & '\*' )
call ScanFolder(SubDirectory)
next SubDirectory

end sub

Call ScanFolder('C:\Users\wynan\Desktop\Test_2') ; // Folder Path

Drop Field Temp;

Re: Multiple Subfolder Scan - Excel Files

wcilliers — Tue, 04 Apr 2017 09:51:03 GMT

Thanks Tim! Much Appreciated!