Converting XML to XLSX

[Semie]

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);

[Reeza]

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);

---

[Semie]

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?

[Tom]

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-so...).  The initial releases that included ODS EXCEL it was experimental (aka buggy).

[Quentin]

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

[Reeza]

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.

[Semie]

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?

[Tom]

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.

[h919g]

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.

[Reeza]

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