topic Re: XLSB file data retrieval: Urgent in QlikView

XLSB file data retrieval: Urgent

Mon, 05 Oct 2015 15:29:46 GMT

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

Re: XLSB file data retrieval: Urgent

petter — Mon, 05 Oct 2015 15:42:28 GMT

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?

Re: XLSB file data retrieval: Urgent

Tue, 06 Oct 2015 03:38:18 GMT

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

Re: XLSB file data retrieval: Urgent

jonathandienst — Tue, 06 Oct 2015 05:20:26 GMT

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

Re: XLSB file data retrieval: Urgent

jonathandienst — Tue, 06 Oct 2015 05:21:50 GMT

Re: XLSB file data retrieval: Urgent

Tue, 06 Oct 2015 05:32:59 GMT

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!

Re: XLSB file data retrieval: Urgent

Anonymous — Tue, 06 Oct 2015 05:40:55 GMT

please check

hope this will be of some use

Re: XLSB file data retrieval: Urgent

Tue, 06 Oct 2015 05:42:43 GMT

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

Re: XLSB file data retrieval: Urgent

Tue, 06 Oct 2015 05:45:43 GMT

Re: XLSB file data retrieval: Urgent

petter — Tue, 06 Oct 2015 05:47:00 GMT

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

Re: XLSB file data retrieval: Urgent

petter — Tue, 06 Oct 2015 05:48:04 GMT

Re: XLSB file data retrieval: Urgent

Tue, 06 Oct 2015 05:55:25 GMT

Thanks Petter!

I think first option will be the best bet for me. Can you please help me download the correct .exe file?

There are many versions available at the site and it downloaded as XZ file.

Thanks in advance!

Thanks

Re: XLSB file data retrieval: Urgent

petter — Tue, 06 Oct 2015 06:17:00 GMT

The one i ticked with a red dot would be the quickest to get started with - it's in a ZIP-file and only one file curl.exe:

Re: XLSB file data retrieval: Urgent

Tue, 06 Oct 2015 07:02:31 GMT

Hey Petter,

I used:
EXECUTE F:\Downloads\curl_4\curl.exe -O //<server>/<>/<>/<>/<>/<>/Time%20Booking%20Data.xlsb;

This shows DONE in the script execution.

Now how should I connect to the xlsb sheet named "Project time booking" ?

Thanks,

Angad

Re: XLSB file data retrieval: Urgent

petter — Tue, 06 Oct 2015 07:27:48 GMT

Check if you have a file name like the XLSB file in the current directory:

Time.......xlsb

Or it might be safer to explicitly name the directory and name of the file:

EXECUTE f:\downloads\curl_4\curl.exe -o f:\download\myfile.xlsb h t t p : // server / ..... / ....xlsb ;

Place the cursor in an empty line after the EXECUTE and make sure that you already have the connect

statement in place pointing to c:\downloads\myfile.xlsb.

Then you use the SELECT wizard to help you get a valid generated load statement.

Re: XLSB file data retrieval: Urgent

petter — Tue, 06 Oct 2015 07:28:44 GMT

Re: XLSB file data retrieval: Urgent

Wed, 07 Oct 2015 06:11:35 GMT

After scanning almost every article at google and researching, I dropped the idea of directly hitting the Sharepoint for XLSB file format.

I have asked the concerned team to download the file from sharepoint to my server location and then using it using ODBC or OLEDB connection.

Thanks,

Angad

Re: XLSB file data retrieval: Urgent

petter — Wed, 07 Oct 2015 07:19:17 GMT

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