topic Re: exportFromXL | Has any one used this Procedure? in SAS Procedures

exportFromXL | Has any one used this Procedure?

TestingSAS — Wed, 06 Jul 2016 16:39:28 GMT

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

Re: exportFromXL | Has any one used this Procedure?

ChrisHemedinger — Wed, 06 Jul 2016 16:49:00 GMT

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.

Re: exportFromXL | Has any one used this Procedure?

TestingSAS — Wed, 06 Jul 2016 16:53:39 GMT

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

Re: exportFromXL | Has any one used this Procedure?

ChrisHemedinger — Wed, 06 Jul 2016 17:25:04 GMT

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

Re: exportFromXL | Has any one used this Procedure?

TestingSAS — Wed, 06 Jul 2016 17:43:03 GMT

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

Re: exportFromXL | Has any one used this Procedure?

ChrisHemedinger — Wed, 06 Jul 2016 17:47:00 GMT

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.

Re: exportFromXL | Has any one used this Procedure?

rogerjdeangelis — Wed, 06 Jul 2016 23:32:48 GMT

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