topic Re: Converting XML to XLSX - Ignoring Corrupt Files in SAS Programming

Converting XML to XLSX - Ignoring Corrupt Files

djbateman — Thu, 23 Aug 2018 14:43:30 GMT

To decrease file sizes, my company has standard code that exports files to XML format and then uses %convert_files() macro to convert XML to XLSX. Basically, this macro will go to the specified directory (default parameter) and convert all XML files to the specified file type (ext parameter).

I run into a small issue where a SAS program might produce a "corrupt" file. This is detected by the file size being 68 KB (a blank but non-corrupted file is 70 KB). When such a file is found, this macro pauses and won't convert the files. Even if later down the road this file is still in the directory and a new non-corrupted file is produced, it won't convert even the valid files.

I don't know VBscript, so can anyone tell me if I can modify the code below to delete any XML files of size 68 KB found before running through the directory and converting any other files?

options noxwait noxsync; 

%macro convert_files(default=,ext=);
      data _null_;
            file "'&default\temp.vbs'";
            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 "  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.vbs""";
%mend;

Re: Converting XML to XLSX - Ignoring Corrupt Files

ChrisNZ — Fri, 24 Aug 2018 02:36:10 GMT

You'd be better off asking this on a Microsoft forum; People are not VBS experts here.

Having said that, a web search for vbs delete file by size returns many replies, so there's probably no point even asking,

Re: Converting XML to XLSX - Ignoring Corrupt Files

ChrisNZ — Tue, 28 Aug 2018 22:22:25 GMT

I think this is a very bad idea by the way.

Excel is the worst possible format to hold data. There is no data type or metadata. It's a mess.

I would strongly suggest that use zip the XML files instead.

They can then be accessed directly:

FILENAME ZIPFILE SASZIPAM "&path\archive.zip";

DATA newdata;
  INFILE ZIPFILE(data1.xml);
  INPUT @; 
RUN;

Re: Converting XML to XLSX - Ignoring Corrupt Files

djbateman — Wed, 29 Aug 2018 14:45:35 GMT

In case there is anyone else out there looking for this solution, I actually came across an article that helped me remove any files. I know ChristNZ does not approve, but this is what works for what my company needs done.

The macro below will now find all "ext" files in the "default" directory, delete any that are <70KB (this is because XML files are corrupt files if they are 68KB and clean but empty at 70KB), and finally convert any "ext" files to XLSX. I may be the only one to benefit from this, but now the information is out there.

%macro convert_files(default=,ext=);
	data _null_;
		file "'&default\temp.vbs'";
		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.vbs""";
%mend;