Date:         Fri, 23 Jun 2006 12:59:31 -0700
Reply-To:     "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Subject:      Re: How to change .xls files into .txt files in SAS
Comments: To: SUBSCRIBE SAS-L Chandra Gadde <ddraj2015@GMAIL.COM>
In-Reply-To:  A<200606231918.k5NFP5Sh008299@mailgw.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Hi Gadde,

SAS can read the directory, we can filter on
the file extension, we can invoke a macro to
repeat the commands on each name, and voila!

NOTE: If it takes 15mins to open your large
Excel files then this will still take that
long.  The only thing this saves is your
opening and closing and SaveAs of the Excel
files manually.

CAUTION: The SAS import&export defaults may
yield a slightly different output layout than
the direct Excel SaveAs, so check your results.




%let MyPath=D:\yourdirwithxls;

data mydir;
    length filename dir fullpath newpath$ 256;
    dir      = "&mypath";
    rc       = filename("readdir","&mypath");
    did      = dopen("readdir");
    memcount = dnum(did);
    do i = 1 to memcount;
      filename = dread(did,i);
      fullpath = trim(dir) !! "\" !! filename;
      newpath = substr(fullpath,1,length(fullpath)-4)||'.txt';
      if substr(reverse(lowcase(trim(filename))),1,4)
        eq 'slx.' then output;
    end;
    rc=dclose(did);
    drop rc did memcount i;
run;

%macro convert(theXlsNm,theTxtNm);
  PROC IMPORT OUT=theTmpDS
     DATAFILE="&theXlsNm"
     DBMS=EXCEL2000 REPLACE;
     GETNAMES=YES;
  RUN;
  PROC EXPORT DATA=theTmpDS
     OUTFILE="&theTxtNm"
     DBMS=TAB REPLACE;
  RUN;
%mend;

option mprint;
data _null_;
    length stmp $2000;
    set Mydir;
    stmp = '%convert('||trim(FullPath)||','||trim(NewPath)||');';
    *put stmp=;
    call execute(stmp);
run;





Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing







-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
SUBSCRIBE SAS-L Chandra Gadde
Sent: Friday, June 23, 2006 12:18 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: How to change .xls files into .txt files in SAS


Hi Mark,

I have several large files that I couldn't even open one file at a time.
It is taking 15 minutes for me to open each file. So I was thinking of
importing them into SAS and send them back as txt files. Or is there any
other idea for this? Of course I have more than 100 xls files in that
directory. But all I need is 40 files to be converted into txt files.



Hi Gadde,

Can you just open them in Excel,
do a SaveAs, change the filetype
to .txt and click OK?

or do you have so many files that
you want to automate this process?

Are you wanting SAS to find all the
.xls files in a directory and then
convert and output them?  SAS can
do that.

and if you have so many files, we
have to ask if these are of "like"
things, and would it not be better
to have them consolidated, but of
course we have no idea what you
circumstances are.




Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing






-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
SUBSCRIBE SAS-L Chandra Gadde
Sent: Friday, June 23, 2006 11:57 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: How to change .xls files into .txt files in SAS


Hi All,

I have several excel files saved in a directory. How can I change all
the excel files into .txt files in SAS? Is there a way to do that? I
think we can import one file at a time into SAS and then send them back
as text files. Please help me if there is a possibility to do this.

Thanks for the help

Gadde