Thursday, May 12, 2022

Excel's "The file is corrupt and cannot be opened"

I wonder when Microsoft will be able to give some more clues about what Excel doesn't like in an XML file instead of this generic and useless message. 


It shouldn't be that difficult to write a log and show it to the user or at least display the first n errors, chances are that the remaining ones are of the same kind. 

Meanwhile, I had to use the binary search approach to iteratively cut in half the XML file containing some 3500 rows (roughly 250000 lines of XML) and find out at the 10th attempt and after more than an hour that a date value written as 15/12/0002 was the real reason for this message. 

It's also interesting to note that whilst this date value is certainly the result of a user entry error and a missing validation problem in the source application, the date value per se is not invalid at all, so I find that this is a twofold failure on Excel's part, because for Oracle it was perfectly acceptable.

By the way I also found out that Excel doesn't complain for the presence of spare "&" characters but some XML syntax checking programs actually do report these occurrences as errors.

So, in case you get "The file is corrupt and cannot be opened" error message, be prepared to spend some time if dealing with a large file in pursue of the offending value.

LOG(2, rows) should give you a good estimate of how many iterations are necessary to pinpoint the bad spot.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles