topic Re: Converting XML to XLSX in SAS Programming

Converting XML to XLSX

Semie — Tue, 17 Aug 2021 16:28:44 GMT

I am trying to convert XML to excel file and got the code from the link below. I created the XML but when I run the code to convert to excel, a corrupted(blank) excel file is created, log looks clean. When I open the file, I got the massage in the screenshot below.

Could anyone help me if I miss any, please?

https://communities.sas.com/t5/SAS-Procedures/Converting-XML-to-XLSX/td-p/142446

Here is the code I am using:

ods tagsets.excelxp file="&default\temp.xml" newfile=bygroup;
proc print data=test;
run;
ods tagsets.excelxp close;

%macro convert_files(default=,ext=);
data _null_;
file "'&default\temp.xlsx'";
put "set xlapp = CreateObject(""Excel.Application"")";
put "set fso = CreateObject(""scripting.filesystemobject"")";
put "set myfolder = fso.GetFolder(""&default"")";
put "set myfiles = myfolder.Files";
put "xlapp.DisplayAlerts = False";
put " ";
put "for each f in myfiles";
put "If f.Size < 70000 And fso.GetExtensionName(f)=""&ext"" Then";
put " f.Delete True";
put " End If";
put " Next";
put " ";
put "for each f in myfiles";
put " ExtName = fso.GetExtensionName(f)";
put " Filename= fso.GetBaseName(f)";
put " if ExtName=""&ext"" then";
put " set mybook = xlapp.Workbooks.Open(f.Path)";
put " xlapp.Visible = false";
put " mybook.SaveAs ""&default.\"" & Filename & "".xlsx"", 51";
put " End If";
put " Next";
put " mybook.Close";
put " xlapp.DisplayAlerts = True";
/* Removes original files */
put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";
put " xlapp.Quit";
put " Set xlapp = Nothing";
put " strScript = Wscript.ScriptFullName";
put " FSO.DeleteFile(strScript)";
run;

x "cscript ""&default\temp.xlsx""";
%mend;

%convert_files(default=H:\CDISC\ADaM\Data\Q2,
ext=xml);

Re: Converting XML to XLSX

Reeza — Tue, 17 Aug 2021 17:37:08 GMT

What happens if you open the XML before your conversion? Does it open fine then?

Also, can you switch to using ODS EXCEL and avoid the conversion requirement entirely?

Also, what happens if you run the code below:



ods tagsets.excelxp file="H:\CDISC\ADaM\Data\Q2\forum_demo.xml";
proc print data=sashelp.class;
run;
ods tagsets.excelxp close;

%macro convert_files(default=,ext=);
data _null_;
file "'&default\temp.xlsx'";
put "set xlapp = CreateObject(""Excel.Application"")";
put "set fso = CreateObject(""scripting.filesystemobject"")";
put "set myfolder = fso.GetFolder(""&default"")";
put "set myfiles = myfolder.Files";
put "xlapp.DisplayAlerts = False";
put " ";
put "for each f in myfiles";
put "If f.Size < 70000 And fso.GetExtensionName(f)=""&ext"" Then";
put " f.Delete True";
put " End If";
put " Next";
put " ";
put "for each f in myfiles";
put " ExtName = fso.GetExtensionName(f)";
put " Filename= fso.GetBaseName(f)";
put " if ExtName=""&ext"" then";
put " set mybook = xlapp.Workbooks.Open(f.Path)";
put " xlapp.Visible = false";
put " mybook.SaveAs ""&default.\"" & Filename & "".xlsx"", 51";
put " End If";
put " Next";
put " mybook.Close";
put " xlapp.DisplayAlerts = True";
/* Removes original files */
put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";
put " xlapp.Quit";
put " Set xlapp = Nothing";
put " strScript = Wscript.ScriptFullName";
put " FSO.DeleteFile(strScript)";
run;

x "cscript ""&default\temp.xlsx""";
%mend;

%convert_files(default=H:\CDISC\ADaM\Data\Q2,
ext=xml);

@Semie wrote:

I am trying to convert XML to excel file and got the code from the link below. I created the XML but when I run the code to convert to excel, a corrupted(blank) excel file is created, log looks clean. When I open the file, I got the massage in the screenshot below.

