Read excel file from FTP

[Saravanan]

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

[Kurt_Bremser]

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.

[Saravanan]

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

[Kurt_Bremser]

On which platform are you using SAS?

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

[Saravanan]

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.

[Kurt_Bremser]

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"

[Saravanan]

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";

[jakarman]

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.

[jakarman]

or copy file binary using SAS

http://blogs.sas.com/content/sasdummy/2011/06/17/how-to-use-sas-data-step-to-copy-a-file-from-anywhe...

[Kurt_Bremser]

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

[jakarman]

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.  

[jakarman]

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

[Saravanan]

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?

[Kurt_Bremser]

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

[jakarman]

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.