ODS creating and excel output but table error on opening and no report produced

[Elliott]

I am having this problem and I think it comes down to some unseen invalid data that causes ODS to give this error.  

The report has been running fine for several months, I read in chat data and output into a report for review.  The chat data is in a csv file.

Last week I started having an issue where the excel only gives an error message when trying to open.

There are no errors in the log.

The box that pops up when trying to open the excel report .

I have attached a pdf of the box.

 When I click ok the excel closes so no report output.

I have tried to strip any unprintable characters out of the field that contains the chat data but still having the same problem.

The tables look fine, I cannot see any issue or strange data.

I need to figure out how to resolve this.  Has anyone else have experience with this?  I tried to upload the image of the box I get, but the image failed to upload.

Any assistance to resolve this issue will be greatly appreciated.

Thanks,

Elliott

[Reeza]

Without more information I'm not sure we can help you.

I would suggest taking one of your current reports and one of the old reports and comparing the XML. An XLSX file is just a zipped XML so you can unzip it and compare the contents to find the difference and then try and trace it back.

My first guess would also be that the issue was with some weird text in the CSV file, especially if it's survey comments. Look for ALT+ENTER symbols (Carriage returns in cells), extra commas, and quotation marks that don't match up in the text data. Verify your data import step is happening correctly and that there are no errors in the log for that step.

[Elliott]

The report output is .xls, I changed to xml and received same issue.
I have narrowed it down to one field in the chat transcript and if I just comment that field out in the ODS proc print the report works, unfortunately the important part of that data is the chat content. I have also used a compress on that variable to eliminate all un writable characters and still would not work if I left that variable in the ODS output.

[Reeza]

Check your raw csv file. I bet that field has some quotation marks or comma that mess up your import.

XLS is a really old file format that shouldn't be used (pre 2010) which means you're using ODS TAGSETS or some really old method to create your files.

[ballardw]

Did you look at that log file referenced in the popup?

Are you reading data using Proc Import?

If so, then you may be getting artifacts because the variable types or other properties have changed. "Tables look fine" is not the same as comparing data to a previous data set where the process worked to verify that the properties of variables are the same. Proc Contents on a working data set and one that creates the error is good place to start.

No errors in the log is not the same as "no warnings" which will often create output but means that the data has another issue.

What if you use a different ODS destination, such as the basic results window (html)? Do you get output?

If your ODS Excel statement has lots of controls set like column widths and the output no longer aligns with them that is something to look at as well. Or share your ODS destination statement. There are enough different ways to create "Excel" output that details matter.

[Elliott]

I have not looked at the log in the message because the location does not exist on my pc.
I am using a data step to read in the .csv file so I am setting the length and format.
I have re run the code on a older transcript file and had no problem, but since 3/23 I started having this issue.
I am sure it is something in the chat content that sas deems invalid but there is no indication in the log what that might be. There are no errors or warnings all runs find.
This is a production report and must post to the location it currently does. I am working on a Linux server.
The ODS proc print is very basic, there is only a style line to convert numeric data to text.
This is the same process we use to build 100's of reports each day.
I have come across this issue and it always has to do with something in the data that is being read in.
I have just never been able to resolve. I was hoping someone else here had experience with this and could help me figure out how to resolve.
a copy of my ods code is below:
ods tagsets.excelxp options(sheet_name="Cards Transcript" sheet_interval='none' embedded_titles='yes' wraptext = "no" autofilter='all'

absolute_column_width="10,12,14,20,20,15,12,20,20,12,14,20,20,14,100");



proc print data= trans noobs label;

title "Transcript";



var

ONEID

Calldate

callsttm

CreateTime;

var

callid /style(Column)= [tagattr='format:text' ];

var

Topic

Duration;

var

deviceid /style(Column)= [tagattr='format:text' ];

var

Name

Call_Type;

var

ANI /style(Column)= [tagattr='format:text' ];

var

Transcript_ID /style(Column)= [tagattr='format:text' ];

var

Transtime

who

chat

;

LABEL

chat='Content'

calldate='CallDate'

callsttm='CallStartTime'

Duration='CallDuration'

callid='CallID'

deviceid='DeviceID'

Transcript_ID='TranscriptID'

who='ResponseFrom'

;

run;

[Reeza]

Text data in CSV is problematic. I'll almost guarantee you have weird characters in your CSV such as a comma, quote or another language even that's messing it up. Save your CSV as an Excel file, Import it as an Excel file and use PROC COMPARE to compare that against your CSV imported data to help find it.