BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TestingSAS
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

6 REPLIES 6
ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
TestingSAS
Obsidian | Level 7

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..

ChrisHemedinger
Community Manager

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
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
TestingSAS
Obsidian | Level 7

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

ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
rogerjdeangelis
Barite | Level 11
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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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