topic Re: Retrieve data from an Excel-Sheet located on a SharePoint Server. in Connectivity & Data Prep

Retrieve data from an Excel-Sheet located on a SharePoint Server.

tschollqlikview — Wed, 01 Mar 2017 17:06:43 GMT

Dear all,

I need to retrieve data from an Excel-Sheet located on a SharePoint Server.

This works perfectly in Qlikview:

LOAD A,

FROM

\\mySharePoint\Shared%20Documents\test.xlsx

(ooxml, no labels, table is Output_short);

The same code however fails in QlikSense:

This statement only works with lib:// paths in this script mode

Creating a new Folder Data Connection as suggested in this post does also not help but results in the error: Invalid Path.

Any help would be highly appreciated

Best,
Tobias

Re: Retrieve data from an Excel-Sheet located on a SharePoint Server.

Gysbert_Wassenaar — Wed, 01 Mar 2017 18:20:56 GMT

If the excel file is really located in a SharePoint Server then it should have an URL. You can use that URL to connect to the excel file using a web file data connection.

Re: Retrieve data from an Excel-Sheet located on a SharePoint Server.

tschollqlikview — Wed, 01 Mar 2017 19:17:42 GMT

Well, the statement above was created using a web file data connection in QV, in QS I only get an error. The file exists and QV can load it. Maybe the problem lies in Windows authentification?

Re: Retrieve data from an Excel-Sheet located on a SharePoint Server.

Gysbert_Wassenaar — Thu, 02 Mar 2017 07:38:52 GMT

Try generating it using Qlik Sense instead of copying it over from Qlikview. The UNC path won't work in Qlik Sense and it is NOT an url. You simply used an UNC path in File Wizard dialog in Qlikview. That works in Qlikview, but the resulting load statement will not work in Qlik Sense.

Re: Retrieve data from an Excel-Sheet located on a SharePoint Server.

tschollqlikview — Thu, 02 Mar 2017 08:08:56 GMT

Thx for your reply, Gysbert. I tried it with both, the UNC and the URL: both failed. Attached you can see a screenshot. The inserted URL is valid and it will lead to a download of the excel file inside the browser. However, the SharePoint requires Windows Auth, which works in the browser but maybe won’t work inside QlikSense?

Re: Retrieve data from an Excel-Sheet located on a SharePoint Server.

Gysbert_Wassenaar — Thu, 02 Mar 2017 18:02:35 GMT

However, the SharePoint requires Windows Auth, which works in the browser but maybe won’t work inside QlikSense?

Could be. I don't know enough about SharePoint to say for sure. If there's a way to make a document public in SharePoint so that no authorization is necessary then you can test if that's the problem.

Re: Retrieve data from an Excel-Sheet located on a SharePoint Server.

tschollqlikview — Thu, 16 Mar 2017 17:17:15 GMT

Ok, so the solution was to map the sahrepoint as a network drive and then use this drive as a local folder in QlikSense