Reading Data from Sharepoint lists into QlikView

Report Inappropriate Content

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.

Qvmaster2019 · ‎2013-03-11

Will this work if i have https: instead of http: and also if i have multiple layers of subsites. The actual URL is :

https://vey.sharepoint.com/sites/OnaagnTestSharepoint/Qlikview_Notes/Lists/Productivity%20Improvemen...

Report Inappropriate Content · ‎2013-03-12

Hi
Darwin,

Basically It should work with both http & https.

And if you are pulling data in Qlikview through link then we can fetch data from
the final view.But if you want data from intermediate layers, then you have to fetch link till that point.

Regards,

Yojas

Qvmaster2019 · ‎2013-03-19

thanks for the response. i was able to make it work. Reloading it using desktop is fine.

Here is the log if it's desktop and it is a SUCCESS..

3/19/2013 8:29:54 AM: 0016 row:

3/19/2013 8:29:54 AM: 0017 LOAD ows_Attachments,

3/19/2013 8:29:54 AM: 0018 ows_LinkIssueIDNoMenu,

3/19/2013 8:29:54 AM: 0019 ows_LinkTitle,

3/19/2013 8:29:54 AM: 0020 ows_Priority,

3/19/2013 8:29:54 AM: 0021 ows_Purpose,

3/19/2013 8:29:54 AM: 0022 ows_GeoScope,

3/19/2013 8:29:54 AM: 0023 ows_Business_x0020_Scope,

3/19/2013 8:29:54 AM: 0024 ows_Audience,

3/19/2013 8:29:54 AM: 0025 ows_Developer,

3/19/2013 8:29:54 AM: 0026 ows_Development_x0020_Opportunities,

3/19/2013 8:29:54 AM: 0027 ows_Created,

3/19/2013 8:29:54 AM: 0028 ows_Modified,

3/19/2013 8:29:54 AM: 0029 ows_Editor,

3/19/2013 8:29:54 AM: 0030 %Key_xml_98C49F546A18E856

