Excel files from Share point

[mohan2391]

Hi Friends,

I need to get a few files from a share point into QlikView script. How to do that ?

I have studied the information at the followed link

Reading Data from Sharepoint lists into QlikView‌

But I didn't get what to do and how to do as I'm new here.

Please help me step by step..

Thank you for your support

[ramasaisaksoft · Accepted solution]

I prepared in a detailed format for my POC may be it will help to u.

Go to SharePoint Site

Ex:-https://xxxxxxx.com/sites/00115/Lists/Delay%20Log/AllItems.aspx]

Right click on the page View Source Code

CTRL+F for List=

Ex:-

List={77d0fb%2D5b61%2D4f38%2D97d1%2Dc5ccc8369b93}

Again CTRL+F for View=

Ex:-

View=%7bE151BDA-FA52-49C8-B338-516018F78B0F%7d

Keep it these 2 values some where

As per the below Qlikview Community link

https://community.qlik.com/docs/DOC-1308#

Using RPC-Calls

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.

We need to change this link

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=listGUI...

Ex:-

https://xxxxxx.com/sites/00115/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={77d7fb%2D5b61%2D4f38%2D97d1%2Dc5ccc8369b93}&View=%7bE91BDA-FA52-49C8-B338-516018F78B0F%7d

Note:-

Here List={………}&View=%........

we already took from source file

Now you need to go to Qlikview

1. Ctrl+Eàscript
2. Click on Web Files àInternet file address

you need to give the address as you already created

https://xxxxxx.com/sites/00115/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={77d7fb%2D5b61%2D4f38%2D97d1%2Dc5ccc8369b93}&View=%7bE91BDA-FA52-49C8-B338-516018F78B0F%7d

Select source format as XML

Then select XML/DATA/Row in your Tables tab on right hand side.

Here you can see the entire data as it is in SharePoint.

Click on Finish Button.

Then your rows will visible like

LOAD ows_ID,
ows_Dep,
ows_Date_x0020_Issue_x0020_happd,
ows_Issue_x0020_Tipe,
ows_Issue_x0020_Caty,
ows_ProblemDesc,
ows_Notification_x0020_Numb,
ows_TotalHrs,
ows_Issue_x0020_Status,
ows_ContainmentAction,
ows_IssueOwner,
%Key_xml_98C49F546A18E856    // Key to parent table: xml
FROM

[https://xxxxxx.com/sites/00115/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={77d7fb%2D5b61%2D4f38%2D97d1%2Dc5ccc8369b93}&View=%7bE91BDA-FA52-49C8-B338-516]

[HirisH_V7]

Hi,

Look into this,

How Qlikview read data from Sharepoint?