BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
angeliquec
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

13 REPLIES 13
Kurt_Bremser
Super User

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.

angeliquec
Quartz | Level 8

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!

angeliquec
Quartz | Level 8

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.

angeliquec
Quartz | Level 8

Thank you Kurt!

The code

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

worked.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

esjackso
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Reeza
Super User

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.

angeliquec
Quartz | Level 8

Thank you Reeza!

The code

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


worked.

angeliquec
Quartz | Level 8

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!

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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