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: 
chriscammers
Partner - Specialist
Partner - Specialist

Consume Spreadsheet from Sharepoint

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

1 Solution

Accepted Solutions
chriscammers
Partner - Specialist
Partner - Specialist
Author

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.

View solution in original post

17 Replies
chriscammers
Partner - Specialist
Partner - Specialist
Author

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.

chriscammers
Partner - Specialist
Partner - Specialist
Author

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

Not applicable

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?

chriscammers
Partner - Specialist
Partner - Specialist
Author

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

chriscammers
Partner - Specialist
Partner - Specialist
Author

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?

chriscammers
Partner - Specialist
Partner - Specialist
Author

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.

Not applicable

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

Not applicable

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.



Not applicable

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.