Reading an Excel file from Sharepoint/Intranet with a URL address - how?

[deleted_user]

I posted this in the "Integrate with Microsoft Office" sub-forum, where I received 60 views but no replies. Perhaps the heavier traffic here will garner some ideas?

How would I import an Excel file with a URL address?

I'm using SAS 9.2 (Windows XP Pro) and Office 2007. We want to store some metadata Excel files on our Sharepoint site (to aid in audit trails, etc.) and grab them with our SAS programs to build on-the-fly subroutines. This works great when pulling the Excel files from a shared server with a regular path-style address, but when I run the example code below I get the error that PROC IMPORT won't work with URL files.

filename test url "https://myintranet.com/sites/project/test.xlsx";
proc import file=test out=testout dbms=excel replace; range='test'; getnames=yes; scantext=yes; run;

ERROR: This "filename URL" access method is not supported by "proc import". Please copy the file to local disk before running the procedure.

An "X" command won't work either because COPY/XCOPY/ROBOCOPY don't support URL addressing. I could do this with a UNC path, but the people who will actually run these job once they're fully developed will not (nor ever hope to) have privileges to use a UNC.

We could do a manual "open/save as" to a shared drive, but that really defeats the purpose of the automation.

Ideas?

Thanks!

[art297]

Take a look at: http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0312e&L=sas-l&D=1&O=A&m=167104&P=3830

or, in short form: http://tiny.cc/0w6mz

In that post, Paul Choate said that he successfully tried the following method:

FILENAME website HTTP

'http://www.iso-ne.com:80/settlement-resettlement/SMD_operating_reserves_sum

mary/200311_OR_Summary.xls';

data file;

  n=-1;

  infile website recfm=s nbyte=n length=len;

  input;.

  file  "200311_OR_Summary.xls" recfm=n;

  put _infile_ $varying32767. len;

  run;

PROC IMPORT OUT= WORK.D200311_OR_Summary_NEMA

            DATAFILE= "200311_OR_Summary.xls"

            DBMS=Excel REPLACE;

      RANGE="A3:G80";

      sheet="NEMA";

      getnames=yes;

RUN;

HTH,

Art

[bentleyj1]

This afternoon I needed this very solution.  Thanks to both of you, Paul and Art.

John Bentley

[LainieH]

@bentleyj1

thank you for posting this! It made me smile to know that the original post, created in 2010 by SAS users, helped you this afternoon in 2014

[PreRiola]

Hi Arthur,

I would like to use your code to download xlsx from URL. The URL automatically prompts from the username and password so I included the ppuser= ppass= debug

However, I encountered the ERROR: The connection has timed out.. after the data step. Please assist.

16         FILENAME srm HTTP 'http://srm.edragonedit.com/stat/overall/download'  puser="srmall" ppass=XXXXXXX debug;

17        

18        

19         data file;

20           n=-1;

21           infile srm recfm=s

22         nbyte=n length=len;

23           input;

24           file  "srm_vouchers_overall.xlsx" recfm=n;

25           put _infile_ $varying32767. len;

26         run;

ERROR: The connection has timed out..

NOTE: UNBUFFERED is the default with RECFM=N.

NOTE: The file "srm_vouchers_overall.xlsx" is:

      Filename=D:\SAS\Config\Lev1\SASAppEM\srm_vouchers_overall.xlsx,

      RECFM=N,LRECL=256,File Size (bytes)=0,

      Last Modified=28Jul2015:17:06:01,

      Create Time=28Jul2015:16:36:20

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.FILE may be incomplete.  When this step was stopped there were 0 observations and 0 variables.

WARNING: Data set WORK.FILE was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

      real time           25.75 seconds

      cpu time            0.00 seconds

27        

28         PROC IMPORT OUT= WORK.srm_vouchers_overall

29                     DATAFILE= "srm_vouchers_overall.xlsx"

30                     DBMS=Excel REPLACE;

31               RANGE="A3:G80";

32               sheet="overall";

33               getnames=yes;

