Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anjali0108
Partner - Creator III
Partner - Creator III

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
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

anjali0108
Partner - Creator III
Partner - Creator III
Author

Thanks a lot Marcus.

I am just trying to understand the code.

Let me try and then revert.