Connecting QlikView to Sharepoint 2016 List not the Listview

[Anonymous]

Hello all,

I am trying to connect to a internally hosted Sharepoint server list table.

I can already create connections to a Listview with XML code but, not the actual list table itself. Listviews are limited to 5000 item records.

Any guidance would be appreciated

Thanks in advance.

[balabhaskarqlik]

This could be because of the setting you have enabled in the Sharepoint List. Request your share point administrator to perform the below for you.

‘Item Limit’ - ‘No of Items to display’ You need to increase the limit from 30 to few 100s or 1000s.

There are 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.

[Anonymous]

Hi Bala,

I will try to answer in order -

• the limitation is due to the SharePoint LVT of 5000 items. This cannot be increased without adversely impacting the overall performance of the company's global SharePoint server. This is per my company IT group after consulting with Microsoft.
• Item 1 - I do not know the required coding to create a Webservices app
• Item 2 - this is what I used to create an XML link to SharePoint listviews. These are what have become limited to the most recent 5000 items in the list. This is the LVT listview limit, not a setting in the the listview creation. I have an excel spreadsheet that will access the actual list and pull all 5500 + items in the list.
• Item 3 - I have an access database linked to the actual list and can see all 5500 + items but cannot find the correct connection to use with Qlik. The database was created from the actual SharePoint list site. The Access dbase is a .accdb file and both the 12.0 and 15.0 Access Database Engine OLE DB Providers fail to work.

As this QV application will be shared on our network with our other QV apps, I would prefer not to use the Access dbase solution as I will then have to get IT to place it on the server and they absolutely hate access and do not support it.

Using the spreadsheet will work on my local machine but not for use on the server. Plus, it has to be updated before every use.

Ideally, if I can figure out what the correct XML coding is for the actual list not a limited listview, this would be best option.

Please advise if you know of how I can find actual SharePoint site's list for use in the XML.

[ken4runner]

this thread has a skeleton of a way to do a loop to get more than 5000 records from the sharepoint list using the web file connector:
https://community.qlik.com/t5/New-to-Qlik-Sense/SharePoint-and-Qlik-Sense-A-better-way-to-get-List-D...