Dynamically Loading Multiple Excel Files

    Hi Qlikers,

     

    Firstly, kudos to avinashelite for giving us the below method to load the all excel files with the multiple sheets.

     

    Loading Multiple Excel Sheets Dynamically along with file name and sheet name

     

    Using above method we can load the multiple excel files with multiple sheets even though all the sheets have different names.

     

    Above method  works only in below scenarios

    1)  When all the files having the same number of sheets.

    2)  When all the sheets of excel files having the same number of Columns with same name.

     

    But below method works even though you don't have same number of columns and sheets in excel and having blank sheet in excel.

     

    // Define the Path

    LET vExcelFilePath = 'D:\Test';
    LET vQVDFilePath='D:\Test';


    SUB CreateQVDFromAllExcelFiles(vPath)

    FOR EACH vFileExtension IN 'xlsx'

    FOR EACH vFile IN FILELIST(vPath & '\*.' & vFileExtension);

    ODBC CONNECT TO [Excel Files;DBQ=$(vFile)];

    Temp:
    LOAD *;
    SQLtables;
    DISCONNECT;

    Data:
    LOAD * INLINE [
    junk ]
    ;

    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 $(vFile)(ooxml, embedded labels, table is [$(vSheetName)]);

    NEXT i


    DROP TABLE Temp;
    DROP FIELD junk;

    NEXT vFile
    NEXT vFileExtension

    Set ErrorMode=0;
    Drop Field A;     // When there is blank sheet in excel file, field A is created
    Set ErrorMode=1;

    STORE Data into $(vQVDFilePath)\FullData.qvd;
    DROP Table Data;

    END SUB

    CALL CreateQVDFromAllExcelFiles('$(vExcelFilePath)');

    LET i = Null();

     

    Feel free to provide your suggestions

     

    Thanks & Regards,

    Kushal Chawda