DATA Step, Macro, Functions and more

How to read data from the SharePoint

Reply
N/A
Posts: 0

How to read data from the SharePoint

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
SAS Super FREQ
Posts: 8,864

Re: How to read data from the SharePoint

Posted in reply to deleted_user
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
Contributor
Posts: 35

Re: How to read data from the SharePoint

Posted in reply to Cynthia_sas

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?

Occasional Contributor
Posts: 16

Re: How to read data from the SharePoint

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; 

Regular Contributor
Regular Contributor
Posts: 170

Re: How to read data from the SharePoint

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.

Occasional Contributor
Posts: 16

Re: How to read data from the SharePoint

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.

Regular Contributor
Regular Contributor
Posts: 170

Re: How to read data from the SharePoint

Randy;

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

Lawrence

Super Contributor
Posts: 291

Re: How to read data from the SharePoint

Posted in reply to deleted_user

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;

Super User
Super User
Posts: 7,039

Re: How to read data from the SharePoint

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

Regular Contributor
Regular Contributor
Posts: 170

Re: How to read data from the SharePoint

I have SAS running on my machine.

Super User
Super User
Posts: 7,039

Re: How to read data from the SharePoint

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.

sharepoint.gif

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.

Regular Contributor
Regular Contributor
Posts: 170

Re: How to read data from the SharePoint

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

Looking at Plan B right now.

Lawrence

Super Contributor
Posts: 291

Re: How to read data from the SharePoint

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!

Regular Contributor
Regular Contributor
Posts: 170

Re: How to read data from the SharePoint

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!

Ask a Question
Discussion stats
  • 13 replies
  • 10541 views
  • 1 like
  • 7 in conversation