Excel Cannot Open the File Because the File Is Corrupt

"Excel cannot open the file 'budget.xlsx' because the file format or file extension is not valid" is one message. "Sorry, we couldn't find... it may be corrupt" is another. This post is about the second kind, a file that is meant to be an xlsx workbook and has been damaged. The data is often still in there. The container is broken, but each worksheet is a separate text file inside the zip, and those can usually be read even when Excel gives up. This post lists the steps in order of effort: test, repair, extract and read. It is a spoke of understanding Excel errors when data comes from code.

Start with a copy

Do not experiment on the only copy. Copy the file somewhere safe before anything else, and keep the original untouched. Check the file size and date. Then look for a better source of the same file: a backup, a version history in OneDrive or SharePoint, the email attachment, the previous night's export. Recovery is always second best to restoring.

Find out how it is damaged

An xlsx is a zip. Test it:

unzip -t budget.xlsx

The output lists each part with OK or an error. Typical results:

• End-of-central-directory signature not found or cannot find zipfile directory: the file was cut short. The central directory sits at the end of a zip, so a truncated file loses the index even though most of the data is there.

• bad CRC on specific parts: those parts were damaged, others may be fine.

• No errors detected in compressed data: the zip is intact, and the problem is inside an XML part (see fixing Excel found unreadable content after a script export).

Check the file size against the expected size. A file that should be 4 MB and is 1.2 MB was cut off. A zero-byte file has nothing to recover. If it begins with something other than PK, it is not an xlsx at all (why Excel says the file format or extension is not valid).

Common causes of damage

• An interrupted save or copy. The machine crashed, the laptop slept, or a network share dropped the connection while writing.

• A full disk. Excel wrote what it could and the rest was lost.

• Sync conflicts. A cloud sync client uploaded a partial file, or two machines wrote the same file and the client kept a half of each.

• A failed download or email gateway. A gateway that rewrote attachments, a proxy that cut the stream.

• Antivirus quarantine. A scanner replaced the file with a stub.

• Storage errors. A failing disk or USB stick.

• Editing the zip by hand or with a buggy tool. A script that rewrote the archive incorrectly.

Knowing the cause matters for the fix. If it is a sync client, check for conflict copies in the folder before you try recovery.

Open and Repair in Excel

In Excel choose File, Open, Browse, select the file, click the arrow beside the Open button and choose Open and Repair. Excel offers two buttons:

• Repair tries to restore as much of the workbook as it can, including formulas and formatting.

• Extract Data gives up on the workbook structure and pulls out values and formulas. If you choose it, you can then convert formulas to values or recover the formulas.

Try Repair first. If it fails, try Extract Data. Save the result under a new name. Expect some loss: formatting, charts, defined names and data validation are the usual casualties, and Excel should list what it removed.

If Excel crashes on opening, start it in safe mode by holding Ctrl as it launches, or run excel /safe, and try again.

Extract the sheets by hand

If the zip is readable, even partly, you can pull the data out yourself. Make a copy, rename it to .zip and extract. The parts you want:

• xl/worksheets/sheet1.xml, sheet2.xml, and so on: the cells of each sheet.

• xl/sharedStrings.xml: the text values, referenced by index from the sheets.

• xl/workbook.xml: the sheet names and order.

• xl/styles.xml: formats, needed to tell dates from numbers.

For a truncated archive, unzip extracts what it can, and Python's zipfile fails on a missing directory. Info-ZIP's zip -FF can rebuild a missing central directory: zip -FF broken.xlsx --out fixed.xlsx. After that, try opening fixed.xlsx. The -FF mode scans the file for local headers and reconstructs the index, which works when the data is intact and only the end is lost.

A tiny reader for the worksheet values:

import zipfile
import xml.etree.ElementTree as ET

NS = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}

with zipfile.ZipFile("fixed.xlsx") as z:
    strings = [
        "".join(t.text or "" for t in si.iter("{%s}t" % NS["m"]))
        for si in ET.fromstring(z.read("xl/sharedStrings.xml")).findall("m:si", NS)
    ]
    sheet = ET.fromstring(z.read("xl/worksheets/sheet1.xml"))
    for row in sheet.iter("{%s}row" % NS["m"]):
        values = []
        for c in row.findall("m:c", NS):
            v = c.find("m:v", NS)
            if v is None:
                continue
            values.append(strings[int(v.text)] if c.get("t") == "s" else v.text)
        print(values)

Cells with t="s" hold an index into the shared strings, other cells hold the number as text. Dates are plain serial numbers, and only styles.xml says which are dates. It is crude, but it recovers the values when nothing else will. For a damaged XML part that no parser accepts, a regular expression over the raw text can pull out <v> values as a last resort.

pandas and openpyxl can often read a repaired copy even when Excel complains: pd.read_excel("fixed.xlsx", engine="openpyxl"). LibreOffice is also a useful second opinion, since it recovers files that Excel rejects: open the file there and save as xlsx or CSV.

Verify what you recovered

Recovery can quietly lose data. Compare against anything you have: the row count from an earlier report, a control total, the last rows. Check that the last row of each sheet is a real row and not a cut-off line. Look for blank sheets, and check that dates survived as dates. Do not treat a recovered file as the same as the original; label it and record what was missing.

Prevent the next one

• Back up. Keep versions. OneDrive and SharePoint version history, a nightly copy to another location, or a git repository for generated files.

• Write atomically. A script should write to a temporary file and rename it when finished, so a crash never leaves a half file under the real name.

• Check after writing. Reopen the file with a library and compare row counts before announcing it is ready (testing that a generated Excel report is correct).

• Watch disk space. A report job that fills a disk does damage far beyond its own output.

• Avoid editing one workbook from many places. Shared files corrupt more often than files with a single writer (shared Excel files and the lost update problem).

When recovery is not worth the effort

The data can be regenerated. If the workbook is a report built from a database, rerun the report. Time spent on recovery is longer than the export.

The file is a copy of something that exists. Check the source system or the sender before opening a hex dump.

The contents are unverifiable. If you cannot tell whether the recovered numbers are right, and the numbers matter, treat the recovered file as a lead, not as data.

Security is in doubt. A file that was damaged by an attack or came from an untrusted source should not be opened on a normal machine (opening untrusted Excel files safely in a pipeline).

Microsoft's page on repairing a corrupted workbook describes the dialogs, and the Info-ZIP project documents zip -FF in its manual. To convert a recovered extract, use the CSV to JSON converter.