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

    Hi All,

     

    This document helps you in loading multiple excels and excel sheets with the name of the sheet and data.

     

    //to read each file from the specified folder

    FOR EACH file in FileList('filepath\*.xlsx');

    //In order to get the file information from SQLtables command making use of the ODBC connection format
    ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];


    tables:
    SQLtables;
    DISCONNECT;

    FOR i = 0 to NoOfRows('tables')-1
    LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
    Table:
    Load * ,
    FileBaseName()as FIle,
    FileDir() as Dir,
    FileName() as File_Name,
    '$(sheetName)'
    as Sheet_name
    From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

    NEXT i

    Next


    Hope this helps !!!


    please find the attachment for the eg: qvw and test fiels


    Regards,

    avinashelite