topic Re: Xlsb File Format In qlikview in QlikView

Xlsb File Format In qlikview

anjali0108 — Tue, 16 May 2017 12:33:31 GMT

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 — Tue, 16 May 2017 13:24:10 GMT

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

Re: Xlsb File Format In qlikview

anjali0108 — Thu, 18 May 2017 12:55:04 GMT

Thanks a lot Marcus.

I am just trying to understand the code.

Let me try and then revert.