Use OLEDB to get Excel sheet names

    Having in the past used the ODBC method of getting .xlsx sheetnames (How to load EXCEL worksheets and retrieve tab names), I recently worked for a client who didn't have access to be able to install the ODBC driver required on the server.

     

    I was eventually able to get on OLEDB connection working, this shouldn't require an installation either on the client or server side.

     

    The code below will read data from the excel file specified in the vFilePath variable.

     

    It then reloads the data in the tables2 resident load and excludes the sheet names we do not to want to load.

     

    Finally we use a For Each Loop to loop through the sheets and load them into Qlikview.

     

    Hope this helps someone!

     

    Let vFilePath = '\\path\to\my\file\';

     

     

    //Get the Sheet Name of the file

     

     

    FOR EACH file in FileList('$(vFilePath)');

     

    //In order to get the file information from SQLtables command making use of the OLEDB connection

    OLEDB CONNECT32 TO  [Provider=Microsoft.ACE.OLEDB.12.0;Data Source='$(vFilePath)';Extended Properties="Excel 12.0;HDR=YES";];

     

     

    tables:

    SQLtables;

    DISCONNECT;

     

    FOR i = 0 to NoOfRows('tables')-1

    LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

    Concatenate

    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

     

    NoConcatenate

     

    //Drop sheetnames we dont need/cant use

    tables2:

    Load Sheet_name Resident tables

    Where not MixMatch(Sheet_name, '[Sheet1]'); //<< Incomplete Sheets

     

    Drop Table tables;

     

    //Loop through the sheetnames in the file and load our data

    FOR EACH Sheet in FieldValueList('Sheet_name')

     

    MyFile:

    LOAD  field1,

      field2,

      field3,

         '$(Sheet)' as Sheet,

         Year(Date(Filetime())) as filetime,

         right(FileDir(),4) as Year

    FROM

    [$(vFilePath)]

    (ooxml, embedded labels, header is 2 lines, table is  $(Sheet));

     

    next;

     

    Drop Table tables2;