Loading data from SharePoint

[#1]

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??

[buzzy996]

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=listGUI...

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.

[petter]

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.

[#4]

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

[#5]

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

[buzzy996]

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

[#7]

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.

[buzzy996]

okie.

[buzzy996]

try tis way,with ur url by comparing

://oursharepointserver/sites/CNA/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={bc504899...}

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

[#10]

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