Getting SharePoint data into QlikView

There are already a number of articles on how to do this, but after reading through them and continuing to struggle with it, i decided it was time to also jump in to these waters and try to bring some clarity.

Here is what you are going to need:

  1. The url to use with the owssvr.dll
  2. If you not using the default view, or more than just the default fields in that view, you will also need the views underlying GUID .

The general form of the URL

[sharepoint site url]/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=[GUID for List]&View=[GUID for view - optional]

The GUID for the list is something that you can scrape out of your browser when you access your sharepoint site. Think of it as the record ID for the Sharepoint display. If you don’t specify the view information, you will automatically get the default view and the default fields. If you need more information, then get the view GUID from your SharePoint admin. In my case there were some additional fields that were needed that are not normally displayed, like some internal record keys that link to a different system. If you are not using the additional view information, just leave off everything after the list, so leave off the &View= segment entirely.

You can test out the URL with any web browser, but my preferred method is to use Microsoft XML Notepad 2007 because it presents the data in a nice XML format. If it returns results, then you can move ahead to using the QlikView Web Source wizard. From the script editor, select “Web Files” and it will ask you for an Internet File – enter the URL here.

When you click Next, it should return some results, but it will try to interpret it as delimited text. Change the file type to XML, and it should render into something usable.
It will provide you 3 tables:
xml
xml/data/row
xml/Schema/ElementType/AttributeType

Click finish. It will write the script to load the three tables.

Now for the fun part – making it usable.
The xml table which loads last is basically just a header record and does not provide anything useful, so you can comment that out or just delete it from the script.

row contains all of your rows of data, and if you want to just run with that, you are fine – except the field names will not match what is displayed in SharePoint.That data is in the AttributeType table that it brought in.

The load for the AttributeType table looks like this – with one piece that I added at the end

AttributeType:
LOAD name,
[rs:name],
[rs:number],
[datatype/dt:type] as [dt:type],
[datatype/dt:maxLength] as [dt:maxLength],
[datatype/dt:lookup] as [dt:lookup],
%Key_xml_98C49F546A18E856 // Key to parent table: xml
FROM [http:[sharepointURL]/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=[ListGUID]&View=[ViewGUID]] (XmlSimple, Table is [xml/Schema/ElementType/AttributeType]);

FieldNameMap:
Mapping LOAD
name,
[rs:name]
Resident AttributeType;

This creates a map for the underlying field name to the display name.

To rename the fields in bulk turns out to be simple

RENAME Fields using FieldNameMap;

The remainder of the script

DROP Table AttributeType;
RENAME Table row to mySharePointData;
DROP Field %Key_xml_98C49F546A18E856;

Renaming all of the fields with a map will rename any field that it has a translation for, but pass through any of the ones it doesn’t recognize. If there are any fields that you don’t want the map to touch such as a field that will create synthetic keys, all you have to do is tweak the code for row and rename the field on the way in to something that is not in the map.

About escaping the GUIDs:

i did not have to unescape the GUIDs that I got from the SharePoint admin. If you are not familiar with that term, it means restoring the dashes and brackets to the URL. The { character gets converted to %7B, } becomes %7D, and – becomes %2D. I have seen some posts that insist you have to change them back, but that was not my experience.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s