Could anyone help me if I miss any, please?

https://communities.sas.com/t5/SAS-Procedures/Converting-XML-to-XLSX/td-p/142446

Here is the code I am using:

ods tagsets.excelxp file="&default\temp.xml" newfile=bygroup;
proc print data=test;
run;
ods tagsets.excelxp close;

%macro convert_files(default=,ext=);
data _null_;
file "'&default\temp.xlsx'";
put "set xlapp = CreateObject(""Excel.Application"")";
put "set fso = CreateObject(""scripting.filesystemobject"")";
put "set myfolder = fso.GetFolder(""&default"")";
put "set myfiles = myfolder.Files";
put "xlapp.DisplayAlerts = False";
put " ";
put "for each f in myfiles";
put "If f.Size < 70000 And fso.GetExtensionName(f)=""&ext"" Then";
put " f.Delete True";
put " End If";
put " Next";
put " ";
put "for each f in myfiles";
put " ExtName = fso.GetExtensionName(f)";
put " Filename= fso.GetBaseName(f)";
put " if ExtName=""&ext"" then";
put " set mybook = xlapp.Workbooks.Open(f.Path)";
put " xlapp.Visible = false";
put " mybook.SaveAs ""&default.\"" & Filename & "".xlsx"", 51";
put " End If";
put " Next";
put " mybook.Close";
put " xlapp.DisplayAlerts = True";
/* Removes original files */
put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";
put " xlapp.Quit";
put " Set xlapp = Nothing";
put " strScript = Wscript.ScriptFullName";
put " FSO.DeleteFile(strScript)";
run;

x "cscript ""&default\temp.xlsx""";
%mend;

%convert_files(default=H:\CDISC\ADaM\Data\Q2,
ext=xml);

Re: Converting XML to XLSX

Semie — Wed, 18 Aug 2021 15:37:33 GMT

The XML file opens fine, I can see all the information as expected.

If I use ODS EXCEL, the program crashes because the SAS dataset is very big, that is the reason I am creating XML and trying to convert to excel.

I tried the code you updated, XML created and can be opened fine, the excel file is also generated but has NO information(just blank).

What could be the reason?

Re: Converting XML to XLSX

Tom — Wed, 18 Aug 2021 16:17:00 GMT

Raise a support ticket with SAS to have them investigate your specific example to see why ODS EXCEL crashes.

Make sure you are running a pretty recent release of SAS (https://blogs.sas.com/content/iml/2013/08/02/how-old-is-your-version-of-sas-release-dates-for-sas-software.html). The initial releases that included ODS EXCEL it was experimental (aka buggy).

Re: Converting XML to XLSX

Quentin — Wed, 18 Aug 2021 17:01:12 GMT

You might try increasing -memsize to see if you can get ODS EXCEL to work. See e.g. the comments in:

https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

But if it's huge data, you still might hit the memory limit. (Of course if it's huge data, some might question the benefits of storing it in an Excel file).

Re: Converting XML to XLSX

Reeza — Wed, 18 Aug 2021 17:09:08 GMT

Go to the VBS code and run it manually instead, find the VBS file and double click it. See if that works but if not it may at least give you a better error.

Re: Converting XML to XLSX

Semie — Thu, 19 Aug 2021 19:14:08 GMT

I went to the VBS code and run it manually, and received the massage below when I double click on the file.

Does the code I am using look correct?

Re: Converting XML to XLSX

Tom — Thu, 19 Aug 2021 19:28:00 GMT

Sounds like there is an EXCEL setting to no longer accept files with inappropriate extensions.

When creating the XML file use .xml as the extension instead of .xlsx.

Re: Converting XML to XLSX

h919g — Mon, 25 Oct 2021 05:02:52 GMT

Hi Reeza,
this is quite useful script. I have on question for one of the line for [put " mybook.SaveAs ""&default.\"" & Filename & "".xlsx"", 51";]
what the '51' stands for? do we have another option for this. because when I use this script to convert xml file to xlsx, some information lost.

Re: Converting XML to XLSX

Reeza — Mon, 25 Oct 2021 16:15:15 GMT

51 specifies the XLSX file format type.
If you manually go in and Save As do you get the same issue?

They're listed here: https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat