Xls file suddenly opens only as "text import"

English
#1

For weeks I’ve been happily working on an xls spreadsheet and saving it in LibreCalc. But today when I tried to open the xls file, suddenly it wouldn’t open. All that appeared was a “text import” window. The file is on a USB drive, so I tried to open it on 2 different laptops and a desktop (with Linux OS or MAC OS 10.something). I got the same frustrating result every time.
And there is no back up file.
I tried everything in “Unable to open .xls file”, and “try to open an xls file in libreoffice calc opens a "text import" subroutine”, but none of it worked for my situation.
Help!!!

#2

There are two potential causes here

  1. File stored on USB drive can be subject to deterioration of the memory in the USB drive if it is a pen drive. Removing a USB drive without unmounting it can lead to cached data not being written to USB. I would suggest to use USB for temporary storage and portability.
  2. Working in non-native format, .xls. You would have seen a warning about saving in another format at some stage but probably turned the warning off. Always work in .ods format and if required export a copy as xls. [Tutorial] Differences between Microsoft and AOO/LO files (View topic) • Apache OpenOffice Community Forum

Now is a good time to make a copy of your file and store it in case of further problems. You are lucky, you can import some or all of your data into a new spreadsheet. You will have lost all formulas (result converted to string) and formatting however. In import text dialogue make sure you have Detect special numbers box ticked and that you select the correct column type if it can be misinterpreted, e.g. Date (D/M/Y), Date (M/D/Y)

I prefer to use File > Save a Copy (as FilenameYYYY-MM-DD) before I make important changes. You can choose how to backup your own data, Preventing data disaster - The Document Foundation Wiki

#3

Thank you for your prompt reply, EarnestAl. I checked “Detect special numbers”, but “Column type” is grey—unusable. The preview of the spreadsheet at the bottom of “Test Import” still shows only gibberish. And the file opens full of only gibberish. What’s your next suggestion?

#4

If you have worked with XLS, and after some ordinary save, you receive garbage, it means that your file is corrupt on the media. You only can hope to restore it from some backup (if you have it).

  1. Check backup directory (see LibreOffice’s Options|Paths for the backup directory location).
  2. Try to use some third-party utilities that allow to “undelete” (restore) erased data on your disk - some previous data may still be available there, unless you did some more writes to the USB drive after the problem.

I hope you will succeed; if these don’t help, I suppose you need to re-create your data from scratch. Please follow @EarnestAl’s advise wrt safe USB operation and choice of storage formats; create some backup strategy for your important data.

#5

Sorry, I can’t help with corrupted files. I thought that the text was available through the text import :slightly_frowning_face:

#6

Hello MikeGanski,

Thank you for replying. I respond to your suggestions below with more questions:

  1. The backup directory in LibreOffice’s “Options / Paths” is empty. How do I activate it? And, will it back up files from a USB?

  2. What are third-party utilities that you can personally recommend? And what do you mean by “unless you did some more writes to the USB drive after the problem”? On the USB I made many copies of the corrupted file in order to try the “solves” I found online, such as to rename the file with the file extension html, zip, (etc.). Other files on the USB are fine. Would using a third-party utility to try and retrieve data from my corrupted xls file risk harming my many gigs of other files that are working fine?

#7
  1. You control backup behavior at Options|Load/Save|General. Of course, enabling e.g. Always create backup copy now will not help you to recover something from the past, but it’s useful to enable when you have something unreliable, such as working on removable device as primary storage.
  2. I can’t advice you here; I see you mentioning Linux and macOS, when I’m a Windows user; and besides, I haven’t needed such tools for more than 10 years already, so couldn’t advise even Windows tools.
    The “On the USB I made many copies of the corrupted file in order to try the “solves”” is very unfortunate. When something is deleted on a drive, its bytes are not immediately destroyed, only its location is forgotten; and proper low-level restoration tools are able to retrieve those still existing bytes from the disk. But following writes to the same storage may (and usually will) write to the same bytes that were used for the erased file; and that means that those bytes get lost forever. That is why there is an advise to never write to the media where you had some data loss, until you finished restoring data. (I realize that it’s a sad truth, but I assume you have now no hope to restore your data. I just hope that knowing that, you will be better prepared next time, which I hope will not happen to you.)
#8

I enabled “Always create backup copy” so this problem hopefully won’t happen again in the future. Thank you for your clear, lucid explanation of the situation. And I thank you for your sympathy.

#9

If you were super lucky, it is conceivable that Excel might be able to open it. If it is, by chance, an XLSX file, then you could consider seeing if it will open as an archive… Is it something you can post?

#10

I tried having Excel open the corrupted xls file. It didn’t recognize it as an Excel file. As for posting–the file is private financial data.

#11

Yes, it looks like this corrupted xls file will just have to be recreated from scratch. Thanks, everyone, for sharing your knowledge, wisdom, and trouble-shooting skills!

#12

Recipe for perfect data loss:

  1. Store database data in a spreadsheet document.
  2. Store the working copy of the spreadsheet document on a removable drive.
  3. Store the spreadsheet document in a proprietary document format.
  4. A binary proprietary document format of course.
  5. An outdated binary proprietary document format.
  6. Edit the file with some program other than the proprietary one which was designed to edit the file as its own proprietary file format.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.