topic Loading data from SharePoint in QlikView

Loading data from SharePoint

Tue, 09 Jun 2015 09:30:26 GMT

I'm trying to load a table of risk data from SharePoint, into Qlik.

I've found a great piece on here that sets out how to do it:

https://community.qlik.com/docs/DOC-1308

And it's the 2nd method 'Using RPC calls' that i'm trying to do. But I can't get my head around it!

The URL of the SharePoint site is:

http://sptx/sites/GrainLNG/TmAreas/Asset/Lists/AM%20Risk%20Register/Risk%20Register%20Qlik.aspx

Following the guidance on the above link, i've found the List GUID to be:

{0767ED6D-D760-4EC6-AEEC-DF26A47BD606}

And the View GUID to be:

{707A71F9-BDA3-4974-B699-0647CD700204}

I'm presuming that you put the load instruction in here?

But i'm not sure how to write the address incorporating the GUID's

Can anyone help me??

Re: Loading data from SharePoint

buzzy996 — Wed, 10 Jun 2015 16:14:54 GMT

follow these steps: just u have to replace ur ids with ur sharepoint url like below,

Using RPC-Calls

An easier way is to use RPC-calls using owssvr.dll, like:

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=listGUID&View=viewGUID

listGUID and viewGUID must be in the format {XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}.

The listGUID can easily be found by navigating to the list in Sharepoint and go to List Settings. The listGUID will then show up in the address-field in your browser.

If viewGUID is omitted, the default view will be retrieved. To find the viewGUID locate the list in Sharepoint and activate the view needed. Select View Soure in IE to see the source code. Search for view={ to locate the viewGUID.

The response will be in XML, so when reading into QlikView make sure you select XML Files and Internet File. Add the correct crafted URL as described above. The data retrieved can be found in the node xml/data/row.

Example URLs to be used in QlikView:

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={B9C20FC4-4C04-4F4C-93EF-E69E11D286F2}

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={B9C20FC4-4C04-4F4C-93EF-E69E11D286F2}&View={58678A30-DC12-4C66-8568-28E4D9A3EED1}

For more info about owssvr.dll use Google to find various ways of filtering the information

Note: Only fields defined in a view can be retrieved. You need to construct the view with the fields needed, if not using the default view.

Re: Loading data from SharePoint

petter — Wed, 10 Jun 2015 17:27:52 GMT

Which version of SharePoint are you using. The RPC-calls with GUIDs are the old-style way of doing it. There is a newer REST API that can be used with SharePoint 2010 and newer.

Re: Loading data from SharePoint

Thu, 11 Jun 2015 06:47:27 GMT

We havn't moved to 2010 yet, so I think the old ways will have to be the way forward!

Re: Loading data from SharePoint

Thu, 11 Jun 2015 06:59:04 GMT

Thanks for the responce Shiva!

I'm aware of the text you included - that was what I linked to in my origional post. My question was really, how do I build the statement that goes into the web files dialogue box?

Their example is:

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={B9C20FC4-4C04-4F4C-93EF-E69E11D286F2}&View={58678A30-DC12-4C66-8568-28E4D9A3EED1}

But the URL of my SharePoint site is:

http://sptx/sites/GrainLNG/TmAreas/Asset/Lists/AM%20Risk%20Register/Risk%20Register%20Qlik.aspx

and doesnt have the owssvr.dll part in it, so even though I have the two GUID's, I don't know how to put it all together to form the address I need to use.

Can you help with that?

Richard

Re: Loading data from SharePoint

buzzy996 — Thu, 11 Jun 2015 08:15:42 GMT

are able to solve ur issue by following the above process?

Re: Loading data from SharePoint

Thu, 11 Jun 2015 08:25:44 GMT

No,

Iv'e posted a reply to you, but becouse there are URL's in it, the message goes through the Qlik moderators, so you'll get it as soon as they have looked at it.

Re: Loading data from SharePoint

buzzy996 — Thu, 11 Jun 2015 08:29:56 GMT

okie.

Re: Loading data from SharePoint

buzzy996 — Thu, 11 Jun 2015 15:34:06 GMT

try tis way,with ur url by comparing

://oursharepointserver/sites/CNA/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={bc504899-b7cd-4055-ac07-ee8cac6b1364}&View={158F2B6D-D3CE-4398-9677-759A6E574C26}

just check with ur sharepoint team,they will help..they know abt owssvr.dll

Re: Loading data from SharePoint

Wed, 17 Jun 2015 07:22:39 GMT

Shiva,

I tried the link structured in theway you suggested and it didn't work. I've spent an hour on the phone with a consultant from Qlik trying to fix it and he basically suggested the same as you - we then tried literally dozens of slightly different iterations and none worked, so he now thinks it may be that SharePoint 2007 doesn't really support this.

He's going to take it to a meting they are having next week and see if any of the other in-house consultants can solve it.

So thanks for you help, it is appreciated and I'll let you know if they find a solution!

Richard

Re: Loading data from SharePoint

buzzy996 — Thu, 18 Jun 2015 08:44:29 GMT

ur wc...np,let me know if u have any issue on the same.

we implemented the same way what i shared with u on this link.