Help using Base SAS procedures

exportFromXL | Has any one used this Procedure?

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

exportFromXL | Has any one used this Procedure?

do any one know how to convert Excel sheets into Txt or CSv file, i searched internet and found one proc

exportFromXL but unable to use it, has anyone used this before or know of someother procedure?

 

thanks in advance

 


Accepted Solutions
Solution
‎07-06-2016 01:31 PM
Community Manager
Posts: 2,766

Re: exportFromXL | Has any one used this Procedure?

Here's a simple example of using VB Script to automate Excel and export a single sheet.

 

' constant that means "tab-delimited text"
Const txtFmt = -4158

WScript.Echo "Opening Excel"
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Visible = False
objExcel.Application.DisplayAlerts = False

Set objWorkbook = objExcel.Workbooks.Open("C:\temp\class.xlsx")
WScript.Echo "Saving as tab-delimited"
objExcel.ActiveWorkbook.SaveAs "C:\temp\class.txt", txtFmt, False
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit		

Assuming you save that in a script called saveastext.vbs, you would run with a command from Windows like:

 

cscript c:\test\saveastext.vbs

View solution in original post


All Replies
Community Manager
Posts: 2,766

Re: exportFromXL | Has any one used this Procedure?

I think you mean the SAS macro called exportFromXL by @NateDerby, which he contributed to SourceForge here.

 

I have not used it, but from what I read it uses DDE (dynamic data exchange) to automate Excel from SAS.  That mechanism works only when you have SAS for Windows and Microsoft Excel installed on the same machine.  So it won't work from SAS Enterprise Guide (usually) or with SAS that's installed on UNIX systems.

 

There are many ways of using SAS to export data that you've imported from Excel.  And there are ways of automating Excel (using VB Script or Windows PowerShell) to export CSV files, independent of SAS.

Contributor
Posts: 27

Re: exportFromXL | Has any one used this Procedure?

thanks Chris

 

can u share some toher ways that i can use?

 

my problem is that my business users are familar with Excel, so they gave me data in Excel only, but our SAS solution looks for Tab delimited files, so i have to convert the excel files before loading it into the solution, so looking for a simple steps t automate this step , i  am looking for a solution where in my Business users drop their excel files in one folder and then i run a utility to convert all the files to .txt files and the load it into SAS..

Solution
‎07-06-2016 01:31 PM
Community Manager
Posts: 2,766

Re: exportFromXL | Has any one used this Procedure?

Here's a simple example of using VB Script to automate Excel and export a single sheet.

 

' constant that means "tab-delimited text"
Const txtFmt = -4158

WScript.Echo "Opening Excel"
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Visible = False
objExcel.Application.DisplayAlerts = False

Set objWorkbook = objExcel.Workbooks.Open("C:\temp\class.xlsx")
WScript.Echo "Saving as tab-delimited"
objExcel.ActiveWorkbook.SaveAs "C:\temp\class.txt", txtFmt, False
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit		

Assuming you save that in a script called saveastext.vbs, you would run with a command from Windows like:

 

cscript c:\test\saveastext.vbs
Contributor
Posts: 27

Re: exportFromXL | Has any one used this Procedure?

can we also modify this script to accept excel work book with multiple sheets?

Community Manager
Posts: 2,766

Re: exportFromXL | Has any one used this Procedure?

Of course you can -- but that's a VBA or VBS question, and some more internet searches should yield some examples.  I have some success by using the Developer tab in Excel to record a macro, which captures a script of your actions in Excel.  You can then use this as a part of your final script.

Valued Guide
Posts: 505

Re: exportFromXL | Has any one used this Procedure?

This has the added advantage that it will work under any operating system, except VMS and Z-OS.

Not much harder in Python. Note you can wrap %utl_submit_r in a macro and call it for every member in a folder.

%utl_submit_r(
  library(rio);
  convert('d:/xls/xls_class.xls','d:/csv/csv_class.csv');
);

"NAME","SEX","AGE","HEIGHT","WEIGHT"
"Alfred","M",14,69,112.5
"Alice","F",13,56.5,84
"Barbara","F",13,65.3,98
"Carol","F",14,62.8,102.5
"Henry","M",14,63.5,102.5
"James","M",12,57.3,83
"Jane","F",12,59.8,84.5
"Janet","F",15,62.5,112.5
"Jeffrey","M",13,62.5,84
"John","M",12,59,99.5
"Joyce","F",11,51.3,50.5
"Judy","F",14,64.3,90
"Louise","F",12,56.3,77
"Mary","F",15,66.5,112
"Philip","M",16,72,150
"Robert","M",12,64.8,128
"Ronald","M",15,67,133
"Thomas","M",11,57.5,85
"William","M",15,66.5,112

Note xls_class.xls is a self locating file

just take the first three characters for the folder name ie

xls/xls_class.xls


☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 378 views
  • 1 like
  • 3 in conversation