Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted

Loading XLSB file format into Qlikview

glitter-hello-text-smiley-emoticon.gif


                We have multiple xlsb format files and I want to load all of them in a single table. I have created ODBC connection but I can not find similar thread on how to load multiple files in single table.


Kaushik.solanki‌  I just see your threads regarding the xlsb format issue. Do you have any idea about this.?


Thank you in advance.


1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Loading XLSB file format into Qlikview

Hi,

Try this script

//to read each file from the specified folder

FOR EACH file in FileList('C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (3)\*.xlsb');

//In order to get the file information from SQLtables command making use of the ODBC connection format

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

Table:

SELECT *

FROM '$(file)'.'Sheet1$';

Next

Regards,

jagan.

View solution in original post

21 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Loading XLSB file format into Qlikview

Hi,

Its not possible to load all the xlsb files in one go.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted

Re: Loading XLSB file format into Qlikview

That's sad. smiley-sad040.gif Also I am facing an issue while connecting the xlsb by using the ODBC method. I have created a connection string yesterday and it was working fine. When I opened the same yesterday eveing, it showing some error. Again the problem occurs today morning. I just created the connection again and it's working fine. Not sure, whether I need to create a connection every time.

Anyways, Thank you for the reply.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Loading XLSB file format into Qlikview

Hi,

Try OLEDB option and see if it works in your case.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
MVP & Luminary
MVP & Luminary

Re: Loading XLSB file format into Qlikview

Hi Tamil,

You can try something like below, this is for excel you can try the same for XLSB file using ODBC and for loop.

//to read each file from the specified folder

FOR EACH file in FileList('filepath\*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];


tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)'
as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);
NEXT i
Next


Regards,

Jagan.


Highlighted

Re: Loading XLSB file format into Qlikview

Kaushik,

I have tried the same by using the "Microsoft OLE DB Provider for ODBC Drivers" but the test connection failed. See below screenshot.

Capture.PNG

It would be really grateful, If you could guide me.!!

Highlighted
MVP & Luminary
MVP & Luminary

Re: Loading XLSB file format into Qlikview

HI,

You can give a try as Jagan has said.

For OLEBD it seems that the driver is corrupted, Try reinstalling the driver.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted

Re: Loading XLSB file format into Qlikview

Jagan,

I have modified the code but I am not sure what could be the format for xlsb. I know that


Biff - for extension xls

Ooxml - for extension xlsx.

Could you guide me.



Highlighted
MVP
MVP

Re: Loading XLSB file format into Qlikview

It is neither format - xlsb is an Excel binary file. You can only open this via ODBC (and possibly OLEDB). You cannot load it using a normal LOAD * FROM myFile.xlsx.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted

Re: Loading XLSB file format into Qlikview

Jonathan,

It seems, I can not loop through the multiple files.!!