Hello and a Calc XLS file filled with #

[BeekeeperJohn]

Hello. Never posted here before so not sure where this should go. The calc section? Also if the problem has been reported and / or solved before, apologies, but could you point me to the thread please?

I've used Open Office Calc through the years to open, modifiy and save as, Excel spreadsheets as they are used a lot by my contacts. I run Windows 10 and have tried the following with Open Office 4.1.3 and now with 4.1.7 (just upgraded).

Today though, if I right click on a .xls file and try to open with Open Office Calc, it goes into Text File Import mode



Then if OK is pressed it comes up either with a blank sheet but with the selected file's name.

If I run Open Office Calc and use File => Open, things get weirder. First the Text Fillter box appears



then when OK is selected, it switches to Open Office Writer, displaying a page filled with # characters.



We really could use a fix for this as our only solution is to borrow a copy of Excel and convert all our files :-/

Ideas welcome I'll check back occasionally. Thanks in advance. I hope

[RoryOF]

Your file is damaged beyond repair, possibly due to corruption in transmission or in transfer. Request another copy from the source.

[BeekeeperJohn]

Your file is damaged beyond repair, possibly due to corruption in transmission or in transfer. Request another copy from the source.

Sorry to disgaree, but when we opened the file with Excel, then saved it in .ods format, it then opened fine. And the same behaviour is shown by lots of other Excel files.

Thanks for your answer, but that ain't it!!

[Hagar Delest]

So you mean that such a file would still open fine in MS Excel?
Can you upload such a sample file?
We have some robust evidence so far that once you see the hashes, there is nothing anymore in the file. Proving otherwise would be a first and would change the situation!

[BeekeeperJohn]

Thanks. I'll try to work out how to upload a sample file. Bear with me. But as I said. I tried to open two or three files using both the methods above, with the same result each time. We then used a copy of Excel to open the files and the content was fine.

We then saved those files from Excel in .ods format, opended them in Calc and they were fine. Plenty of content, multiple sheets, lots of formulae. All working.

[Zizi64]

Try/use the LibreOffice if you need edit foreign file formats.

[BeekeeperJohn]

I'm happy to try Libre Office & wilkl let you know the results. Thanks.

[RoryOF]

We really do need to see an uncorrected original .xls file, so we can find the cause.

[BeekeeperJohn]

We really do need to see an uncorrected original .xls file, so we can find the cause.

Yep. Understand. The but 'uncorrected original file(s)' contain customer financial data which I'm not going to share. There is another candidate file which is exhibitiing the same behaviour. But I need to check the file contents (using Excel on a copy) then upload a copy here. The Excel machine is available only in the evenings. Please bear with me.

Regards and thanks

[John_Ha]

Trust us. Any file which opens in Writer and is full of ##### is corrupted beyond repair. See Why is my file full of #####? at viewtopic.php?f=71&t=85038#p493247

If you want to confirm it yourself open it with a HeX editor like HxD and you will see it is full of nulls.

[RoryOF]

@John_Ha: An .XLS file should not be an archive; we know OO identifies file types internally, so why does it treat the reported files as an archive?

[John_Ha]

Rory

Without sight of the file we are guessing. I repeated what the OP reported using report.odt from my #### post - it is full of nulls - but renamed to report.xls and uploaded here.

I right click on a .xls file and try to open with Open Office Calc, it goes into Text File Import mode

Correct.

Then if OK is pressed it comes up either with a blank sheet but with the selected file's name.

Correct.

If I run Open Office Calc and use File => Open, things get weirder. First the Text Fillter box appears then when OK is selected, it switches to Open Office Writer, displaying a page filled with # characters.

Correct.

So, while I don't know why AOO chooses to open a flat binary file with a .xls qualifier with Writer instead of Calc, I concluded that a file full of nulls did exactly what the OP reported.

Hence his file is full of nulls.

[RoryOF]

I agree, John, that the reported effects are as we expect; but these are puzzling. I await a sample file, as I am sure do you also, that the problem can be investigated further. It is puzzling that the same(?) .xls file can open correctly with Excel but not with Calc, unless the .xls is a .csv file in disguise.

Perhaps, if selection of a non-confidential file is an obstacle, the OP could try starting Calc, then importing the file as a csv (Help under csv gives assistance).

[Hagar Delest]

I don't know why AOO chooses to open a flat binary file with a .xls qualifier with Writer instead of Calc

Devs could tell for sure. IMHO, when AOO is lost, I mean when soffice cannot identify what kind of file it is, then it switches to the simplest mode it knows: a mere text editor to display characters as they come.
Since the MIME type must be wrecked in the file header (full of nulls), it may trigger that downgraded mode.

[John_Ha]

It is puzzling that the same(?) .xls file can open correctly with Excel but not with Calc

I am not sure the OP has described that correctly and I am pretty certain it is a different file. "Opening correctly" means the user data is there and it isn't present in a file which shows only ##### in Writer.

