Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More

Reading Data from Sharepoint lists into QlikView

Not applicable

Reading Data from Sharepoint lists into QlikView

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.

Labels (2)
Comments
Creator
Creator

how to add credential for Using RPC-Calls CommunityAdmin

0 Likes
Not applicable

great!

Creator II
Creator II

I tried putting this in the connect to website.  When I click connect it says http error and then the window says object not found.  I know the View and list GUID are correct as they show up when I export the list to excel.  Any help would be appreciated.

'https://officespace.ace-ina.com/s/PRS/TRC/Lists/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={063FFB4B-D345-43D5-A6FD-7AF7F867EF5A}&View={563C7161-148C-42EE-82EE-9748BBC985AC}'

0 Likes
Explorer
Explorer

I often see people get the list guid and view guid around the wrong way in the Qlik connection string as they appear in the excel connection in the opposite order.

Sent from my phone

Creator II
Creator II

Thank you Ben.  You were absolutely correct I had the View and List GUID's switched.  Appreciate your help!

0 Likes
Contributor III
Contributor III

Hi there,

I have a library with several folders inside.

what I need to do is loop through all subfolders and fetch the metadata entered for each file dropped in the subfolder.

my problem now is, that every subfolder has the same list and view ID and when I read it into Qlik I only get a list of all subfolders within the parentfolder.

anyone an idea how to solve this?

Thanks and kind regards from germany

Ben

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2009-02-09 03:28 AM
Updated by: