topic Re: Load SharePoint 365 Lists in Qlikview in QlikView

Load SharePoint 365 Lists in Qlikview

Tue, 06 Oct 2015 07:10:00 GMT

Hello,

Lately I have been trying to create a connection between SharePoint 365 and Qlikview. This works great when I reload a .qvw document using the desktop client. Using the server however, I get the following error:

06-10-2015 08:42:56: Error: Unknown error in MSXML

06-10-2015 08:42:56: General Script Error

06-10-2015 08:42:56: Execution Failed

06-10-2015 08:42:56: Execution finished.

I am running Qlikview 10.

This is the link I use to obtain data from SharePoint:

https://[domain].sharepoint.com/[website]/_api/web/lists/getbytitle('[Listname]')/Items

I tried to add the SharePoint domain to my intranet, but still no luck.

I also tried the following URL, but it does not load any data using Server reload. It does not give any error though.

FROM [[domain].sharepoint.com/[website]/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={ [list id] }&View={ [view id] }] (XmlSimple, Table is [xml/data/row]);

It also works perfectly using the Desktop Client. Only reason that I can think of that causes these problems is authentication. What can I do to solve this issue?

Re: Load SharePoint 365 Lists in Qlikview

petter — Tue, 06 Oct 2015 11:04:30 GMT

There might be (at least) two things that could fail for your script:

1) The MSXML version installed.

2) The Account that the load script runs under when scheduled as a task might not have the right access to SharePoint.

Re: Load SharePoint 365 Lists in Qlikview

Tue, 06 Oct 2015 11:17:09 GMT

Petter,

1) If the MSXML version is outdated, wouldn't it also give an error when running the script in the desktop version of Qlikview?

2) How can I determine what account is used?

Re: Load SharePoint 365 Lists in Qlikview

petter — Tue, 06 Oct 2015 15:20:00 GMT

1) Yes if it is on the same machine - you are right

2) If you have a look at Services and find the QlikView Server (QVS) you can find out which account it is run as.

Re: Load SharePoint 365 Lists in Qlikview

Thu, 08 Oct 2015 08:38:25 GMT

Well, the problem is that SharePoint cannot be authenticated directly. If I logout from SharePoint in Internet Explorer, the local desktop reload is also not working anymore.

Since we use SharePoint online (365), I guess I have to search for a way to authenticate through Qlikview.

Re: Load SharePoint 365 Lists in Qlikview

petter — Thu, 08 Oct 2015 12:57:58 GMT

You could use some other software like cURL to download the XML-file and then read it from local disk instead. That way you have more flexibility when it comes to authentication.

Re: Load SharePoint 365 Lists in Qlikview

Fri, 09 Oct 2015 10:04:25 GMT

Thank you for your suggestions. I found a workaround.

I exported my list to Excel, which creates a data connection. Next, I created a batch file that refreshes the Excel-sheet & scheduled it on the server.

Now I can simply load the data from the Excel-sheet in Qlikview.

One benefit, the structure of the Excel data is much better than the XML i retrieved using owssvr.dll. Saved me a lot of work formatting.

Re: Load SharePoint 365 Lists in Qlikview

Anonymous — Wed, 20 Jan 2016 22:37:23 GMT

Hi Jacob.

Could you tell me, how connect Excel to Sharepoint 365?.

Thank you

Re: Load SharePoint 365 Lists in Qlikview

huangchao — Wed, 29 Nov 2017 05:12:16 GMT

hi, we have some problem and also downloaded into excel, but could you help on how to set up automatic scheduler to refresh data?