34        

35         GOPTIONS NOACCESSIBLE;

36         %LET _CLIENTTASKLABEL=;

37         %LET _CLIENTPROJECTPATH=;

38         %LET _CLIENTPROJECTNAME=;

2                                                          The SAS System                               15:31 Tuesday, July 28, 2015

39         %LET _SASPROGRAMFILE=;

40        

41         ;*';*";*/;quit;

1                                                          The SAS System                               17:06 Tuesday, July 28, 2015

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

Connection Failed.  See log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.32 seconds

      cpu time            0.04 seconds

[ArtC]

As an aside, depending on your phase of 9.2, you may not yet have support for MS Office 2007 (XLSX). If you have not already done so double check
http://support.sas.com/kb/32/455.html

[RD2]

Hi ArtC,

The File on share point/intranet is a read only file. I have read only right on that file. But my SAS Log throw following error:
"ERROR: Unauthorized to retrieve URL. Use the debug option for more info.".

Any one can help?

[ArtC]

Randy Dai,
I feel like we are cross threading here. Since your error is different than the OP, after checking the other responses here, you may wish to start your own thread. Include some code so that we can help with the diagnosis.

That said make sure that you are not requesting write access when you set up your fileref (access=readonly).

[RD2]

Thx Art, I use art297 code to do the test. That's why I not start a new thread. The problem I have is because of proxy server. See following link.
http://support.sas.com/dsearch?Find=Search&ct=&qt=FILEREF++%2B+URL&col=suppprd&nh=10&qp=&qc=suppsas&... Message was edited by: Randy Dai

[deleted_user]

Thanks, Art...I think we're getting close (sorry to be so long, got pulled into other things.) Here is my run log (cleaned up to protect client sensitivity):

1095 filename test2 URL
1096 "https://myintranet.com/sites/clients/CLIENT/projects/PROJECT/Shared Documents/Common
1096! Layout.xlsx"
1097 puser="myid" ppass=XXXXXXXXXX debug;
1098 data file2;
1099 n=-1;
1100 infile test2 recfm=s nbyte=n length=len;
1101 input;
1102 file "Common Layout.xlsx" recfm=n;
1103 put _infile_ $varying32767. len;
1104 run;

NOTE: >>> GET /sites/clients/CLIENT/projects/PROJECT/Shared Documents/Common Layout.xlsx HTTP/1.0
NOTE: >>> Host: myintranet.com:443
NOTE: >>> Accept: */*.
NOTE: >>> Proxy-Authorization: Basic cGllaGxtOjY5VWl6ODYz
NOTE: >>> Accept-Language: en
NOTE: >>> Accept-Charset: iso-8859-1,*,utf-8
NOTE: >>> User-Agent: SAS/URL
NOTE: >>>
NOTE: <<< HTTP_1.1 400 Bad Request

NOTE: <<< Content-Type: text_html
NOTE: <<< Date: Thu, 16 Dec 2010 20:02:22 GMT
NOTE: <<< Connection: close
NOTE: <<< Content-Length: 20
NOTE: <<< ERROR: Bad request. Use the debug option for more info.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.FILE2 may be incomplete. When this step was stopped there were 0 observations and 0 variables.

Overall, looks like it won't authenticate me (I used both user-pass and puser-ppass with the same results.) I do not believe we have a SAS Metadata server set up, but not sure that will help me either.

I'll also look into the suggestion that we're not set up for Office 2007 yet...SAS may just not recognize that file type using URL (or HTTP, same difference) although I read and write xlsx files practically every day, just not with a URL address.

The other approach that I'm looking at is getting a service account set up that can read files with a UNC path, eliminating the need to use the URL (nee HTTP) type. However, this somewhat negates the scalability of this solution, since our IT group wants to grant rights for specific files. Sigh...

Mike

P.S. @Randy - use the "debug" option on the filename statement (see my example, it provided the NOTES in my log.) Also, use the PUSER= and PPASS= options to pass your proxy server userid and password. Message was edited by: mpiehl