Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group

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 II
Creator II

Hi All

The particular sharepoint where I am going to fetch the data needs authentication. Could you please let me how that can be done please.

Created the new thread for the same below.

http://community.qlik.com/message/249859#249859

Thanks,

qlikview forum

0 Likes
Specialist II
Specialist II

Hi,

I'm using RPC-Calls in two desktop versions of QV.

In SR1 v11 I'm getting a syntax error and in SR5 v10 I´m getting this message error: "The download failed"

I don't know what to do, I´m following this topic to make it right but I can´t...

Any help will be apreciated

0 Likes
Creator II
Creator II

Hi Annette

Could you please help me out how you managed to do that. Still I couldn't load the data from sharepoint list. Any help appreciated!

Thanks,

Qlikviewforum

0 Likes
Creator II
Creator II

Hi Christian

Could you please help me out how you managed to do that. Still I couldn't load the data from sharepoint list. Any help appreciated!

Thanks,

Qlikviewforum

0 Likes
Creator II
Creator II

Hi Nimish

Could you please help me out how you managed to do that. Still I couldn't load the data from sharepoint list. Any help appreciated!

Thanks,

Qlikviewforum

0 Likes
Creator II
Creator II

Hi Ellen

Could you please help me out how you managed to do that. Still I couldn't load the data from sharepoint list. Any help appreciated!

Thanks,

Qlikviewforum

0 Likes
Not applicable

Hello,

I'm trying to extract data from sharepoint using RPC-Calls but i have a error message :

"White space required was missing" (in french : "L'espace blanc requis était manquant.")

I don't understand because my URL doesn't contain white space :

test_import:

LOAD
IC,
DATE,
CLIENT,

SUJET

from

[https://xxxxxxx.com/sites/xxxx/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}&View={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}]
(
XmlSimple, Table is

[xml/data/row])
;

do you know what's wrong ?

0 Likes
Not applicable

Good afternoon all,

I would like to use the RPC Call option listed above but I am brand new to Qlikview and I know I am doing something wrong.

Could we take this one from the top in a little more "Step-by-Step" maner assuming I know little or nothing?

One thing I noticed when working with this is that the GUID for my view is not in the 8-4-4-4-12 format described above. It is a 10-6-6-6-14-2 format so I may be doing something wrong there too.

I really think that with a little mare basic instruction, I coul dget this to work.

Thanks in advance,

Steve

0 Likes
Not applicable

With regard to the GUID format:

You're dealing with a URL-encoded version of the GUID (using %## to represent 'special' characters in the URL).

What you actually have is:

%7B########%2D####%2D####%2D####%2D############%7D

The %7B represents {

The %2D represents -

The %7D represents }

If you're not already familiar with URL escaping values, this can be confusing.

Not applicable

Hi,

Can you please tell me how you manage to pull data from SharePoint to Qlikview?

Because the links given above are no longer working.

Regards,

Yojas

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