topic Re: Read excel file from FTP in SAS Programming

Read excel file from FTP

Saravanan — Thu, 20 Mar 2014 20:56:07 GMT

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

Re: Read excel file from FTP

Kurt_Bremser — Fri, 21 Mar 2014 06:17:16 GMT

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.

Re: Read excel file from FTP

Saravanan — Fri, 21 Mar 2014 20:36:34 GMT

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

Re: Read excel file from FTP

jakarman — Sat, 22 Mar 2014 06:19:32 GMT

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.

Re: Read excel file from FTP

Kurt_Bremser — Mon, 24 Mar 2014 07:46:48 GMT

On which platform are you using SAS?

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

Re: Read excel file from FTP

Saravanan — Mon, 24 Mar 2014 12:34:21 GMT

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.

Re: Read excel file from FTP

Kurt_Bremser — Mon, 24 Mar 2014 13:04:16 GMT

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"

Re: Read excel file from FTP

jakarman — Mon, 24 Mar 2014 13:24:54 GMT

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-anywhere/

Re: Read excel file from FTP

Kurt_Bremser — Mon, 24 Mar 2014 13:28:31 GMT

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

Re: Read excel file from FTP

jakarman — Mon, 24 Mar 2014 13:49:49 GMT

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.

Re: Read excel file from FTP

Saravanan — Mon, 24 Mar 2014 18:25:09 GMT

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

Re: Read excel file from FTP

jakarman — Mon, 24 Mar 2014 18:38:22 GMT

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

Re: Read excel file from FTP

Saravanan — Mon, 24 Mar 2014 18:53:11 GMT

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?

Re: Read excel file from FTP

jakarman — Mon, 24 Mar 2014 19:32:59 GMT

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.

Re: Read excel file from FTP

Saravanan — Mon, 24 Mar 2014 20:19:24 GMT

Jaap, we do not have a dedicated platform administrator for SAS server. Well, I have administrative privileges on server and I am very much a beginner in terms of SAS administration. Not sure, what details you are looking for to understand the environment and issue.

Re: Read excel file from FTP

jakarman — Tue, 25 Mar 2014 06:09:50 GMT

As you do not have that guy and it is you as a selfy, well . Let us ask:
- as there are no additional controls in place you data is not very sensitive... correct?

- You are the person for the server-side (W-8), the desktop installation and the processing uasag... correct?

- You have access to the server side installationm but someone else has run the sas-setup program... correct.?

There should be some guidelines how to work woht SAS procesess on the server.

- With Eguide are you using agroup account or personal AD domainaccounts

- the location of the SAS-work and the SAS-user datasets

- the way the used AD domain are used in getting access data within the Windows-domain.

Is Windows remote desktop to the server by all your users allowed.

Is the common windows user-profile available on the server or is that machine completely isolated

- I assume the ACE interface and the access to pc-files are available on the W8 server. correct?.

With that all you can make the decisions

- Where your users have to place their data (guidelines)

- Do you allow x-cmd or not. When the OS-security is sufficient and all all running by personal keys there should be no reason to let it have blocked. The changen can be found wihtin the launch-properties of the workspaceserver definitions. Do a restart of the objectspawner afterwards.

Pffff.. (just preparations)..

Get back to Kurts code.

The C:/temp is a fake name, you are required to change that to an acceptable one for your environment.

His approach wiht ftp commands and using X will have better options to run.

However, I would prefer the SAS (s)FTP acces method with filenames

Re: Read excel file from FTP

Kurt_Bremser — Tue, 25 Mar 2014 06:23:18 GMT

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

Re: Read excel file from FTP

Kurt_Bremser — Tue, 25 Mar 2014 06:26:33 GMT

PS do not despair. I was in your shoes some 10 years ago.

I have the luxury of running SAS on a _real_ server OS (AIX), though ....

Re: Read excel file from FTP

Saravanan — Wed, 26 Mar 2014 02:12:29 GMT

Jaap and Kurt, Thank you so much for all the inputs. I have not tried these suggestions yet. will try it and get back with my findings. This is both interesting and challenging. Keeping my fingers crossed.