Loading data from Sharepoint list

[alec1982]

Hi,

anybody knows how to load data from a sharepoint list using the URL or any method.

Thxs,

[alec1982]

I was able to load data from SharePoint using the web Files laod from Edit Script.

I entered the URl of the sharepoint list and and loaded it.

It is working normally as long as no changes are made to the list on sharepoint.

I tried to add a ew Item on the SharePoint and loaded again. it faild..

Somehow the name of the tble changes once I add a new item to the sharepoint site.

Any idea on what should I do to have it working perfectly.

Thxs,

[Anonymous]

You can load a list via.

LOAD

    ows_aaaaa,

    ows_bbbbb,

    %Key_xml_98C49F546A18E856    // Key to parent table: xml

FROM [http://<path to list>/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={EAAAA340-01EF-40B6-8AB7-8BD5DF076947}] (XmlSimple, Table is [xml/data/row]);

in the script

The tricky thing is to get the Listname: {EAAAAA.....

My way to do this is link the List to Excel, then look at the connection properties to get the Listname:

<LIST><VIEWGUID>{A75A8452-A561-4CFB-92B1-9D02E292320C}</VIEWGUID><LISTNAME>{EAAAA340-01EF-40B6-8AB7-8BD5DF076947}</LISTNAME><LISTWEB>http://<path to list>//_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><ROOTFOLDER>/sites/germany/coll/wup/IT/Lists/Dell Bestellungen</ROOTFOLDER></LIST>

Once you have the full URL, just link a webfile as XML in Qlikview. You will get fieldnames like ows_xxxx and just need to rename.

I hope this helps.

Regards

Norbert

[alec1982]

Hi,

Thank you for the replay.

I have somequestions about the query:

where could I get the Key to parent table:

%Key_xml_98C49F546A18E856

// Key to parent table: xml

May you please give me more direction specially on how to connect to excel.

Thxs,

[alec1982]

I was able toconnect to excel and i have now the List name and Path:

<LIST><VIEWGUID>{C4726304-D839-46E4-8BD5-F96B0E79F00D}</VIEWGUID><LISTNAME>{AACA82B9-55E0-4BFC-BC5F-745CE1B4AC04}</LISTNAME><LISTWEB>

I am still confused on how to build the query.

I appreciate your help if you could.

Thxs,

[Anonymous · Accepted solution]

Alec,

open the script, click on "Web Files...." and enter the URL
you got from the instruction above into the "Internet File" field.

Click next and then select File Type "Xml" and press finish.

QV then creates the complete structure for you.

Regards

Norbert

[alec1982]

I love it!!

you made my day.

Thxs so much

Badr

[alec1982]

Hi,

Thanks again for your help.

I was wondering if this method would work on external SharaPoint sites.

I have tested it on our server and everything seems to work fine.

Now let's assume the List is coming from another compay(another sharepoint site), would it work?

I didn't see anything related to credentials? don't we need them?

Again thank you so much for your help. and hopefully you will have time to help me.

Alec,

[Anonymous]

Alec, unfortunately I have no experience with access to external sites. What I would try to do linking the list with Excel and see if Excel puts something inside the connection string. I depends how the external website is asking for the credential and if there is a chance to pass the ID and password via the URL.

Regards

Norbert

[Anonymous]

Alec, have a look here:

http://community.qlik.com/docs/DOC-1308

There are perhaps other solutions in the Connectivity blog.

Regards

Norbert