topic Re: How to convert XML files to XLS (Excel 2003)? in SAS Programming

How to convert XML files to XLS (Excel 2003)?

angeliquec — Thu, 06 Nov 2014 08:07:00 GMT

Hi, I would like to know how we can convert XML files to XLS by SAS code. We do not want manual opening of XML files in excel then doing Save As Excel 2003 (xls) because there are a lot of files to be converted.

The guide in this link (43496 - Convert files created using an ODS destination to native Excel files) converts XML to XLSX, not XLS. Our end users still prefer the outputs in Excel 2003 format.

Thank you!

Re: How to convert XML files to XLS (Excel 2003)?

Kurt_Bremser — Thu, 06 Nov 2014 08:57:03 GMT

Unless you run SAS on Windows and have ACCESS to PC Files licensed, you won't be able to directly write a .xls file.

With ACCESS to PC Files licensed on another platform, you may be able to do it with the PC Files Server which needs to be installed on a suitable Windows machine.

Bu you may be able to script the conversion process in Windows with Powershell or VB.

Re: How to convert XML files to XLS (Excel 2003)?

angeliquec — Thu, 06 Nov 2014 11:48:26 GMT

Hi Kurt,

We have SAS Access to PC Files installed. May I know where can I find a sample conversion script that converts XML to XLS? Apologies, I am not knowledgeable with Powershell or VB.

Thank you!

Re: How to convert XML files to XLS (Excel 2003)?

Kurt_Bremser — Thu, 06 Nov 2014 12:20:25 GMT

The macro in 43496 - Convert files created using an ODS destination to native Excel files has a check for Excelversion and then decides which name to use. Since all Excel versions can write .xls, have you tried to simply reduce that part so that it always does a save as .xls?

Re: How to convert XML files to XLS (Excel 2003)?

angeliquec — Thu, 06 Nov 2014 12:28:41 GMT

Yes, I have tried reducing that part. The macro converts the XML to XLS but when the workbook is opened in Excel, I get the error message

The file format and extension of .xls don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it....

I've found no issues with the produced xls file but I'm worried about the end users who will find the message suspicious and may not trust the output.

Re: How to convert XML files to XLS (Excel 2003)?

Kurt_Bremser — Thu, 06 Nov 2014 13:01:50 GMT

Try to use

oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xls"",56

The 56 seems to be crucial in determining the correct internal filetype, as in

Use VBA SaveAs in Excel 2007-2013

Re: How to convert XML files to XLS (Excel 2003)?

RW9 — Thu, 06 Nov 2014 13:03:02 GMT

Hi,

The reason you are getting that message box is not because of an error. When opening a file with an extension which Excel expects, it does a pre-check to ensure the file is in its (proprietary) format. Even if you have a perfectly fine XML document, Excel with read and interpret it, but still complain it isn't one of its file formats. IMO I would create a small VBS script which opens your file and uses the Excel SaveAS function to create an Excel propriatory file format . Take a look at this thread where I give an example of this: https://communities.sas.com/thread/62554

Re: How to convert XML files to XLS (Excel 2003)?

esjackso — Thu, 06 Nov 2014 13:06:45 GMT

I believe XLSX actually uses XML while XLS is another format. So just changing the extension leaves the XML and excel warns that something funny is going on.

An all sas solution would probably be to read the XML into a sas dataset then export the sas dataset to excel using a libname with excel engine or just a proc export step.

There is code that will search a folder (or a drive for that matter) for a certain file type (xml) then all you need to do is loop through all the files that are returned through the dataset creation and export phases.

To get you started here is some code I use to search a specific folder and exclude files with a tiff extension from the output:

%let folder = U:\Test; /*Folder you are looking in*/

Data txt_file (keep=file_name file row );

length file_name file $20 ;

rc = filename("dir","&folder");

d = dopen("dir");

n = dnum(d);

do i = 1 to n;

file_name = dread(d,i);

file = scan(file_name,1,".",'R');

row + 1;

if indexw(file_name,"Tiff")>0 then ;

else output;

end;

run;

Here is the beginning of the macro code that uses the output from above:

%macro test;

proc sql noprint;

select count(*) into: cnt

from txt_file

;

quit;

%do i = 1 %to &cnt;

proc sql noprint;

select strip(file_name), strip(file) into :fn, :f

from txt_file

where row = &i

;

quit;

data _null_;

date = strip(put(date(),weekdate.));

time = strip(put(time(),timeampm.));

call symput('date',date);

call symput('time',time);

run;

%put ***************************;

%put Date: &date;

%put Time: &time;

%put &i of %sysfunc(strip(&cnt)) : &fn ;

%put ***************************;

/*PUT YOUR XML TO SAS HERE*/

/*PUT YOUR SAS TO XLS HERE*/

%mend test;

Hope this gets you going in the right direction!

Re: How to convert XML files to XLS (Excel 2003)?

RW9 — Thu, 06 Nov 2014 14:47:22 GMT

Hi,

Just to confirm, there is no "problem" with that. All Excel is saying is hey, this isn't in my format (i.e. Zipped Open Office format or Proprietary binary file). It does not alter the functionality. Hence the suggestion to use a script to have Excel open, parse and then save the file in its own format. To do it through SAS would mean that it gets parsed several times, e.g. import/export, which can influence the end product.

Re: How to convert XML files to XLS (Excel 2003)?

Reeza — Thu, 06 Nov 2014 15:24:48 GMT

Replace this section of code:

" if (oXL.Version) >= 12 Then" ;

put " oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xlsx"",51";

put " Else";

put " oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xls"",-4143";

put " End If";

With only:

put " oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xls"",-4143";

Does your error still appear? This is saving the file as .xls rather than just a rename so I wouldn't expect to see an error about format type.

Re: How to convert XML files to XLS (Excel 2003)?

angeliquec — Fri, 07 Nov 2014 08:43:07 GMT

Thank you Kurt!

The code

oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xls"",56";

worked.

Re: How to convert XML files to XLS (Excel 2003)?

angeliquec — Fri, 07 Nov 2014 08:43:49 GMT

Thank you Reeza!

The code

put " oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xls"",-4143";

worked.

Re: How to convert XML files to XLS (Excel 2003)?

angeliquec — Thu, 13 Nov 2014 00:00:37 GMT

Hi all,

Apologies for bringing up this topic again.

Can the code from

43496 - Convert files created using an ODS destination to native Excel files)

be edited in a way that a specific file is converted, and not all the files from a folder?

Thank you!

Re: How to convert XML files to XLS (Excel 2003)?

Reeza — Thu, 13 Nov 2014 00:33:09 GMT

Sure, but if you have only one file, why not manually do it?