topic Consume Spreadsheet from Sharepoint in QlikView

Consume Spreadsheet from Sharepoint

chriscammers — Mon, 11 Jan 2010 15:34:52 GMT

I have to be missing something and it is probably a very simple question.

I have a spreadsheet stored in sharepoint. I want my script to load data from that sheet. What is the syntax to get the sheet? Do I have to write a macro to download the file locally then consume the data from there?

Thanks

Chris

Consume Spreadsheet from Sharepoint

chriscammers — Mon, 11 Jan 2010 15:56:50 GMT

Figured it out, you have to use the UNC path. To find the UNC path go into sharepoint and from the actions menu select open in Windows explorer.

Consume Spreadsheet from Sharepoint

chriscammers — Tue, 12 Jan 2010 15:30:31 GMT

One more note,

In addition to using the unc path you also have to install the webclient service on your server or the server will not be able to reload the document. In windows server 2008 this service is not installed by default. You have to add the User experience feature to the OS since it is not installed by default.

Happy Qliking

Chris

Consume Spreadsheet from Sharepoint

Thu, 04 Mar 2010 23:16:23 GMT

i was under the impression that the files in sharepoint are stored in sql server. I saw there's an option to pick "web files..." but have not been successful pointing to the http url of the xlsx file i have on sharepoint. Have you been able to use this feature?

Consume Spreadsheet from Sharepoint

chriscammers — Fri, 05 Mar 2010 15:10:34 GMT

So the sheet that I am using has this URL

http://servername/sites/thefolder/thesheet.xlsx

you can find it by doing a properties in the list where you find it.

you need the UNC path for qlikview to open it

from your sharepoint in the actions menu click "open in windows explorer"

now from the Windows Explorer screen right click the file you want and select properties the location is the unc path.

When you are building your qvw you will want a table file and then enter the unc path as the path for the sheet, don't forget to add the file name if you copy the path from the properties dialog.

The most critical piece of this is the user experience feature on your server and the service account running your Qlikview servers needs to have permission to read the file. If the service account does not have permission you will find that your qvw will reload perfectly on your desktop but when the server tries to reload it will fail.

Good luck I hope this helps

Chris

Consume Spreadsheet from Sharepoint

chriscammers — Thu, 22 Apr 2010 20:42:47 GMT

Ok another wrinkle in this

I recently upgraded to Windows 7

When I try to load my spreadsheet from sharepoint Qlikview cannot open it until I have opened it. Once done I can load the sheet even if is no longer open. I don't get it.

Is there something about my new OS that is weird? Anybody seen this problem?

Consume Spreadsheet from Sharepoint

chriscammers — Thu, 22 Apr 2010 21:18:01 GMT

I am not sure if this is a Windows 7 thing or if it is a thing with our company's image that was loaded on my machine,

Once again the WebClient service needs to be running, just like the server. In my case it was installed but set to run manually so I could not load the spreadsheet from qlikview. I think this will also improve the speed of opening the sheet from my browser too, but I have to test that.

Consume Spreadsheet from Sharepoint

Wed, 30 Jun 2010 16:15:03 GMT

This thread is a little stale but possibly someone who is following can help.

I would like to read data from an xls file stored on our shareponit site. I am able to accomplish when developing but when run the job through publisher it fails. I am guessing it is a permissions issue. I granted the account running the QVS and QVP services all rights to the sharepoint it would need but I still have a failure.

We are running 8.5 on a windows 2003 64 bit server, webclient service is installed and started.

Any ideas on why this is failing, is this a permissions issue?

Thank you

Consume Spreadsheet from Sharepoint

Tue, 11 Jan 2011 01:51:26 GMT

Chris - We have the same exact problem in our environment and all QlikView services run under a system account which has full permissions to the SharePoint location. We tried this with both SharePoint 2007 and 2010. We also did a trace on the QlikView server and when the Publisher tries to reload the document it gets a 401 when trying to execute an HTTP OPTIONS command to our SharePoint server.

Manually logging on to the server as the service account that runs publisher and navigating to the SharePoint UNC path provides an authentication prompt. After authenticating with the service account and password we're able to open the source file we're trying to reload from.

Any other ideas? DId you do anything else to make this work? Our server is Windows 2003 and webclient is running.

Consume Spreadsheet from Sharepoint

Mon, 31 Jan 2011 23:13:36 GMT

Steve,

Did you get this issue resolved? Sounds to me like the site needs to be a trusted site (or an intranet zone site) in Internet Explorer to prevent that authentication prompt. QlikView fails if it needs authentication to load a page in IE.

Re: Consume Spreadsheet from Sharepoint

Tue, 10 May 2011 07:13:12 GMT

Hi,

Using UNC paths fails for exampel in 64 bit Windows Server 2003 (and possibly other 64 bit clients)

Why don't you download the fiel from SharePoint ( wich is a glorified WebDAV ) using the open-source cURL client ? Mind the %% escape needed in batch files

rem download

curl --ntlm -u "DOMAIN\USER:PASSWORD" --url "https://workspaces.company.com/site/Shared%%20Documents/file.txt" -o file.txt

-Alex

Consume Spreadsheet from Sharepoint

Tue, 06 Dec 2011 19:19:19 GMT

This is correct, the site must be added as a trusted site while logged in with the account that runs the jobs. This was our sticking point.

Consume Spreadsheet from Sharepoint

erichshiino — Tue, 06 Dec 2011 20:59:50 GMT

If it helps anyone, I was able to read the excel file with qlikview using the http address:

Use the web file option

http://servername/sites/thefolder/thesheet.xlsx

Regards,

Erich

Re: Consume Spreadsheet from Sharepoint

qlikviewforum — Mon, 20 Aug 2012 14:13:03 GMT

Hi All

The particular sharepoint where I am going to fetch the data needs authentication. Could you please let me how that can be done please.

Created the new thread for the same below.

http://community.qlik.com/message/249859#249859

Thanks,

qlikview forum

Re: Consume Spreadsheet from Sharepoint

qlikviewforum — Mon, 27 Aug 2012 06:52:30 GMT

Hi chriscammers,

I am not able to find "open in Windows explorer" in actions. But I have managed to get the path after exporting to spread sheet. So could you please let me know what is the next step once I get the path?

Thanks,

Qlikview Forum

Re: Consume Spreadsheet from Sharepoint

qlikviewforum — Mon, 27 Aug 2012 07:40:33 GMT

Hi chriscammers,

I have checked in my server but WebClient is not been installed in the server. May I know what is the next step once the WebClient has been installed? So that I can do the same once the WebClient has been installed.

Thanks,

Qlikview Forum

Re: Consume Spreadsheet from Sharepoint

qlikviewforum — Wed, 29 Aug 2012 12:45:11 GMT

Hi Chris

We are waiting for your reply. Please help!

Thanks,

qlikviewforum

Re: Consume Spreadsheet from Sharepoint

gainkarthi — Thu, 18 Apr 2013 11:47:46 GMT

All,

Where i can get web client service installation.

Regards,

Karthi