Multiple Subfolder Scan - Excel Files

[wcilliers]

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

[zhadrakas]

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)')

[wcilliers]

Thanks Tim,

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

Thanks,

Wynand

[zhadrakas · Accepted solution]

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)')

[tamilarasu]

Hi Wynand,

Try this,

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 

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;

[wcilliers]

Thanks Tim! Much Appreciated!