[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: Importing Excel file using URL engine ?
From:       "Richard A. DeVenezia" <radevenz () IX ! NETCOM ! COM>
Date:       2004-02-29 14:08:09
[Download RAW message or body]

"Chang Y. Chung" <chang_y_chung@HOTMAIL.COM> wrote in message
news:200402282044.i1SKiBf27648@listserv.cc.uga.edu...
> >On Sat, 28 Feb 2004 01:25:03 -0500, oht3075@HOTMAIL.COM wrote:
> >
> >>Do anyone has experience importing Excel file from a web site ?
> >>I use the following code but do not work.
> >>
> >>Filename fi url 'http:\\.....\file1.xls';
> >>
> >>Proc import in=fi out=work.one dbms=excel;
> >>run;
> >>
> >>I got error saying cannot read file. Anyone has a better idea ?
> >>
> >>Thanks
> >>
> >>David
>
>
> On Sat, 28 Feb 2004 14:29:52 -0500, Howard Schreier
> <Howard_Schreier@ITA.DOC.GOV> wrote:
>
> >I have more experience with the FTP access method than with the URL
access
> >method (which, by the way, probably should be called the HTTP access
> >method), but there are some parallels.
> >
> >The goal here is to download and import in one step, rather than making a
> >local copy of the remote file and importing from the copy.
> >
> >That generally works when the file structure is sequential. Native Excel
> >files are not sequential.
> >
> >Web browsers render HTML progressively as it is received, record by
> record.
> >Sometimes when the HTTP hangs for a moment on a long file, you can see
> that
> >you have a partial page in the window. But when a web browser handles an
> >Excel file (using a plug-in or OLE or whatever), it first downloads the
> >file into its disk cache and then renders from that copy. I think that
> will
> >have to be the strategy here as well.
> >
>
> Hi, David,
>
> Following Howard's suggestion (which always a good thing to do, by the
> way), I was able to make the following work.
>
> It is inefficient since it reads one byte at a time -- it also has a
> problem with a large file, since there is a limit on how big the lrecl on
> (in)file statement can be). But for a small files, it is good to know that
> it works -- just in case...
>
> HTH.
>
> Cheers,
> Chang
>
> <sasl:code>
> /* this code downloads "test.xls" file on a website through http protocol
> from sas and store it locally as test-local.xls. The file size of the file
> is 17408 bytes. Tested with sas 8.2 on WinXP */
>
> filename remote url "http://www.changchung.com:80/g/test.xls" recfm=s
> debug;
>
> data _null_;
>  nbyte=1;
>  infile remote nbyte=nbyte end=done lrecl=999999;
>  file "d:\chang\g\test_local.xls"  lrecl=999999;
>  do while (not done);
>   input;
>   put _infile_ @;
>  end;
>  stop;
> run;
>

Chang:

Here is a version that should work with any size file (@@ might be overkill,
but it still works:).
A small tweak might be possible  to process a sequence of files read using
MGET.

%let remote_source_fileref = ftp_src;
%let local_destination_fileref = lcl_dst;

%let remote_file = patridge.src;

filename &remote_source_fileref
  ftp
  "&remotefile"
  mprompt
  host='ftp.sas.com'
  user='anonymous'
  pass='me@my.email.com'
  cd='/observations/obswww15'
;

filename &local_destination_fileref
  "%sysfunc(pathname(WORK))/&remotefile."
;

%let buffer_size = 2000;

data _null_;
  infile &remote_source_fileref     recfm=s lrecl=&buffer_size. length=len
nbyte=_nbyte_;
  file   &local_destination_fileref recfm=n recfm=n ;
  input ;
  put _infile_ $varying. len @@;
run;

--
Richard A. DeVenezia
http://www.devenezia.com/downloads/sas/macros

[prev in list] [next in list] [prev in thread] [next in thread]