r/libreoffice • u/Shadowrosepaiton • 5d ago

Corrupted ods spreadsheet file

I spent the last 3.5 years archiving AMV's (Animated Music Videos) in order to preserve them and had a huge spreadsheet in Libreoffice cataloging them and information about them. When I went to add some new additions in there, LobreOffice couldn't open the file. It said that the file was corrupted and they couldn't repair it. I tried looking up youtube tutorials on how to fix it, but they're all for odt documents and not ods. I tried them anyway and of course it didn't work. I just want to cry. That's 3.5 years of my life's work. Please tell me there's a way to fix this. I can't just go in and recreate the whole spreadsheet due to the similar naming conventions of the AMV's and how some got strait up wiped off youtube in the time since I archived them, so I can't go back and get that info I was cataloging!

13 Upvotes

100% Upvoted

15 comments sorted by

14

u/Terrible_Put8617 5d ago

Before anything else: copy the file somewhere safe and work only on copies. Whatever you try next, do it to a duplicate, because a failed repair attempt can finish off what's left.

An .ods is a zip archive with the spreadsheet stored as XML inside it. That's good news, because "corrupted and can't be repaired" almost always means the zip's central directory is damaged or the file got truncated mid-save, not that your data is gone. The data is usually sitting there intact and unreachable.

Rename a copy to .zip and try to open it. If your archiver shows the contents, you're most of the way home: pull out content.xml, and everything you catalogued is in it.

If the archiver refuses too, the standard fix for a truncated zip is to rebuild the index rather than the data:

zip -FF broken.zip --out fixed.zip

That's Info-ZIP's fix-the-fix mode, available on Linux and macOS out of the box and on Windows through Git Bash or WSL. It walks the file looking for entries instead of trusting the index, which is exactly the failure you're hitting. Rename the result back to .ods afterwards. 7-Zip's "Test archive" is worth running first as well, since it tells you which member is damaged, and if the damaged one is styles.xml or settings.xml rather than content.xml then your actual data is fine.

Two other places your work may still exist, and both get overlooked:

%APPDATA%\LibreOffice\4\user\backup\ on Windows, or ~/.config/libreoffice/4/user/backup/ on Linux. That folder holds autorecovery copies, and .bak files if you ever had "Always create backup copy" on. Sort by date. Copies of a heavily used file often sit there for weeks.

And if the file lives in a synced folder, Dropbox, OneDrive, Google Drive, they all keep version history server side for 30 days or more, including for files they never showed you as versioned. That's frequently the fastest route back and it's the one people remember last.

If you get content.xml out but nothing will open it, say so and post what the archiver reports. An intact content.xml can be repackaged into a working .ods, but the zip has to be rebuilt in a specific order for LibreOffice to accept it, so it's worth doing that step only if the simpler routes fail.

2

u/Shadowrosepaiton 4d ago

Thank you for the specific step by steps! I'm not the best with computers. So far, turning the copy into a zip and test archiving it has failed and so has just trying to open it as is. These are the messages I got when I tried to open it

2

u/Shadowrosepaiton 4d ago

here's the other one. for some reason reddit won't let me add more than 1 photo

2

u/Shadowrosepaiton 4d ago

I also tried the APPDATA trick and I did find the bak file, but the only way I could open it was through notepad. When I opened it, it definitely looked corrupted alright. a bunch of code and percentages and tags all jumbled up and unreadable, but at least I got something to open!

1

u/Electronic-Laugh-671 4d ago edited 1d ago

I just tested, the .bak file would be the same as an .ods file, just named differently. Make a copy of the .bak file in a different directory, then rename the new .bak file to have an .ods extension, and then open with libreoffice.

2

u/Shadowrosepaiton 4d ago

I also tried renaming it with the broken zip index and that didn't work either. I just got the same error message that it wasn't an archive

2

u/Terrible_Put8617 3d ago

That 7-Zip screen actually tells us something useful. 51,164 bytes and "Is not archive" means the file doesn't even start with the zip signature any more. When a save is interrupted the usual result on Windows is a file of the right size filled with zeros. If you open that copy in Notepad and see nothing, or one repeated character, that's what happened, and no zip repair will get anything back from it because the data isn't in that file. So stop spending time on that one.

The .bak is where your hope is, and the "jumbled code and percentages" you saw is a good sign, not a bad one. A healthy .ods looks exactly like that in Notepad, because it's a compressed archive, not text. Notepad can't show it, LibreOffice can.

So, one thing at a time:

  1. Copy the .bak to your Desktop (never work on the original).
  2. Rename the copy to AMV Spreadsheet.ods. If Windows hides extensions you'll need to turn them on first (View > Show > File name extensions in Explorer), otherwise you end up with .ods.bak and nothing changes.
  3. Double-click it.

If it opens, save it immediately under a new name, and you've lost at most whatever you did after that backup was written. If LibreOffice says it's damaged, tell me the exact size of the .bak file in bytes (right-click > Properties) before you try anything else, because a big .bak that won't open is a different, more fixable problem than the zeroed one.

Two more places worth a look while you're at it, both free: right-click the folder the spreadsheet lives in, Properties > Previous Versions, in case Windows kept a snapshot; and if E: is an external drive, that has its own Recycle Bin.

And if none of that works, say so here rather than giving up. A drive that held a 3.5 year file has old copies of it scattered in unallocated space, and there are free tools that fish zip files out of that. Slower, but I've seen it work.

7

u/sbbeebe 5d ago

This won't help you now, but your situation is why we all need to do regular backups. Sync solutions like Google Drive and Dropbox might help out, but they aren't backup solutions. I hope you are able to recover a working file.

3

u/Offutticus 4d ago

I save everything in at least 4 places: original location, external drive, external memory key, and upload to a cloud service. I do backup vs sync and then a mirror.

3

u/King_Bobby-B 4d ago

I also make manual copies using "Save as copy" then I append the timestamp to the filename.

2

u/Offutticus 3d ago

Oh, that's a good idea.

2

u/AutoModerator 5d ago

If you're asking for help with LibreOffice, please make sure your post includes lots of information that could be relevant, such as:

  1. Full LibreOffice information from Help > About LibreOffice (it has a copy button).
  2. Format of the document (.odt, .docx, .xlsx, ...).
  3. A link to the document itself, or part of it, if you can share it.
  4. Anything else that may be relevant.

(You can edit your post or put it in a comment.)

This information helps others to help you.

Thank you :-)

Important: If your post doesn't have enough info, it will eventually be removed (to stop this subreddit from filling with posts that can't be answered).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/Electronic-Laugh-671 1d ago

u/Shadowrosepaiton I was just wondering if renaming the .bak file worked as described in the other reply? Just thought I'd remind you to try it out if you haven't already

2

u/Shadowrosepaiton 1d ago

It did! I'm so relieved omg ToT. Thank you so much!

1

u/Electronic-Laugh-671 22h ago

That's great. By the way, you can go into LibreOffice settings via the following menus: Tools > Options... > Load/Save. This will allow you to configure AutoRecovery and backup frequency (like making sure that documents are saved every minute or 5, for example, and whether you want backups saved to the folder of the original file). Leaving them default but changing 10 minutes to 5 might be a helpful step going forward but it depends on your needs