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

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

17 Replies
petter
Partner - Champion III
Partner - Champion III

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?

Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

>>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
jonathandienst
Partner - Champion III
Partner - Champion III

t1.png

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

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!

Anonymous
Not applicable
Author

please check

hope this will be of some use

Not applicable
Author

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

Not applicable
Author

Capture.JPG

petter
Partner - Champion III
Partner - Champion III

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