Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
anjali0108
Contributor II

Xlsb File Format In qlikview

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.

2 Replies

Re: Xlsb File Format In qlikview

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

anjali0108
Contributor II

Re: Xlsb File Format In qlikview

Thanks a lot Marcus.

I am just trying to understand the code.

Let me try and then revert.

Community Browser