Read excel file from FTP

Reply
Contributor
Posts: 60

Read excel file from FTP


Hi All,

I`m trying to read an excel file from FTP. I cannot succeed in doing so. following is the code I use,

filename xlremote ftp 'test.xlsx'  host='ftp.mycompany.com' cd='/somepath/path/' user='username' pass='password' debug;

proc import datafile=xlremote out=test dbms=excel replace; run;

I get the following error when I run the above code

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

Thanks,

Saravanan

Super User
Posts: 6,942

Re: Read excel file from FTP

Do as you are told. Copy the file externally (use a scripted ftp with the x statement), and then run proc import.

I think that proc import makes use of Microsoft Data Access Objects which need to see the file locallly.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 60

Re: Read excel file from FTP

I`m sorry. I do not understand it. can you please provide me a sample code? Thanks.

Super User
Posts: 6,942

Re: Read excel file from FTP

On which platform are you using SAS?

Windows ftp does have its quirks compared to "standard" ftp on a UNIX platform.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 60

Re: Read excel file from FTP

I`m working on Windows platform. I`m looking to read the file from FTP directly or download the file using SAS to local system.

Super User
Posts: 6,942

Re: Read excel file from FTP

begin with:

data _null_;

file 'C:\temp\ftp.script';

put "open ftp.mycompany.com";

put "username"; /* supply the username in plain text here */

put "password"; /* supply the password in plain text here */

put "cd /somepath/path";

put "lcd where-you-want-the-file-to-end-up"; /* (eg c:\temp) */

put "bin";

put "get test.xlsx";

put "quit";

run;

x "ftp -s c:\temp\ftp.script";

x "del c:\temp\ftp.script";

the script file is necessary because it's (next to) impossible (AFAIK) to pipe the commands into the windows ftp.exe

now you can run the proc import on c:\temp\test.xlsx

edit: had to change how user and password are supplied, added "bin"

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 60

Re: Read excel file from FTP

I tried running this piece of code. Below are the errors I got. I believe these errors are due to missing ftp.script file? if yes, how can I go about writing the script file? example code would be great as I have no experience in writing scripts.

ERROR: Physical file does not exist, C:\temp\ftp.script.

ERROR: Shell escape is not valid in this SAS session.

x "ftp -s c:\temp\ftp.script";

ERROR: Shell escape is not valid in this SAS session.

x "del c:\temp\ftp.script";

Valued Guide
Posts: 3,208

Re: Read excel file from FTP

Kurt assumption is correct. The current implementation of the SAS-Excel interface is  using microsoft ACE driver.
By that the file needs to be local present on a Windows-machine. ACE has several limitations as originating from excel 2003.

That is is bit shamefull as the standard of xlsx is essential a Zip-file.containging some Xml files and more files. There is no need for using ACE anymore.
The Excel type xlslx is well documented at OASIS.

---->-- ja karman --<-----
Valued Guide
Posts: 3,208

Re: Read excel file from FTP

Super User
Posts: 6,942

Re: Read excel file from FTP

I just suspect that reading a file byte-wise may be slow as drying paint Smiley Wink

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 3,208

Re: Read excel file from FTP

Kurt agree, Now it is hoping for not too big data and efficient buffering/caching.

A better approach would by 4K records except the last one (variable) connecting to TCP-IP buffering

The best one would be not being dependent on Microsoftdrivers like ACE. Direct reading the xlsx file.

The best performance on IO time is not having (or as less as reasonable possible) IO.  

---->-- ja karman --<-----
Valued Guide
Posts: 3,208

Re: Read excel file from FTP

Saravan, is your SAS session running under Windows? We would have expected that.

The messages (shell escape)  are giving the impression you are on a server-side probably a Unix version

As of sas 9.3 the default would be that x-commands are allowed on you windows SAS desktop

---->-- ja karman --<-----
Contributor
Posts: 60

Re: Read excel file from FTP

Hi Jaap,

I am running EG5.1 on my client desktop which is Windows 7 professional, I connect to SAS 9.3 housed on a server which is running on Windows server 2008 R2 enterprise. Am I missing something?

Super User
Posts: 6,942

Re: Read excel file from FTP

In this case, you can either make sure that the windows server has access to the excel file via AD, or use the ftp transfer as in the example I gave you.

But to use ftp you first have to modify the default behaviour of the SAS workspace server that does all the work for your Enterprise Guide.

By default, no shell escapes are allowed in workspace server sessions right out of the box.

I hope this works for you (did that quite some time ago, SAS 9.2):

Start Management Console and log in as sasadm when connecting to the metadata server.

Search for your workspace server definition:

Plug-Ins - Server Manager - SASApp (the name you see as server in EG) - Logical Workspace Server - Workspace Server

Right Click on Workspace Server, select Properties and then Options, you will see the script that is used to start the server instance. Click on Advanced Options (or whatever it is called, it is "Erweiterte Optionen" in German). In Startup Properties you need to activate "allow XCMD")

To put this into effect, you then have to restart the Object Spawner on this machine, you can do this via the MC

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 3,208

Re: Read excel file from FTP

Well we were missing something not knowing your environment.

SAS is not running on your desktop, the view URLs autorisations are not necessary the same.

Running Microsoft  interfaces on the server are requiring they are present on that side.

Eguide has good interfaces to the desktop but should be seen as a different approach.

Do you have more details on your issue?

Who is your platform administration at the server?

Does he accept SAS or is it something of an other world for him.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 18 replies
  • 2321 views
  • 0 likes
  • 3 in conversation