DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

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

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!


Accepted Solutions
Solution
‎11-06-2014 10:24 AM
Super User
Posts: 17,907

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

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.

View solution in original post


All Replies
Super User
Posts: 6,963

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 54

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

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!

Super User
Posts: 6,963

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

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 54

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

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.

Super User
Posts: 6,963

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 54

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

Thank you Kurt!

The code

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

worked.

Super User
Super User
Posts: 7,413

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

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

Super Contributor
Posts: 333

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

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!

EJ

Super User
Super User
Posts: 7,413

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

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.

Solution
‎11-06-2014 10:24 AM
Super User
Posts: 17,907

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

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.

Contributor
Posts: 54

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

Thank you Reeza!

The code

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


worked.

Contributor
Posts: 54

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

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!

Super User
Posts: 17,907

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 2883 views
  • 4 likes
  • 5 in conversation