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:

      StepsToConfigureExcelFile.PNG

       

      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)];
                     RevisionsprotokollODBC:
                    
          Load *;
                    
          SELECT * FROM `$(file)`.`RevisionsProtokoll$`;
               
          next
              
          next
          next

           

          - Marcus