Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

XLSB file data retrieval: Urgent

Hello Community,

I need urgent help on issue related to XLSB file format. Below are the details:

1) Connection to XLSB file at Sharepoint: DONE

OLEDB CONNECT32 To [Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;database=$(vSharepoint_Time_Booking_Data);Extended Properties="Excel 12.0;HDR=YES;"];

where vSharepoint_Time_Booking_Data = http://<servername>/sites/<>/<>/<>/<>/Time Booking Data.xlsb

It shows connected in the script execution.

2) Fetch data from the file, having only one sheet named as 'Project time booking' : NEED HELP

gwassenaar

swuehl

jagan

maxgro

tresesco‌ and all other geeks!!

Thanks

itsangad

Tags (3)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: XLSB file data retrieval: Urgent

Yes - getting support from other resources let you focus on your really value-added solution develolpment.

View solution in original post

17 Replies
Highlighted
MVP
MVP

Re: XLSB file data retrieval: Urgent

What is the issue you are facing? Do you get an error message? Are you developing something new or is it something that used to work that doesn't work anymore?

Highlighted
Not applicable

Re: XLSB file data retrieval: Urgent

Hey Petter,

Thanks for coming in!

The requirement is that I need to directly use the XSLB placed in at Sharepoint and fetch the data from that file into qlikview.

Now for XLSB to work, we need to either make an ODBC or OLEDB connection and it works as a database and SQL commond is used.

Everything works fine, if the file is locally placed. But I am trying to use the http:// Sharepoint URL to create the OLEDB connection( which I was able to connect).

All I need is the syntax to fetch the data from this OLEDB connection.

Thanks

Highlighted
MVP
MVP

Re: XLSB file data retrieval: Urgent

>>All I need is the syntax to fetch the data from this OLEDB connection.

You need syntax like

     SQL SELECT * FROM SheetName    // (or perhaps $Sheetname) - to load from am XL sheet

     SQL SELECT * FROM RangeName   // to load from a named range

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

Re: XLSB file data retrieval: Urgent

t1.png

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

Re: XLSB file data retrieval: Urgent

Hey Jonathan,

thanks for responding, but I am getting error(see attached), when tried using the query:

SQL SELECT * from [Project time booking];

where Project time booking is the sheet name.



thanks!

Highlighted
Contributor III
Contributor III

Re: XLSB file data retrieval: Urgent

please check

hope this will be of some use

Highlighted
Not applicable

Re: XLSB file data retrieval: Urgent

May be I think, the OLEDB connection is not made correctly, since I changed the XLSB filename in the connection string to any random text, Qlikview still showed me as connected.

My question:

Do you know, how to connect to XLSB file placed at Sharepoint ?

Thanks,

Angad

Highlighted
Not applicable

Re: XLSB file data retrieval: Urgent

Capture.JPG

Highlighted
MVP
MVP

Re: XLSB file data retrieval: Urgent

Actually - when I come to think of it - the ODBC driver for Excel does not support the HTTP protocol to retrieve your file.

So you have to have a step to retrieve the XLSB-file from SharePoint to a drive that is accessible for the ODBC-driver. Or you will have to map a drive to a WebFolder on the SharePoint server if that is possible for you.

The first option can be done by using for instance cURL - a free public domain utility that is an command line executable program which can retrieve files for you (amongst many other things). The second option depends quite a bit on your setup...

Then you will have to do something like this in your load script:

EXECUTE d:\bin\curl.exe -O h t t p : / / <servername>/sites/<>/<>/<>/<>/Time Booking Data.xlsb ;

// then you can have a normal LOAD following this reading from "Time Booking Data.xlsb";




curl.exe can be found as a downloadable on:    curl . haxx . se