How can I refresh a (possibly) corrupted workbook so it doesn't crash?

[jg0]

I have a spreadsheet that has been in use for 10 years. Big (1.5MB) but simple: no formulae, no graphs or charts, simply numbers, text and 8 images (logos, 1 per sheet, 8 sheets), regularly sorted and extracts exported to PDF.

It works fine with libreoffice calc v5.1.6.1 on Ubuntu 16.04 and 18.04.
On later versions of calc (eg v6.0.7.3 which comes from Ubuntu 18.04 repo or v7.4.5.1 downloaded from LibrOffice website) it crashes when I select a large number of cells (eg a range 90cols x 2400rows = 216000cells) and then attempt to copy [Ctl]+C.

Checking back through the audit trail I find that LO calc v6.0.7 works OK on spreadsheets saved prior to 12:48pm on 10 Apr 2021. The copy saved at 13:59 that day crashes which leads me to think there’s a corruption in the data which is tolerated by v5.1.6 but not by later versions.

To try to fix the problem I’ve tried to save as an Excel (.xls) workbook and reload. If I do that it still doesn’t work as an Excel sheet or when I’ve re-saved it in Open Doc format (.ods). I’ve also copied and pasted into new sheets and that doesn’t help either. If I try to copy say 1000rows the PC freezes - could it be short of memory although there’s 8GB RAM + 8GB Swap and it works OK with v5.1.6?

Has anyone else suffered a similar problem and if so how was it fixed?
Is there any way I can refresh the current version of the spreadsheet so that any corruptions are ironed out?
Any help would be hugely appreciated!
Tks
jg

[Lupp]

(Was there a “brown out” on on 2021-04-10 around high noon?)

As you said there are no formulas and only 8 images (which you surely also have saved elsewhere).
So you should be able to export the complete content to 8 csv files, and to create a new version of your Calc doc importing these files into a new document.

There may occur an error helping to better understand the cause of the issue. In case of no error (and no isues when checking the content) you may have what you need, and if problems occur after re-insertion of the logos, that’s also relevant info.

For better advice I would need the corrupted (malfunctioning) file itself.

[jg0]

Lupp
Tks for your suggestion.
I’ve been thinking some more about this problem and will do a heap more tests today and post the results. And, yes, I’ll try exporting to csv. I’ll also strip away some of the stuff to see at which point the problem disappears.
BTW I can’t count. The copy I was trying to do had 90 columns rather than 64. I have amended original post accordingly. I also tried to copy the 90 columns with 100 rows and it still crashed.
jg

[jg0]

Having trouble with creating a csv file as many of the text fields contain punctuation such as quotation marks, commas and semicolons. I get the issue quoted in this thread:

What is the maximum number of characters in a Calc cell?

In LibreOffice Calc, what is the maximum number of characters in a cell? (In comparison: the xls format has 32,767 characters, and it seems .xlsx also has that limit). I’d like to programmatically create Calc files and would like to know the limit (if any) and whether it is likely to change.

I created a fresh new blank sheet and started by successfully copying a heavy text column (2823 rows, no images) and pasting it into the new sheet. No problem. I then copied the preceding 14 columns of small, mixed text and numeric data into the new sheet. Again, no problem. I then added the remaining 75 columns of sparse numeric data. Slow (15 secs approx) but, no problem. But when I tried to select and copy all the data (2823 rows x 90 cols) it crashed (LO Calc v6.0.7.3). So maybe I’m exceeding some sort of limit somewhere which Calc v5.1.6.2 doesn’t care about.
Still working on it!
jg

[Wanderer]

@jg0

Having trouble with creating a csv file as many of the text fields contain punctuation such as quotation marks, commas and semicolons

Use tab as separator then and you may even use your own char like | if the pipe is not used in your data. Quoting text-fields should solve the problems also…
.
If you wish to use the clipboard, try a “binary” search: Copy 45 column over to abnew sheet, then the other 45 columns to a second sheet. Wich halve is crashing? Close and restart this with the crashed half and 23 + 22 colums. Repeat with 11/12 , then 6, 3, 2 colums and in 5 steps you should know in wich column is your problem.
.
With a similiar approach you can attempt to circle in the row. As 2400 is smaller then 2^12 you will need max. 12 steps for this.
Analyze this point. You may even delete the other rows in a copy and test, if this contents crash then also while copied. Then please upload this row here or create a bug-report and include the row there.

[jg0]

I think I may have found the answer.

I have a widget running called Clipman which remembers the last (in my case) 10 clipboard contents. I noticed that when I hit [Ctl]+C in Calc, Clipman grabs a chunk of CPU and memory. If I disable Clipman, Calc 6.0.7.3 will happily select and copy/paste any amount of my spreadsheet. It appears that Clipman interferes with Calc in a way which hurts v6 & v7 but not v5.

So I think the problem may lie with Clipman causing Calc to crash rather than the dataset.

Does this sound plausible?

jg

[erAck]

Oh yes, clipboard destroyers (aka “managers”), there are many of them.