Bus Obj 6.5 - Saving to Excel - Long Cells

[SHardy]

Hi,

One of our users has been having trouble saving a Business Objects 6.5 report to Excel.

When refreshing and saving the report, the following is returned when trying to open the Excel file:

"Microsoft Office Excel File Repair Log

Errors were detected in file 'C:\ReportName.xls'
The following is a list of repairs:

Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted.

Excel found errors that may cause some recovered data to be put in the wrong cells. Please check your recovered data carefully."

However, if the BusObj document is NOT refreshed, but is saved with the previous data, the file opens OK.

Upon comparing the two sets of returned data, it would appear that the problems are caused when a cell contains over 2,000 characters.

Can any one confirm that this is an issue?
Is this an intentionally imposed limit?
Or is it a bug?
Is there any way to get round this?

Many Thanks,
Simon

 

[SHardy]

OK, ignore this post. There IS still a problem, but it seems impossible to pinpoint it.

It is not down to text length.

I tried performing a SUBSTR on the field in question. Gradually changing the parameters. Eventually, with SUNSTR(1,1314) I got no error from the saved Excel file. However, with SUBSTR(1315,1) I still got no error. Even with SUBSTR(1300,700) I still got no error from the Excel file.

I then tried to pin it down to a particular record by changing the conditions further. By changing the conditions, the following months (when run individually) all returned the same problem with the Excel file:
Feb2005
Apr2005
Jul2005
Aug2005

However, when breaking it down further:
1st Feb to 11th Feb returned the problem.
1st Feb to 10th Feb did not.
However 11th Feb also returned no error.
9th Feb to 12th Feb returned no error.

At this point I decided to rebuild the report from scratch.
When starting the report (via the new report wizard) it created a single table with all data. Saved this to Excel... no problems opening it.

Added a couple of tabs, where I went about duplicating the original report. When all the necessary objects were in place, I saved as Excel... and it returned errors when I tried opening it.

I deleted the long text field from the table in one of those duplicated tabs, saved as Excel and... it opened OK.

The very odd thing here is that I didn't delete the original tab that was created when I started setting up the report. So ALL of those text values were still in the last Excel file that opened OK.


I think this is just one of those really weird buggy things that will never be understood.

Please don't waste your time on this.

Thank you

 

[skrandel]

It's not a bug. It has to do with the limitation on the size of a cell in Excel. Do a search in the Excel help for limits. A cell can only contain a certain number of characters, but it can also only be a certain height. So if there are too many linefeeds in the data, this can happen.

Steve Krandel
Symantec

 

[SHardy]

Row height 409 points"

"Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar."

Just over 2,000 characters hardly makes a dent.

However, as I said in my last post, I was a bit hasty in assuming that the cell length was the cause. It is not possible to identify a single data item that is causing the problem. Also, I can get the data into Excel, but not when in that particular report tab/ layout.

Thanks for your help, but I really don't think it's something that is worth pursuing any further.

regards,
Simon

 

[xlbo]

32767 is actually a bit misleading - for any data transfer etc, it is the 1024 limit that is important. Dates and date formats can also be an issue as excel will try to import as mm/dd/yyyy - if the date in the report is dd/mm/yyyy it can 'throw a tantrum' if the mm portion is >12 eg. dd/mm/yyyy for 14th feb 2006 would be 14/02/2006 - if excel tries to read this a mm/dd/yyyy, it will read as 14/02/2006 - there cannot be 14 months in a year so excel has 'a bit of a fit' about it. However, having said all that, these things will rarely cause a corrupt file. It is more likely (as you have probably discovered) that the BOBJ file has become slightly corrupt and this is what is causing the issue. As an aside, I have seen this quite a lot - especially with large reports that have a lot of data elements - it is something that seems to become more prevalent over time (ie it happens to older reports more than new ones)

 

[skrandel]

Right, lenght is 32767 is the limit. But, height of 409 points can happen with alot less characters. We've seen the problem with less than 1200 characters.

Steve Krandel
Symantec