topic Re: converting xml to xls in ODS and Base Reporting

converting xml to xls

wkossack_nspirehealth_com — Tue, 09 Jul 2013 14:09:04 GMT

Is there a better way to get output into an actual xls? I'm running 9.3 on 64bit windows server 2006r2 and office 2013

ods tagsets.excelxp path=xxxx file=spreadsheet.xml style xlsanspprinter;

ods tagsets.excelxp options (

embedded_titles='yes' embedded_footnotes='yes' sheet_name='Reversibility'

suppress_bylines='yes'

width_points='1' width_fudge='1'

absolute_column_width='100,100,100,100,100,100,100,100,100,100,100'

autofit_height='yes' orientation='landscape'

autofilter='No' frozen_headers='3'

center_horizontal ='yes ');

proc report;....

ods gtagsets.excelxp close;

ods listing close;

right now I'm using a vbscript kicked off by sas to convert the xml to xls

Dim xlApp, xlWkb

Set xlApp = CreateObject("excel.application")

set fs = CreateObject("Scripting.FileSystemObject")

SourceFolder="\\srv01\Reports\Reports\"

TargetFolder="\\srv01\Reports\Reports\"

for each file in fs.GetFolder(SourceFolder).files

If Right(LCase(file.Name), 4) = ".xml" Then

Set xlWkb = xlApp.Workbooks.Open(file)

BaseName= fs.getbasename(file)

FullTargetPath=TargetFolder & "\" & BaseName

xlApp.DisplayAlerts=false

xlWkb.SaveAs FullTargetPath, 51

xlApp.DisplayAlerts=true

xlWkb.close

wscript.sleep 5000

End If

fs.DeleteFile("\\srv01\Reports\Reports\\*.xml")

Re: converting xml to xls

Cynthia_sas — Tue, 09 Jul 2013 22:45:09 GMT

Hi, an "actual xls" file is a proprietary Microsoft format. TAGSETS.EXCELXP creates Microsoft Office Open XML Spreadsheet Markup Language XML 2003 specification. So unless you use the LIBNAME engine or PROC EXPORT, the XML file is as close to Excel "real/actual" format as you can get. with ODS.

The VBScript solution is a good one. It won't work on all operating systems, however, but it is one way to re-save the files, if you MUST have proprietary format .XLS files. Otherwise, I find that naming the files with the .XML extension and educating the folks who open the .xml file is also a viable alternative.

cynthia

Re: converting xml to xls

wkossack_nspirehealth_com — Thu, 11 Jul 2013 15:09:23 GMT

according to SAS Tech support 9.4M1 will produce excel 2010 spreadshets

One thing that is puzzeling me is that the vbscripts don't run consistently in midnight batch resulting in xml files and xlsx files remaining in the output folders confusing clients using a web portal to get their reports. The vbscript runs fine if I kick it off in batch but not when run at night

Re: converting xml to xls

wkossack_nspirehealth_com — Wed, 24 Jul 2013 16:05:55 GMT

IT changed the trust settings on the batch file that was running and now things seem to be working