- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi all,
I do an ods excel of a dataset with about 25000 records and 50 columns.
I have been getting an error that says "We found a problem with some content. Do you want us to try to recover as much as we can ? " I click yes.
Then it says Excel was able to to open the file by repairing or removing the unreadable content. Removed Records : Formula from /xl/worksheet/sheet1.xml part "
I tried all sorts of permutations and combinations to find the offending data column.
I tried only outputting some rows ( that worked fine) . Then I started excluding some columns to find if that is the column causing the error. On the face of it it didn't look like any records were removed.
Finally I think I have narrowed down the column causing the issue to a Character column. I took that column out from the ods and all worked ok. So there must be some offending characters or something in that column.
It's a Company name column so could be that it has some odd characters that excel may not like. It is difficult to find which row entry might be causing the issue.
How can I clean up that column so my user can open the file without the errors please ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Did your program fail due to memory?
I believe ODS EXCEL has to have enough memory to load an entire sheet.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@data_null__ Nope ...no memory issues at all
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
There are probably errors in the SAS log. You need to fix these.
If you're not sure how to fix the errors, then please show us the LOG for the ODS Excel part.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@PaigeMiller ok. I hadn't noticed anything in the log in terms of errors. But maybe I overlooked or missed any messages that might have given an indication. Let me rerun with the column back in and share.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
There are no errors. I tried running the ods without the offending column and have the same log. Only thing is the warning which I have sorted using goptions device=actximg;
25 GOPTIONS ACCESSIBLE;
26
27 ods excel file="&rpt_path./TEST_COMP.xlsx"
28 options( sheet_name="Comp Data");
WARNING: Unsupported device 'ACTIVEX' for EXCEL destination. Using device 'ACTXIMG'.
29 options validvarname=UPCASE label=0;
30
31 proc report data= RPT_TBL.COMP_TABLE ;
32 &title_gen.;
33 title2 underlin=1 font="Times New Roman" justify=left height=2.5 color=crimson bold "Test Company-&date.";
34 run;
NOTE: There were 25324 observations read from the data set RPT_TBL.COMP_TEST
NOTE: PROCEDURE REPORT used (Total process time):
real time 25:33.16
cpu time 25:13.75
35 ods excel close;
NOTE: Writing EXCEL file: /data1/general/FILES/ALL_PERI/XL_REPORTS/TEST_COMP.xlsx
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Use a similar approach, export the first half of your data and then the second half. If both cause the issue you have more than one row causing the issue, otherwise you keep doing that until you find the issue.
For company name, you can look at COMPRESS() to start removing characters that could be problematic. Look at the modifiers and see if you can start with removing punctuations and other symbols.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@Reeza I used the compress - p modifier for punctuations and that seems to have done the trick,
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Did you try PROC EXPORT ?
This issue usually occurred under low version SAS ( < SAS9.4M4 ) .
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@Ksharp yes. I did with proc export. it doesnt have that issue.
When i used the compress with modifier - p that seems to have resolved the issue (for ods) .
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Anuz,
I got this error message, too.
The cause in my case were calculation signs (+ - > <) at the beginning of the cell.
Obviously ODS Excel cannot handle this.
After replacing the calculation signs by words (plus, minus, gt, lt), it worked.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A work-around is to insert a space after the + so that is not seen as a formula in excel....col1 = tranwrd(col1, '+', '+ ');