This article explains how to load a list from Sharepoint using the native Webfile connector in Qlik Sense and QlikView.
Environment:
Qlik Sense Enterprise on Windows
QlikView
Qlik Web Connectors
- Identify the GUID of the Sharepoint list. This can be done by:
- Open the List Settings page.
- GUID of the list can be found at the end of the page URL.
- Copy the last part of the URL after "...?List=" to a text editor and replace “%2D” to “-“
- Place the list GUID into the following URL: http://Server_Name/[sites/][Site_Name/]_vti_bin/owssvr.dll?Cmd=Display&List=GUID&XMLDATA=TRUE
The final URL should look like the following: http://sharepoint/_vti_bin/owssvr.dll?Cmd=Display&List=0f40e3a1-bc9a-4800-9d6d-e58da228126d&XMLDATA=...
- Create a WebFile connection in QlikView/ Qlik Sense with the URL in step (2). The returned XML file contains 3 tables, among which list data is contained in table xml/data/row
- If the Web file connection cannot be created or fails to load, basic troubleshooting steps are as follows:
- On the server where QlikView/ Qlik Sense is installed, open a web browser with the service user or the user running Desktop application. Put the URL in step (2) in the address bar. Make sure that the XML file can be shown/downloaded directly without any authentication or redirection.
- Open Internet Options > Security > Local intranet > Custom level... > scroll to User Authentication > select option "Automatic logon with current username and password". Note that it's not always the Local Intranet zone. You can open the Sharepoint list in a browser, right click the page > Properties to understand which zone the page belongs to.
- For Qlik Sense only: Open file Settings.ini (C:\ProgramData\Qlik\Sense\Engine for Qlik Sense Enterprise, C:\Users\<Username>\Documents\Qlik\Sense\Settings.ini for Qlik Sense Desktop), add the following parameter: WebFileUnrestrictedAuth=1 and restart Qlik Sense Desktop/ Engine service. Make sure to backup the file before making any change.