XLSB file data retrieval: Urgent

[#1]

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

[petter]

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?

[#3]

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]

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

[jonathandienst]

[#6]

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]

please check

hope this will be of some use

[#8]

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

[#9]

[petter]

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