Desktop productivity for business analysts and programmers

Reading data from a FTP site (.XLS file)

Reply
Occasional Contributor
Posts: 12

Reading data from a FTP site (.XLS file)

Hello All

I have a program which is running fine when I run local . We wan to schedule this program to automate the data sets. I'm not able to get all the field values correctly .

We have a .XLS file on a FTP server , we need to read data from that server using SAS enterprise guide create a SAS , using this in other programs.

Here is the sample and issue details..

FILENAME TEST11 FTP 'mydata.xls' lrecl=256

                CD='/EEXInterfaces/Data/'

                HOST='myhost'

                USER='anonymous'

                PASS='XXXX' ;

DATA REC11 ;

   INFILE TEST11;

   INPUT  Region $9.;

RUN;

PROC PRINT DATA=REC11;

RUN;

It's not printing values from region column in that excel. I have other columns also ..I think this is related to converting binary format to correct format which SAS can understand . Any idea .. It's urgent ..

Rgds

Jagadish

Valued Guide
Posts: 3,206

Re: Reading data from a FTP site (.XLS file)

more ideas than being sure on that as you gave too little information.

- The FTP protocol is doing sometimes unwanted conversions.  Usually related to crossing different systems (Unix/Windows)

- The xls file you are reading onlys  is readable with a datastep when it is really more like a csv-file

- running in batch is ususally done with different credentials they could be blocked for FTP (security)

All kind of combinations of these with variations.

What did run in EGuide this code? 

---->-- ja karman --<-----
Occasional Contributor
Posts: 12

Re: Reading data from a FTP site (.XLS file)

Thanks. As per my knowledge nothing is blocked to retrieve data from FTP site. Yes the same code (adding actual location of the file)I ran in EG . I see there is a problem in displaying that data once it's read from XLS.

Any idea / thoughts..

Valued Guide
Posts: 3,206

Re: Reading data from a FTP site (.XLS file)

And the code in EG is giving the error?

- Checked whether it is CSV type named as XLS (that could work) or is the binary 2003 Excel type or the zipped/xml one known as .xlsx?   
- Checked the possible translations in FTP? (binary and no record separation are often special parms)   
You could do a binary download and then convert the file as usual. Two steps but more easy to validate.

---->-- ja karman --<-----
Occasional Contributor
Posts: 12

Re: Reading data from a FTP site (.XLS file)

No errors , but data displayed is not correct . I want to see the data which is there on the first column ( Region) but it's displays some values from other columns.

It's .xls format not CSV shows as xls. Can you please let me know the code how we can download binary and covert to file as usual.

I'm new to EG

Grand Advisor
Posts: 10,223

Re: Reading data from a FTP site (.XLS file)

I second Jaap's approach of downloading the file. You may need it for documenting when format changes occur such as colum order changes/additions/deletions or trouble shooting why expected numeric values are character or even possible text encoding issues.

Valued Guide
Posts: 3,206

Re: Reading data from a FTP site (.XLS file)

this is one approach Copy a file using a SAS program: another method - The SAS Dummy

reading form maianfrem binary eplains a lot on different machines using (s)ftp. TS-DOC: TS-642 - Reading EBCDIC Files on ASCII Systems

When being allowed to used xcmd you could uses the often mandatory standard tools local in place.

Many more options as being a standard way. As you mentioning anonymous Is it public data (we could try) or local in house data?    

---->-- ja karman --<-----
SAS Employee
Posts: 340

Re: Reading data from a FTP site (.XLS file)

The above code was working locally with the same file? What was exactly the code?

Stange, that you can read an Excel file using a data step.

What doeas "run local" mean?

Do you have SAS installed on your (Windows?) desktop? Or you have only EG (so you were connected to a SAS server)? Is the server Windows? Do you have ACCESS to PC file on the server<

If this is real Excel data (not csv), use ftp binary transfer:

FILENAME TEST11 FTP 'mydata.xls' lrecl=256 binary

                CD='/EEXInterfaces/Data/'

                HOST='myhost'

                USER='anonymous'

                PASS='XXXX' ;

Ask a Question
Discussion stats
  • 7 replies
  • 1139 views
  • 0 likes
  • 4 in conversation