3/19/2013 8:29:54 AM: 0031 FROM [https://vey.sharepoint.com/sites/DonCamTestSharepoint/Qlikview_Notes/_vti_bin/owssvr.dll?Cmd=Display...] (XmlSimple, Table is [xml/data/row])

3/19/2013 8:29:54 AM: 14 fields found: ows_Attachments, ows_LinkIssueIDNoMenu, ows_LinkTitle, ows_Priority, ows_Purpose, ows_GeoScope, ows_Business_x0020_Scope, ows_Audience, ows_Developer, ows_Development_x0020_Opportunities, ows_Created, ows_Modified, ows_Editor, %Key_xml_98C49F546A18E856, 119 lines fetched

3/19/2013 8:29:54 AM: Execution finished.

The problem now is using QMC. Attached is the log and it is a FAILURE.

3/19/2013 8:26:01 AM: 0016 row:

3/19/2013 8:26:01 AM: 0017 LOAD ows_Attachments,

3/19/2013 8:26:01 AM: 0018 ows_LinkIssueIDNoMenu,

3/19/2013 8:26:01 AM: 0019 ows_LinkTitle,

3/19/2013 8:26:01 AM: 0020 ows_Priority,

3/19/2013 8:26:01 AM: 0021 ows_Purpose,

3/19/2013 8:26:01 AM: 0022 ows_GeoScope,

3/19/2013 8:26:01 AM: 0023 ows_Business_x0020_Scope,

3/19/2013 8:26:01 AM: 0024 ows_Audience,

3/19/2013 8:26:01 AM: 0025 ows_Developer,

3/19/2013 8:26:01 AM: 0026 ows_Development_x0020_Opportunities,

3/19/2013 8:26:01 AM: 0027 ows_Created,

3/19/2013 8:26:01 AM: 0028 ows_Modified,

3/19/2013 8:26:01 AM: 0029 ows_Editor,

3/19/2013 8:26:01 AM: 0030 %Key_xml_98C49F546A18E856

3/19/2013 8:26:01 AM: 0031 FROM [https://veya.sharepoint.com/sites/DonCamTestSharepoint/Qlikview_Notes/_vti_bin/owssvr.dll?Cmd=Displa...] (XmlSimple, Table is [xml/data/row])

3/19/2013 8:26:01 AM: Error: Incorrect syntax was used in a comment.

3/19/2013 8:26:01 AM: On line number: 2. On column number: 45. System ID: https://vey.sharepoint.com/sites/DonCamTestSharepoint/Qlikview_Notes/_vti_bin/owssvr.dll?Cmd=Display....

3/19/2013 8:26:01 AM: General Script Error

3/19/2013 8:26:01 AM: Execution Failed

Any thoughts why it fails in QMC?

Report Inappropriate Content · ‎2013-04-16

Hi,

I am able to load data from the share point using RPC-Calls method.But while converting share point list data into XML i am getting <div> tag in between the data.

Can any one help me on this issue.

Thanks in advance.

saumyashah90 · ‎2013-08-14

Hi All i have gone through this link and my URl is working fine.

http://sharepointqlik.blogspot.com/2012/03/how-to-connect-sharepoint-list-to.html#!/2012/03/how-to-c...

But when i load it i get this

saumyashah90 · ‎2013-08-14

i have also tried the format given above

Report Inappropriate Content · ‎2013-09-07

Hi Chandana,

This is a possibility that <div> tag makes some problem while converting it into XML.

Can you check with <div> tag positioning in the Script? May be your problem get resolved after repositioning <div> tag content.

Regards,

Yojas

Qvmaster2019 · ‎2013-09-23

Using MS Access, it works fine when reloading via QV Desktop but fails when using QV Publisher.

Error is :

Error: SQL##f - SqlState: S0002, ErrorCode: 4294965991, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine could not find the object 'Contacts'. Make sure the object exists and that you spell its name and the path name correctly. If 'Contacts' is not a local object, check your network connection or contact the server administrator.

arieidel · ‎2013-09-27

Have you had any luck making a LOAD * FROM a Web Service?

Qvmaster2019 · ‎2013-09-30

Hi Ariel,

Still no luck but you can try Supporting Task. It works but the challenge is when you have multiple extracts.

Also, here's what Qlikview Support gave me. Hope this will help you. I will post an update once they are able to resolve this issue. If you find solution yourself, please let me know. I could use all the help out there for this.

#1

Dear Darwin,

Sure, no problem. I have all day tomorrow open, so let me know what time works best.

Usually, if it works on Desktop but not in Server, it's a permissions issue. I would check permissions for the service account running the QlikView services on the Sharepoint site. You can also log in as the QlikView service account and try to run the reload through Developer. If it fails then, you know it's a permissions issue.

#2

Dear Darwin,

I was able to find one other user with a similar issue, and their solution is copied below. Please let me know if this helps:

There are lots of posts on the internet of people having problems with mapped drives in combination with the dbase driver and running this from a service or scheduled job. This seems to be a known problem with that driver, so I would say this is more a driver problem than a QlikView problem.

Looking closer at how the DSN is defined, 32bit ODBC DSN's are stored in the registry under:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\< DSN name >

The location of the database file is specified in the "DefaultDir" string value.

I have found that if I change the DefaultDir value to the path to the database in unc format, example:

\\QTStorage.qliktech.com\UserStorage\mls\dbase

And at the same time change the "FROM" line in the QlikView Script to the path + database file name:

FROM `\\QTStorage.qliktech.com\UserStorage\mls\dbase`\ABTLG;

Then the reload works fine both when run manually and from the server.

#3

Dear Darwin,

That looks right according to the comment from the other case. The only thing I would add is to also use the UNC path for the connect string:

ODBC CONNECT32 TO [MS Access Database;DBQ=E:\Qlikview\Sharepoint\QV_Datasources\Access\Contacts.accdb];

So, use \\server\EverythingElse instead of E:\.

Please let me know how that works for you.

#4

Dear Darwin,

It looks like we've gone down the wrong path with this troubleshooting. When asking my senior techs to take a look, this is what they've come back with:

"The issue is that when reloading from server it sometimes fails with the error:

SqlState: S0002, ErrorCode: 4294965991, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine could not find the object 'Contacts'. Make sure the object exists and that you spell its name and the path name correctly. If 'Contacts' is not a local object, check your network connection or contact the server administrator.

This is an error coming from the database. They will need to ask Microsoft why their driver sometimes returns this error.

I found a similar issue and some troubleshooting hints here:
http://support.microsoft.com/kb/287696

but not really anything we can help with."

He then mentioned that if you still think the fault lies in QV, we could test a sample file for you, but that would most likely still point back to an issue with the db file or driver.

Reading Data from Sharepoint lists into QlikView