2 Replies Latest reply: May 18, 2017 8:55 AM by Anjali Ahlawat RSS

    Xlsb File Format In qlikview

    Anjali Ahlawat

      Hi All,


      As suggested by some thread in qlik community,to use .xlsb file format,we should follow the below steps:


      1. Make sure Excel ODBC drivers are installed. It install by default most of the time while installing Office.

      2. Go to Administrative Tools -> Data Sources (ODBC) -> Choose User DSN -> Choose either 'Excel Files'from the list ->

      Press 'Configure' -> finally choose the file as ODBC source

      Once the connection is addded successfully, In QV script editor you can select the odbc connection


      I downloaded excel drivers but I am not sure if I configured it properly.

      The below screenshot is just giving me option to select a file.What if I want to select a whole folder where all excel files will come?


      Please suggest what should I choose in below screenshot:



      Also,Could anyone suggest what username and password am I going to use while connecting to ODBC in qv script



      Thanks in advance.

        • Re: Xlsb File Format In qlikview
          Marcus Sommer

          You couldn't access a folder per odbc only tables respectively files. That meant you need an additionally routine to grab multiple files - most convenient will be a loop per filelist/dirlist and it should be look like this one:



          let vCurrentYearMonth = date(today() - 1, 'YYMM');
          let vLastYearMonth = date(monthstart(today() - 1) - 1, 'YYMM');

          for each vRevisor in 'a', 'b', 'c', 'd'
               for each vPeriod in '$(vLastYearMonth)', '$(vCurrentYearMonth)'
          for each file in filelist('X:\Path\Revision\$(vRevisor)\$(vPeriod)??_????.xlsb');
          ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
          Load *;
          SELECT * FROM `$(file)`.`RevisionsProtokoll$`;


          - Marcus