topic Re: How to read data from the SharePoint in SAS Programming

How to read data from the SharePoint

deleted_user — Tue, 15 Dec 2009 06:29:50 GMT

Hi All,

When I try to extract the data from the SharePoint using 'URL' option of 'FILENMAE' statement I'm getting an error : Unauthorized access........., but I can able to access the same data through MS Access and MS Excel.

Could any one please let me know, if you have solution to this.

Thanks in advance.
Narasimha

Re: How to read data from the SharePoint

Cynthia_sas — Tue, 15 Dec 2009 16:36:54 GMT

Hi:
If you search on support.sas.com with the string
access SharePoint SAS

You do come up with quite a few hits. Most of them are related to the SAS Enterprise Intelligence Platform (also known as the BI Platform).

If searching on support.sas.com doesn't provide useful information, you might wish to work with Technical Support on this question. Generally speaking, SharePoint documents could be -anything- a Word doc, an HTML page, a PPT presentation, an Excel spreadsheet, a text file. The URL option of the FILENAME statement is most often used to access an HTML file and read an HTML table out of the HTML file and INTO a SAS dataset or to read a text file, as described here:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000223242.htm

If you are trying to publish stored process results to SharePoint, instead of to the Xythos webdav repository, you really do need to work with Tech Support.

cynthia

Re: How to read data from the SharePoint

rab24 — Thu, 28 Jul 2011 03:20:58 GMT

I think I am in the same boat as Narasimha. I followed the documents on connecting Sharepoint and SAS to extracts lists, but was rewarded with an Unauthorized access message to the URL. From that, I found a 2005 note http://support.sas.com/kb/15/692.html that indicates that Filename URL won’t work with the authorization issue I encountered.

Since some of the sample documentation (e.g., SAS Global Paper 390-2009) shows Filename URL working, I am wondering if this indicates that my Sharepoint install is not as basic as I thought or the samples used a program that I don’t have. Can anyone tell me if the functionality for import direct from Sharepoint is only intended to work with Web Parts or for users with a certain level of security?

Re: How to read data from the SharePoint

RD2 — Thu, 28 Jul 2011 10:37:53 GMT

I use following code to download a xls file from sharepoint through proxy server

filename out "C:\temp\testout.xls";

proc http

in=in

out=out

url="http://collaboration.test.xyz.net/cnrd/cd/cdstudycode/XXX/Structure.xls"

method="get"

proxyhost="xxx.xx"

proxyusername="xxx"

proxypassword="xxx"

proxyport=8080;

run;

Re: How to read data from the SharePoint

_LB — Wed, 19 Oct 2011 22:12:14 GMT

Randy,

Can you tell me how you figured out what the url is of the xls format. Whenever I attempt to figure it out, it exports out but I can't figure out the URL.

Thanks.

Re: How to read data from the SharePoint

RD2 — Thu, 20 Oct 2011 02:56:42 GMT

In your browser, move your mouse to the correspond EXCEL file icon, right click mouse, and select 'Copy link address'. Then you can copy the full url from clipbord.

Re: How to read data from the SharePoint

Bill — Thu, 20 Oct 2011 15:59:05 GMT

The code below works for me ...

PROC IMPORT OUT= WORK.SHAREPOINTIMPORT

DATAFILE= "\\sharepoint02\Technology\Reports\Quality\CPQ.xls"

DBMS=EXCEL REPLACE;

SHEET="AUTOX";

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

Re: How to read data from the SharePoint

_LB — Thu, 20 Oct 2011 17:01:01 GMT

Randy;

Unfortunately I do not seem to have the same menu options. However, Thanks for the tip.

Lawrence

Re: How to read data from the SharePoint

Tom — Thu, 20 Oct 2011 17:04:40 GMT

Unfortunately that method will only work if you are running SAS on your Windows machine.

Re: How to read data from the SharePoint

_LB — Thu, 20 Oct 2011 17:06:14 GMT

I have SAS running on my machine.

Re: How to read data from the SharePoint

Tom — Thu, 20 Oct 2011 17:18:45 GMT

Then the question with this syntax is whether your Sharepoint server allows you to view the document folder as if it was a actual network folder. On the sites I have used you can access a document library as if it was a network folder by selecting Open in Windows Explorer from the Actions menu. See image below.

I assume that is a feature that the site owner can turn on or off depending on the security settings that they need, but the details of how are beyond me.

Re: How to read data from the SharePoint

_LB — Thu, 20 Oct 2011 17:50:24 GMT

Yea, the permissions are set by IT staff of which I have no control over.

Looking at Plan B right now.

Lawrence

Re: How to read data from the SharePoint

Bill — Fri, 21 Oct 2011 13:56:58 GMT

You may not be able to "control" the settings, but you can ask to have them changed. IT is (should be) there to help us to do our jobs, not to create barriers!

Security settings can be very narrowly defined so that you only get as much rope as you need to do the specific task. If you don't ask, you won't get!

Re: How to read data from the SharePoint

_LB — Fri, 21 Oct 2011 22:08:12 GMT

Bill,

You are right, if you don't ask, you don't get.

Found a much easier solution actually. Our sharepoint allows (some users) to save as an Access 2007-10 DB with a linked table to sharepoint.

Solves the problem!