Convert Many XLS files to TXT using SAS?

Reply
Occasional Contributor
Posts: 15

Convert Many XLS files to TXT using SAS?

Convert Many XLS files to TXT using SAS?

Thanks.

Super User
Posts: 17,784

Re: Convert Many XLS files to TXT using SAS?

Yes you can convert many files to TXT using SAS. If you need further help beyond that you need to provide more details.

Respected Advisor
Posts: 3,777

Re: Convert Many XLS files to TXT using SAS?

You don't need SAS.  SAVEAS

You can write a shell script to automate it.

Occasional Contributor
Posts: 15

Re: Convert Many XLS files to TXT using SAS?

How do I write the script? I have several thousand files.

Super User
Posts: 17,784

Re: Convert Many XLS files to TXT using SAS?

Depends on your OS.

Googling shows a few answers:

vb.net - VB Script to convert excel to text exits with error code 9009 - Stack Overflow

Even if you wanted to use SAS you'd basically be using it to call a script as above.

Contributor
Posts: 29

Re: Convert Many XLS files to TXT using SAS?

google sas macro DIRLISTWIN. it is a sas provided macro.

then

%let SASPath=%substr(%sysget(SAS_EXECFILEPATH),1,%eval(%length(%sysget(SAS_EXECFILEPATH))-%length(%sysget(SAS_EXECFILENAME))));

%dirlistwin(&saspath);

*makes import file;

filename import "&SASpath.\forimport.sas";

data _null_;

set report end=eof;

retain i 1;

if index(filename,"xls") ne 0 then do;

  file import;

  string= 'proc import datafile="'||catx("\",strip(path),strip(filename))||'" out=data'||strip(i)||' dbms=xls; guessingrows=10000; run;';

  string2='proc export data=data'||strip(i)|| out='strip(filename)'.txt dbms=tab; run;';

  put string;

     put string2;

  i+1;

end;

run;

*runs import file;

%inc "&SASpath.\forimport.sas";

New Contributor
Posts: 2

Re: Convert Many XLS files to TXT using SAS?

I recommend PowerShell as the ideal solution. According to Douglas Finke, author of Windows PowerShell for Developers, PowerShell has 348 Excel functions.  The 21Jul2011 post on The Hey, Scripting Guy! Blog gives a solution.  Yeah, PowerShell

scripting is hard, harder than Unix shell scripting. Below, I wrapped a FOR loop around five lines of code copied from www.experts-exchange.com.

The attachment named singlexl.txt should be renamed singlexl.ps1. Tested code but does not handle multiple workbooks. Input spreadsheets must be named spread1.xlsx, spread2.xlsx and spread3.xlsx. Execute by type .\singlexl.ps1 at the PowerShell prompt.

Attachment
Super Contributor
Posts: 275

Re: Convert Many XLS files to TXT using SAS?

If You MUST use SAS, try this:

%macro excel_to_txt(inpath,outpath);

data _null_;

infile "dir/b ""&inpath.\*.xls"" " pipe  truncover;

length filename $20.;

input;

filename=scan(_infile_,1);

call execute(

'proc import out='||filename|| 'datafile="%bquote(&inpath.)\'||strip(filename)||'.xls " dbms=xls replace;

run;

proc export data='||strip(filename)|| ' outfile="%bquote(&outpath.)\'||strip(filename)||'.txt " dbms=tab replace;

run;'

);

run;

%mend;

Super User
Posts: 6,932

Re: Convert Many XLS files to TXT using SAS?

Do it with a shell script.

Do it with a shell script.

Do it with a shell script.

What I tell you three times is the absolute truth. IMHO Smiley Wink

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 2

Re: Convert Many XLS files to TXT using SAS?

Yeah, why would anyone want to use SAS? I have Linux and Windoze shells available to me.

While xlsx2csv (https://github.com/dilshod/xlsx2csv) is not on the Linux system I use, I know I can convert spreadsheets to CSV using Windows PowerShell.

Ask a Question
Discussion stats
  • 9 replies
  • 1283 views
  • 2 likes
  • 7 in conversation