Emailing an XLSX File Created from ODS from a Unix environment

JordanS — Wed, 20 Sep 2017 13:31:58 GMT

Hello,

I am attempting to email an XLSX file that is created via ODS from a Unix environment, but I am getting two different error messages when trying to open the file in Excel 2013. The first is: "We found a problem with some cotent in 'MY_FILE.xlsx'.Do you want to try to recover as much as we can? If you trust hte source of this workbook, click Yes." After clicking "Yes", I am getting "Excel cannot open the file "MY_FILE.xlsx" becaause the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

When I download the file via an FTP client directly from the Unix environment, I still get the first message, but after clicking "Yes", Excel can successfully repair the file and I can view the contents.

My SAS version is 9.04 and the code that I am using is below:

<code>

ods excel file="/unix/path/MY_FILE.xlsx";

proc report data=libname.my_dataset headline headskip missing;
define product/ width=20 display style(column)={tagattr='Format:TEXT'};
define description/ width=20 display style(column)={tagattr='Format:TEXT'};
define total/ width=20 display style(column)={tagattr='Format:TEXT'};
define sub_total/ width=20 display style(column)={tagattr='Format:TEXT'};
RUN;

ods excel close;
ODS LISTING;

%let attachment = /unix/path/MY_FILE.xlsx;

filename SENDMAIL email TO= "asdf@email.com"
subject= "Email Subject"
type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
attach= ("&attachment");
run;

DATA _NULL_;
file SENDMAIL;
RUN;

</code>

I have tried defining the content type in several ways, including adding it into the attachment arguments, but that just attempts to attach random files to the email and I still get the same error messages. I have also tried "application/xlsx" and "application/excel" content-types, both with the same results.

Any help would be greatly appreciated.

Thanks!

-Jordan

Re: Emailing an XLSX File Created from ODS from a Unix environment

Quentin — Wed, 20 Sep 2017 14:43:08 GMT

I can replicate the problem with:

%let attachment=%sysfunc(pathname(work))/MY_FILE.xlsx ;

ods excel file="&attachment";

proc print data=sashelp.class ;
run ;

ods excel close;

filename SENDMAIL email 
  to= "me@blah.com"
  from="me@blah.com"
  subject= "Email Subject"
  attach= ("&attachment") ;
run;

data _null_;
  file SENDMAIL;
run ;

Re: Emailing an XLSX File Created from ODS from a Unix environment

JordanS — Thu, 21 Sep 2017 13:39:56 GMT

Thanks for the verfication! Glad I'm not the only one. We've created a work around by just zipping the file first and attaching, but I'd rather not have to do that long term.

-Jordan

topic Re: Emailing an XLSX File Created from ODS from a Unix environment in SAS Programming

Emailing an XLSX File Created from ODS from a Unix environment

Re: Emailing an XLSX File Created from ODS from a Unix environment

Re: Emailing an XLSX File Created from ODS from a Unix environment