Fatal crash in big worksheet

[Abonnee]

I work with Windows 11 with 32 GB RAM and LibreOffice Version: 24.8.5.2 (X86_64).
A 191,488 KB Opendocument worksheet with 20 tabs, including a few small ones with only a little text, a few graphs and a few large worksheets with (a lot of) data.
Saving took longer and longer without seeing anything happening. I thought that was because of the size of the worksheet, so I was thinking about downsizing. The worksheet is daily updated. Yesterday, when saving, I got a message that there was a problem and that the document was damaged. Something with input and output. The error message was longer than I am normally used to.
It was a pity that the automatic backup was also saved with this damage. So the backup from the previous day was of no use to me.
When opening, the system asks whether LibreOffice should repair it and when you confirm, you get a screen in which UTF-8 is selected as text import. I accept all settings and click OK, but then a worksheet opens with only one tab that is also completely empty. Yet it is still a file of 191,488 KB. In a hex-editor I can see the file is not empty, but the content is garbage to me.

1. Does anyone know a trick to save the data, or is it a lost cause? The graphs may be lost, but it would be nice if I could get the data back.
2. Is this too much for LibreOffice? It would be nice if there was a warning when the file gets too big.

Which worksheet wizard can help?

[Villeroy]

Large spreadsheets with no backup strategy are the perfect recipe for data loss.

@Abonnee

The worksheet is daily updated.

In fact, it is a database on sheets, right?

[Abonnee]

Yes, you’re right.
But in my defense: I switched on the automatic backup on saving time.
It is only a pity the problem was saved in this backup too, without any warning.
So, from now on, I’ll create a manual backup on a regular basis.
Anyway, we’re all humans and one bad day Murphy will come by, no matter how careful you normally are.

[Kyodake]

LibreOffice applications can become unstable with large files, especially if they have many formulas and cross-references between sheets, and if you’re running Windows.

It’s a good idea to optimize your LibreOffice settings:

Increasing allocated memory:

Menu —> Tools —> Options —> LibreOffice —> Advanced —> Open Expert Settings

org.openoffice.Office.Common/Cache/GraphicManager/ Increase the cache for graphics.

org.openoffice.Office.Common/Cache/GraphicManager/ Increase the cached graphic objects.

Disabling OpenCL:

Menu —> Tools —> Options —> LibreOffice —> OpenCL – Uncheck Enable OpenCL

Optimizing formulas and calculations:

Menu —> Data —> Calculate – Disable Automatic Calculation

As for recovering the file’s contents, you can try:

First, you should make a backup of the document.

Then, change the extension to .zip. You must enable the option to view extensions in Windows, or you will only change the name.

Once the file has been converted to .zip, you must open it with WinZip, for example.

Exploring the contents of the .zip file, you should find a file named content.xml.

Create a new, blank document with the LibreOffice application you use, save it, and change the extension to .zip.

In this blank document, once opened with the application for managing .zip files, replace the content.xml file with the file from the corrupted file.

Save the file with the .zip extension and then change the .zip extension to .odt or .ods, depending on the application you used, and try editing it with LibreOffice.

[Abonnee]

Thanks Kyodake for your detailed answer.
I tend to praise the open source software far above commercial, i.e. Microsoft software. But I must keep in mind open source is software too and Murphy is everywhere…
I followed your suggestions on the configuration.
Your zip-solution wasn’t working because the file appeared to be too corrupted and couldn’t be opened by the zip-app. I think I have to recreate my worksheet.
But now I have better settings, I learned about the zip-solution (which worked fine on a working sheet) and I will backup the file manually on a regular basis. Apparently the automatic backup creation on saving time will not do, because the same problem was saved in this backup too.
So thanks for teaching me!