Data load from Sharepoint 2013

[orital81]

Hi all,

i am about to start a QV project based on Sharepoint 2013 data stored in SQL Server DB.

I have read some solutions how to reload data from sharepoint but all had to deal with 3rd party application or external .NET code.

the xmls are generated using infopath.

I will appreciate any simple suggestion to approach the data load from Sharepoint 2013.

Thanks a lot

Ori

[saumyashah90]

Data source in sharepoint is a list or an uploaded Excel?

[orital81]

I have both a list and an XML stored within infopath.

If you have solution for at least one of them that would help.

[#4]

n earlier versions of SharePoint most of the Qlikview developers used  Windows SharePoint Services Remote Procedure Call (RPC) methods to fetch data from a share point list.

http://Server_Name/[sites/][Site_Name/]_vti_bin/owssvr.dll?Cmd=Display&List=GUID&XMLDATA=TRUE

This method of extraction always required a GUID to fetch a list or a specific view. This posed difficulties in automating some of the extraction tasks.

With the launch of Sharepoint 2013 Microsoft provided REST(Representational State Transfer) interface which is a data service that allows you to use construct HTTP requests to query SharePoint list data. The main advantage here is that WCF Data Services uses the OData protocol for fetching the data. The data is returned using a well known XML format called ATOM. As a Qlikview developer you don't have to learn entire technical aspects of this new feature. All you have to do is to understand some of the very useful methods.

Some of the main advantages are given below.

1.  GUID is no longer required to fetch a list. Now we can fetch a list by the Title

Use the following format

http://server/site/_api/web/lists/getbytitle(‘Title’)/Items

2. We can fetch meta data about the list.

http://server/site/_api/web/lists/getbytitle(‘Title’)  will provide a XML with Metadata about list

For example

[content/properties/ItemCount]  will provide you the number of records in the list

3. OData protocol have much more powerful methods and Going forward ,Microsoft is positioning it as a preferred method of interaction.

Following are some of the available options

Option	Description
$expand	Directs that related records should be retrieved in the record or collection being retrieved.
$filter	Specifies an expression or function that must evaluate to ‘true’ for a record to be returned in the collection.
$orderby	Determines what values are used to order a collection of records.
$select	Specifies a sub set of properties to return.
$skip	Sets the number of records to skip before it retrieves records in a collection.
$top	Determines the maximum number of records to return.

Sample Script for Fetching data from Sharepoint 2013 List using ODate protocol

//Get number of Records from the list

ListCount:

LOAD

        Num([content/properties/ItemCount]) as ItemCount

FROM  [http://server/site/_api/web/lists/getbytitle(‘Title’) ] (XmlSimple, Table is [entry]);

//fetch the Row count

Let vListCount=  Peek('ItemCount',0,'ListCount')  ;

//Get List

//Please remember to use top method. If the top method with number of total rows is omitted the fetch might not return all the

//records in the share point list

List:

LOAD

      [content/properties/ID] as ID,

    [content/properties/Title] as Title,

    [content/properties/Modified] as Modified,

    [content/properties/Created] as Created

FROM [http://server/site/_api/web/lists/getbytitle(‘Title’)/Items?$Top=$(vListCount)] (XmlSimple, Table is [feed/entry]);

Hope this will help you in your quest for the next Qlikview solution. This method enabled us to automate fetching of lists with the same name from different share-point sites.

Wishing you all the best of up coming holiday season from your friends in Michigan

Pauljin Kochupyloth     | Sr. BI Consultant

WIT Inc :"HARNESS THE POWER OF BI"  http://www.witinc.com/

[saumyashah90]

Okay i am uploading a doc, this would surely help you