Fixing Excel Found Unreadable Content After a Script Export

A script finishes, the report opens in Excel, and a dialog appears: "We found a problem with some content in 'report.xlsx'. Do you want us to try to recover as much as we can?" After you click Yes, some cells are blank or a sheet is gone, and a log says things like "Removed Records: Cell information from /xl/worksheets/sheet1.xml part". The file is not corrupt in the sense of being broken in transit. A part of it breaks the format rules. This post lists the causes that come from generated files, shows how to read the log, and gives a way to find the bad data. It is a spoke of understanding Excel errors when data comes from code.

What the message means

An xlsx file is a zip of XML documents. When Excel opens it, it validates each part against rules stricter than XML itself: allowed characters, value ranges, unique names, correct references. If a part violates a rule, Excel cannot trust it. It offers to recover by dropping the record that failed. The message is therefore about content, not about the file container, and it is a different failure from "file format or extension is not valid" (why Excel says the file format or extension is not valid).

The repair is lossy. Whatever Excel dropped is gone from the repaired copy, so do not save the repaired file over the original, and fix the generator.

Read the repair log

After recovery, Excel shows a dialog with a link to a log, and the log names what it removed. The kinds of entry:

• Removed Records: Cell information from /xl/worksheets/sheet3.xml part: a bad cell value or style in sheet 3.

• Removed Records: Formula from /xl/worksheets/sheet1.xml part: a formula that does not parse.

• Repaired Records: Table from /xl/tables/table1.xml part (Table): a table with duplicate or invalid header names.

• Removed Feature: Drawing from /xl/drawings/drawing1.xml part: a chart or image problem.

• Removed Records: Named range from /xl/workbook.xml part: a bad defined name.

The part path tells you the sheet. Match sheet3.xml to the third sheet in the workbook's order, then look at the data written to that sheet. If you are not sure, rename the file to .zip and look inside.

Causes that scripts produce

Illegal control characters in strings. XML 1.0 forbids characters below 32 except tab, line feed and carriage return. A string from a database or a PDF with a stray \x0b (vertical tab) or \x00 writes into the sheet XML and invalidates the cell. openpyxl raises IllegalCharacterError for these; other writers may not. Strip them before writing: re.sub(r"[\x00-\x08\x0b\x0c\x0e-\x1f]", "", s).

Text longer than 32,767 characters. That is the cell limit (Excel cell contains more than 32767 characters). Some writers truncate with a warning, others write it and produce an invalid cell.

Invalid sheet names. A sheet name must be 31 characters or fewer, must not contain \ / ? * [ ] :, and must not start or end with an apostrophe. Names are case-insensitive unique, so Sales and sales collide. A name built from customer data, such as Q1/Q2 Report, breaks this. XlsxWriter raises an exception; other tools may not check.

Duplicate or empty table headers. An Excel Table needs unique non-empty header names. A DataFrame with an unnamed index column or two columns called Total writes a table that Excel repairs by renaming.

NaN and infinity. These are not valid cell numbers. XlsxWriter by default raises an error, with an option nan_inf_to_errors that writes them as #NUM! or #N/A. pandas writes NaN as an empty cell by default (na_rep=""), and an infinity as an error value or an invalid cell depending on the engine. Replace them before writing.

Dates out of range. Excel's date system starts in 1900 (or 1904). A date before 1900 or far beyond year 9999 cannot be stored as a serial number. A datetime of year 1 from a database default writes a negative serial.

Timezone-aware datetimes. Excel has no timezone support. openpyxl and pandas raise an error on timezone-aware datetimes with some engines; convert to naive local or UTC first.

Overlapping merged ranges. Two merged ranges that share a cell are invalid. A loop that merges a block per group can overlap when the groups do.

Hyperlinks that are too long. Excel limits hyperlink addresses to about 2,079 characters. A long tracking URL written as a hyperlink can be dropped.

Too many styles or formats. A script that creates a new style object for each cell can exceed the limit of unique cell formats (64,000), and Excel repairs by dropping styles. Reuse styles.

Invalid number formats and conditional formats. A format string with an unescaped quote, or a conditional format pointing outside the sheet.

Mixing writers. Opening a workbook with one library and saving with another, or editing the zip by hand, can leave dangling references to parts that no longer exist.

Bisect the data

If the log does not make the cause obvious, find the bad record by halving. Write the first half of the rows to a file and open it; write the second half; whichever fails contains the bad row. Repeat on the failing half. With 100,000 rows this takes about seventeen steps, fewer if you test with a script, not by hand.

from openpyxl import Workbook, load_workbook

def opens(rows):
    wb = Workbook()
    ws = wb.active
    for r in rows:
        ws.append(r)
    wb.save("probe.xlsx")
    load_workbook("probe.xlsx")      # a second reader catches some, not all, problems

A reader library is more forgiving than Excel, so a clean load does not prove Excel will accept the file. The only reliable validator is Excel itself, or Microsoft's Open XML SDK Productivity Tool, which validates a package against the schema. For an automated check, use the SDK's validator in a build step, or keep a manual open-in-Excel step for new report types.

Inspect the zip

Rename a copy to .zip and extract it. Open xl/worksheets/sheet3.xml in a text editor and search for the suspect cell. Look for <c r="B7" t="inlineStr"> with odd bytes, a <v>nan</v> value, or an <f> with a formula that does not parse. Compare with the same cell from a file that opens cleanly. This direct look often reveals the problem faster than guessing.

unzip -o report.xlsx -d report_x
grep -c "nan" report_x/xl/worksheets/sheet3.xml

Prevent it

Add the defences to the generator once. Sanitize every string before writing: strip illegal characters, cut to 32,767. Validate sheet names and make them unique. Replace NaN and infinity with an explicit value. Check dates against the supported range and convert timezone-aware values. Reuse styles. Give every Excel Table unique headers. Then add a test that generates a workbook from a deliberately nasty fixture, with a control character, a long string, a slash in a name and a NaN, and asserts that the writer either handles each or fails with a clear error.

Also keep the writer current. Libraries fix XML validity bugs, and an old version may produce a file a new Excel rejects.

When repairing the file is not the answer

The data is the problem. If a row contains a control character because the source is corrupt, repairing the workbook hides it. Fix or reject the row upstream.

Excel is not the right output. If a million-row export produces a fragile file, write CSV or Parquet for machine use and keep Excel for the summary (Excel files that are too big or too slow).

The file came from someone else. You cannot fix their generator. Ask for a CSV, or read the file with a tolerant library and rebuild a clean workbook.

The repair succeeded and nothing important was lost. Check the log for what went. If it is only a cosmetic style, document and move on. If it is cell data, it is not acceptable.

See openpyxl's documentation of its handling of illegal characters, XlsxWriter's working with Pandas and NaN values, and Microsoft's Open XML SDK for validation. To convert a small extract, use the CSV to JSON converter.