topic Re: Exported XLSX file unable to open corrupted in SAS Programming

Exported XLSX file unable to open corrupted

Ashpak — Mon, 16 May 2022 14:32:40 GMT

Hi All

I have created below code to export my SAS data set into xlsx file with multiple sheets, file got created and sent email with attachment but xls file unable to open , its says file is corrupted, if we manually transfer xlsx file from SAS server to windows Desktop through WINSCP, file gets opened , but its not opening through email and SAS server locations. here is the code. Appreciate your help on this

libname x xlsx "/workspace/lob5/alm/u441070/xyz.xlsx";
data x.DQ_Pricing_Completeness;
set work.pdq001;
run;
data x.DQ_Pricing_Integrity;
set work.pdq002;
run;
%macro SendEmailFAST;
filename outbox email
to=(&emailTo1)
from=(&emailFrom)
cc=(&emailCC)
bcc=(&emailbcc)
type='text/html'
subject= "Data Quality Run &scn -&dbn &taday"
attach="/workspace/lob5/alm/u441070/xyz.xlsx" ;
ODS html body=outbox style=Harvest; /*BarrettsBlue*/
/* Pricing*/
Title1 color=red H=3 "Data Quality Exceptions Report NVP Pricing Templates for &scn%";
proc report data=Email_Fast_Rep_Pricing ;
columns CNT DQ_Dimension DQRule Field DQCHEK_POINT DQPriority;
define DQPriority / display; /* explicitly specify column usage */
define DQCHEK_POINT / display;
compute DQPriority;
if DQPriority='High' then call define(_col_, "style", "style=[backgroundcolor=Red]");
if DQPriority='Medium' then call define(_col_, "style", "style=[backgroundcolor=coral]");
if DQPriority='Low' then call define(_col_, "style", "style=[backgroundcolor=cyan]");
if DQPriority='No Actions' then call define(_col_, "style", "style=[backgroundcolor=Green]");
endcomp;
compute DQCHEK_POINT;
if DQCHEK_POINT='Fail' then call define (_col_, 'style', 'style=[foreground=Red]');
endcomp;
run;
Title1;

ods _all_ close;

%mend;
%SendEmailFAST;

Re: Exported XLSX file unable to open corrupted

ballardw — Mon, 16 May 2022 15:28:05 GMT

CLOSE the library connection before sending the email would be my first suggestion. Use:

LIbname x clear;

after adding the data but before calling the email macro.

The Libname means that SAS is still using the file and is very likely to cause problems like this.

Note: post code and log entries into a text box opened with the forum's </> icon to improve readability.

@Ashpak wrote:

Hi All

I have created below code to export my SAS data set into xlsx file with multiple sheets, file got created and sent email with attachment but xls file unable to open , its says file is corrupted, if we manually transfer xlsx file from SAS server to windows Desktop through WINSCP, file gets opened , but its not opening through email and SAS server locations. here is the code. Appreciate your help on this

libname x xlsx "/workspace/lob5/alm/u441070/xyz.xlsx";
data x.DQ_Pricing_Completeness;
set work.pdq001;
run;
data x.DQ_Pricing_Integrity;
set work.pdq002;
run;
%macro SendEmailFAST;
filename outbox email
to=(&emailTo1)
from=(&emailFrom)
cc=(&emailCC)
bcc=(&emailbcc)
type='text/html'
subject= "Data Quality Run &scn -&dbn &taday"
attach="/workspace/lob5/alm/u441070/xyz.xlsx" ;
ODS html body=outbox style=Harvest; /*BarrettsBlue*/
/* Pricing*/
Title1 color=red H=3 "Data Quality Exceptions Report NVP Pricing Templates for &scn%";
proc report data=Email_Fast_Rep_Pricing ;
columns CNT DQ_Dimension DQRule Field DQCHEK_POINT DQPriority;
define DQPriority / display; /* explicitly specify column usage */
define DQCHEK_POINT / display;
compute DQPriority;
if DQPriority='High' then call define(_col_, "style", "style=[backgroundcolor=Red]");
if DQPriority='Medium' then call define(_col_, "style", "style=[backgroundcolor=coral]");
if DQPriority='Low' then call define(_col_, "style", "style=[backgroundcolor=cyan]");
if DQPriority='No Actions' then call define(_col_, "style", "style=[backgroundcolor=Green]");
endcomp;
compute DQCHEK_POINT;
if DQCHEK_POINT='Fail' then call define (_col_, 'style', 'style=[foreground=Red]');
endcomp;
run;
Title1;

ods _all_ close;

%mend;
%SendEmailFAST;

Re: Exported XLSX file unable to open corrupted

Ashpak — Mon, 16 May 2022 16:31:01 GMT

Still the problem same unable to open even after closing libname before calling to email macros.

LIbname x clear;

Re: Exported XLSX file unable to open corrupted

ballardw — Mon, 16 May 2022 16:45:20 GMT

Can you open the file before sending the email?

Re: Exported XLSX file unable to open corrupted

PaigeMiller — Mon, 16 May 2022 16:50:52 GMT

Corrupted files cannot be opened. Something is corrupting it in the e-mail process. This really isn't a SAS issue at all.

Re: Exported XLSX file unable to open corrupted

Ashpak — Mon, 16 May 2022 16:56:38 GMT

Since file is written in Unix environment location if we open the file in binary formats

Re: Exported XLSX file unable to open corrupted

SASKiwi — Mon, 16 May 2022 23:34:15 GMT

Try modifying your FILENAME statement to ensure your attachment is recognised as an XLSX-type spreadsheet.

filename outbox email
to=(&emailTo1)
from=(&emailFrom)
cc=(&emailCC)
bcc=(&emailbcc)
type='text/html'
subject= "Data Quality Run &scn -&dbn &taday"
(attach="/workspace/lob5/alm/u441070/xyz.xlsx" content_type="application/xlsx");

Re: Exported XLSX file unable to open corrupted

Patrick — Tue, 17 May 2022 10:47:44 GMT

Did you already try what @SASKiwi proposes. That looks promising as it tells the email server that the attachment is of type XLSX so the email server won't do "bad things" to it.

Re: Exported XLSX file unable to open corrupted

Ashpak — Tue, 17 May 2022 10:52:07 GMT

Yes tried still the same issues unable to open xlsx (attach="/workspace/lob5/alm/u441070/xyz.xlsx" content_type="application/xlsx");

Hence thinking to use Text file with Tab delimited options if xlsx is not working

Re: Exported XLSX file unable to open corrupted

Patrick — Tue, 17 May 2022 11:12:08 GMT

I've sent in the past XLSX email attachments created in a Linux environment and never had such problems.

Re: Exported XLSX file unable to open corrupted

Ashpak — Wed, 18 May 2022 00:05:24 GMT

Is this problem with Sas enterprise guide version , I’m using sas EG7.1 version

Re: Exported XLSX file unable to open corrupted

SASKiwi — Wed, 18 May 2022 01:12:47 GMT

You are using your SAS server to send the email so your EG version is irrelevant. SAS version could be relevant though, so post the results of this:

%put &SYSVLONG;

I suggest that you try this test - upload a known good XLSX spreadsheet to your SAS server. Then email it to yourself using your SAS program. Does it open OK? If so there is nothing wrong with the SAS email attachment process. There must be something wrong with the original spreadsheet you are trying to attach.