A possible explanation is a .xls file which starts with multiple nulls and then continues with the correct data for a .xls file. When I create one and open it with Calc I get as before except, after the initial nulls, Writer shows binary content. A possible explanation could therefore be that Excel ignores preceding nulls in a file ... but if so, the OP would presumably have reported he saw binary content when Writer opened it.

IMHO, when AOO is lost, I mean when soffice cannot identify what kind of file it is, then it switches to the simplest mode it knows: a mere text editor to display characters as they come.

That sounds logical.

[BeekeeperJohn]

Hmm. Not sure what to make of today.

I chose another .xls file last modified by Open Office in the same time period (about 2 months ago).

I took a copy of the file. (Call this Copy 1)

I tried to open Copy 1 using the right click, use other app method and once again the Import Text function came up, as per the original report. The Import Text function was exited using close, X, top right, i.e. no changes saved.

I then made another copy of the file. (Copy 1 = > Copy 2)

I then tried to open Copy 2 file using File => Open in Open Office and it brought up the same results as reported in the original report, i.e. ASCII Filter box followed by switching to Writer and a document file full of # marks.

I then tried to open the original file in Excel, and it opened just fine. I saved it to a new Excel file and then to an ODS file.

I tried to open Copy 1 and Copy 2 using Excel and they were both corrupted.

So this would point to the first open by Open Office, the one initiated by right clicking on the file in Explorer and choosing the scalc.exe app, corrupting the file.

But before you Bug Busters dash off to bust bugs there is, fortunately or unfortunately, more to tell.

I took another copy of the original file, call this Copy 3.

I brought up Open Office and used the File => Open function on Copy 3 and it opened just fine! Hmm. ( I wouldn't normally open a spreadsheet this way. Why waste clicks and time, after all. )

I checked the program directory and the pathway offered by Explorer to scalc.exe and they were correct.

Finally, with no intervention from myself, Windows has elected to appoint Open Office Calc as the default application for .xls files. Something I seem to recollect it had refused to do before. And .xls files are starting in Open Office Calc with a double click from Explorer. Odd.

So apologies for wasting your time. I guess the only things we (you) can take away from this are that even if a .xls file appears to be unusable or unopenable by Open Office Calc, it's worth trying to open it with Excel before you give up on it!!

Oh. And always have a backup

I tried to find a file that displays the corruption behaviour, but they all contain customer financial data. If I find one, I'll be back! But I really can't let these out into the big wide world. Newly created files don't display the error.

Trust me. This is as frustrating for me as it must be for you. I worked 40 years in software development (now retired).

Many thanks again for your time and advice. I'll get back to the bees.

[RoryOF]

We know that OpenOffice will not overwrite a file unless instructed (/File /Save, or Ctrl S), so we can rule that out.

I cannot recollect if I asked: have these files been brought forward from an older computer, or from external storage, a USB device, or a DVD?

[BeekeeperJohn]

It is puzzling that the same(?) .xls file can open correctly with Excel but not with Calc

I am not sure the OP has described that correctly and I am pretty certain it is a different file. "Opening correctly" means the user data is there and it isn't present in a file which shows only ##### in Writer.

A possible explanation is a .xls file which starts with multiple nulls and then continues with the correct data for a .xls file. When I create one and open it with Calc I get as before except, after the initial nulls, Writer shows binary content. A possible explanation could therefore be that Excel ignores preceding nulls in a file ... but if so, the OP would presumably have reported he saw binary content when Writer opened it.

IMHO, when AOO is lost, I mean when soffice cannot identify what kind of file it is, then it switches to the simplest mode it knows: a mere text editor to display characters as they come.

That sounds logical.

You may be onto something there. That's my gut feeling too. I think Open Office mis-identified the file (even though it created it) and went into text import mode. Excel did things differently and identified the file correctly.

What I'd find more worrying is that Open Office corrupted the file, even though I exited from the Text Import box using the X top right of the window.

And it wasn't different files. The file in question was my master customer account file. It's not something I'd make a mistake over.

You folk were right about the ## though. Open Office had already corrupted the file when I tried to open it using that method.

See also today's results below. At least I've got my data back And don't have to buy Excel! Double result!

[BeekeeperJohn]

We know that OpenOffice will not overwrite a file unless instructed (/File /Save, or Ctrl S), so we can rule that out.

I cannot recollect if I asked: have these files been brought forward from an older computer, or from external storage, a USB device, or a DVD?

Yes. As they contain customer financial information they are stored offline on USB sticks and hard drives. They are copied to the work computer only when required, modified and re-archived.

[RoryOF]

There may be the problem. USB devices - in particular USB sticks - require strict attention be paid to the USB device removal protocol from the operating system. Any failure to adhere to that can cause corruption such as you report. With USB hard drives if the drive is removed hastily (i.e., without observance of the protocol) that may also cause failure, as the data may be cached internally in the drive and not yet written to disk when the power/USB connection is removed.

Also, when USB sticks were new to the market, there were reports of failure of OO files when worked directly on the USB stick; whether these were failures in the stick itself, or failures in the observance of the removal protocol, it is not now possible to ascertain.

Edit: It is also said that USB sticks only support a limited number of read/write cycles, and then may suddenly fail with age and use.