Reading Data from Sharepoint lists into QlikView

[#1]

This is something that's been asked a number of times, so I would like to share this small tip for everyone. interesting in reading data from Sharepoint lists into QlikView.

There are a number of ways to extract data from Sharepoint.

1. Using WebServices

2. Using RPC-Calls

3. Using Microsoft Access as a Data Bridge

1. Using WebServices

The most common way is using Webservice-calls. However constructing XML-request isn't all straight-forward and can't be done directly in QlikView.

2. 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.

3. Using Microsoft Access as a Data Bridge

You can retrieve data from sharepoint using Microsoft Access.

If you are looking at a sharepoint list, click at

Actions --> Open with Access

Microsoft Access opens and a window "Open in Microsoft Access" appears. Select a folder and choose the option "Link to Data on the Sharepoint Site".

Afterwards you can connect from QlikView to this access database via ODBC and you can easily read all fields of the sharepoint list with a SQL Query.

This disadvantage of this approach is, that you'll always have to store your MS Access Database with your QlikView-Sheet. In a server environment this isn't a big problem, but with local clients loading data, this approach has got its shortcomings.

[gksmithlcw]

Great information! It took me a couple of tries to get the link just right but the dll method works great. Thanks for the post!

[brindlogcool]

I am able to load the list from Sharepoint using the RPC calls. Would like to know is there any limitation in the no of rows to be accessed or i will be able to access the complete list irrespective of no of rows in it.

Current we have only 1200 rows in the list. So i am not able to test it. It would be really helpful if somebody could share the details.

[#4]

Hi Brindlogcool,

you will be able to pull all rows even it's in millions but while creating charts using it, you have to check other parameters too.

Regards,

Yojas

[balasundaram]

can u tell wiki page name?

[#6]

Hi Darwin,

I am running into a similar issue where in I am able to extract data from sharepoint list into qlikview from the desktop and from the server, but it is not reloading from the QMC. I see that you ran into the same issue as well. Do you know of any solutions?

Please advise ASAP.

Thanks,

Mayank

[dgreenberg]

For those of you having trouble like me look at this URL: Getting XML Data From a SharePoint List – The Easy Way - Kirk Evans Blog - Site Home - MSDN Blogs

This helped me figure out the correct syntax for the URL - same info just a little easier to follow.

http://{0}/_vti_bin/owssvr.dll?Cmd=Display&List={1}&XMLDATA=TRUE

{0} – The URL to your site.  This could be the root web or a child site.

{1} – The GUID for your list.

Works great in QV 11.

[benmaslen]

Thanks to all contributors, most helpful.

Another option for getting your URL right. This relates to 2007 but similar in later versions.

Select the "Export to Spreadsheet" option from the Actions menu of your Sharepoint list view. Follow the prompts to open the spreadsheet and this will export your list data to excel. In excel go to Data-->Connections-->owssvr-->Definition-->Command text. This contains the path and GUID's you need.

cheers

[po2221982]

We had a similar issue - data loaded from the desktop perfectly but when we ran from QMC the data did not load. Once we granted the QV service account with access to the SharePoint List the data loaded via QMC.

[po2221982]

Grant the QV service account access to the SharePoint List. The data should load from the QMC at that point.

[#11]

This post has been brilliant, Thank you!