Load SharePoint 365 Lists in Qlikview

[#1]

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?

[petter]

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.

[#3]

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?

[petter]

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.

[#5]

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.

[petter]

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.

[#7 · Accepted solution]

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.

[Anonymous]

Hi Jacob.

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

Thank you

[huangchao]

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