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.

[Annette_Steinrücken]

Great, I managed to read my first document list in Sharepoint

[#3]

I am getting confusing date formats. I haven't done more investigation. Is there a simple way to read this XML? Please update the wiki.

[#4]

I tried the instructions in the Article an am getting an HTTP 400-Bad Request error. Any suggestions?

[#5]

I am unable to enter an address into the "Internet File" part of the XML screen. I can enter information into the "Local file" section, but not the "Internet File". Could anyone explain to me why this is happening? Thanks!

[#6]

I've found an interesting way to read sharepoint lists, which has been much mor e comfortable for me. I've updated the wiki page accordingly.

[#7]

You could ofcourse directly access the SQL database as I describe here: http://weblaunch.nl/?p=309. Once you get the SQL correct it's easy to use in your QV script. That worked best for me...

[#8]

Just noticed the following while reading lists from sharepoiont site using the RPC calls - owssvr.dll. We have an inhouse sharepoint services with NT authentication enabled. The RPC calls work perfectly on this since the authenticaion to the site is the Windows Authentication itself. But we also have another sharepoint site which is hosted by a third party. Again we need to login to the site to view it in the browser. They have their own Windows authenticaion. While making the RPC calls through Qlikview, it gives error of 'Acces Denied'. I am presuming this is because, Qlikview is not able to throw up the authentication dailog box. Has anyone has any idea what could be a possible way of doing this

[bullish35]

Using your instructions for RPC-calls worked flawlessly! However, I have question about SP data I'm pulling into QV from a 'People or Group' column. Once loaded into QV, the value of each data from my People or Group type SP columns is preceded by a number and pound sign. For example, the list of Program Managers comes in like this . . . 189;#Smith,John 134;#Doe,Jane I just want to pull in Smith, John and Doe, Jane I'm wondering if a anyone else has encountered and resolved this in their script. I'll post question in the Forums section. Thank you.

[#10]

I couldn't get this to work at all.

However when I deleted the curly brackets around the list ID it worked. So something to try.

[nathanfurby]

This works for the root folder (list) but how do you return contents of a sub folder?  The list GUID is the same and always relates to the root.  There is a 'FolderCTID' but cannot seem to include it in the call. Anyone tried this? Thanks