I 'd like to thank avinashelite for sharing the method to load the multiple excel files with the multiple sheets. You can find the article at the link below.
Loading Multiple Excel Sheets Dynamically along with file name and sheet name
Unfortunately, this method has some limitations. It only works when all files have the same number of non-blank sheets, and all sheets must have the same number of columns with identical names.
I’d like to share a method below that works effectively without these restrictions
1) Define some variables to automate process.
/* Change the below variable with actual folder path */
LET vExcelFilePath = '..\Files'; // Root folder path on which all the excel files are stored. It might contain sub folders as well.
LET vFileExtension = 'xlsx';
LET vQVDFilePath='..\QVD'; // Path on which a QVD is stored which contains data from all the excel files
LET vQVDName ='Sales_data'; // Name of a physically stored QVD File
2) Define function to get the list of all the files located inside the folder. It also scans sub folders inside the parent folder.
/* Below subroutine is created to get the lis of excel files stored in specific folder and sub folder within that */
sub ScanFolder(Root)
for each vFile in filelist( Root & '\*.' & vFileExtension)
FileList:
LOAD '$(vFile)' as Files
AutoGenerate 1;
next vFile
for each SubDirectory in dirlist( Root & '\*' )
call ScanFolder(SubDirectory)
next SubDirectory
end sub
Call ScanFolder('$(vExcelFilePath)') ;
let vFile = Null();
3) Define a function to loop through the list of Excel files generated by the previous function. Let’s take a look at the connection string below. I’ve used CONNECT64 because I’m working with a 64-bit ODBC driver and Excel application. If you’re using 32-bit ODBC drivers and Excel, you may need to use CONNECT32 instead. The 'Excel Files' DSN is created on the machine using either 64-bit or 32-bit ODBC drivers. Typically, when Office drivers are installed, the 'Excel Files' DSN is created automatically, but in some cases, it may need to be created manually. If your DSN name differs, be sure to update it in the connection string below."
ODBC CONNECT64 TO [Excel Files;DBQ=$(vFile)];
/* Below subroutine is created to load all the excel file and store it into the QVD. */
SUB load_all_excel_files_and_store_in_qvd(LoadData)
FOR EACH vFile IN FieldValueList('Files');
ODBC CONNECT64 TO [Excel Files;DBQ=$(vFile)];
Temp:
LOAD *;
SQLtables;
DISCONNECT;
[$(vTableName)]:
LOAD * INLINE [
junk ];
FOR i = 0 TO NOOFROWS('Temp')-1
LET vSheetName = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i, 'Temp'), CHR(39)), CHR(36));
Set ErrorMode=0; // Disable error mode to avoid error while loading blank sheet
CONCATENATE([$(vTableName)])
LOAD *,
SubField('$(vFile)','\',-1) AS FileName,
'$(vSheetName)' AS Sheet_name
FROM $(vFile)(ooxml, embedded labels, table is [$(vSheetName)]);
if len(FieldName(FieldNumber('A','Data'),'A'))>0 THEN
Drop Field A; // When there is a blank sheet in the excel file, field A is created which we don't want
ENDIF
Set ErrorMode=1;
NEXT
DROP TABLE Temp;
DROP FIELD junk;
NEXT
let vFile = NULL();
if NoOfRows('$(vTableName)')>0 THEN
STORE [$(vTableName)] into $(vQVDFilePath)\$(vQVDName).qvd(qvd); // store into the QVD file
ELSE
TRACE "Data is not available";
ENDIF
END SUB
CALL load_all_excel_files_and_store_in_qvd(LoadData);
DROP Table FileList;
Note: This Script is best suited for QlikView
Please feel free to offer any suggestions to improve this document.
Thanks & Regards,
Kushal Chawda
