Excel “file format and extension don’t match” when merging files

What the warning is telling you

Excel shows "The file format and extension of 'report.xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it." It checks the first bytes of the file against the extension. Many reporting tools, CRMs and banking portals export an HTML table or XML and name it .xls so it opens in Excel. Excel can usually open it after the warning; the trouble starts when you merge.

• Power Query's Excel connector cannot read it and fails, often with a message that the file is not in the expected format.
• A From Folder combine stops on the first mislabelled file.
• Python's pd.read_excel raises an error because the file is not a real workbook.

Treat the warning seriously if you don't know the file's origin. For exports you downloaded yourself from a known system, it is almost always a harmless format mismatch.

Identify the real format

Open the file in Notepad, TextEdit or VS Code (not Excel) and look at the first characters.

Convert one file

1. Open the file in Excel and accept the warning (only for a trusted source).
2. Check the data looks right: HTML exports sometimes include title rows or merged cells above the table.
3. Choose File > Save As, pick **Excel Workbook (*.xlsx)** and save under a new name.
4. Use the new .xlsx in your merge.

Convert a whole folder

For a monthly batch, convert in code. The script below checks each file's first bytes and handles HTML, CSV and real workbooks; XML Spreadsheet 2003 files are easiest to re-save from Excel.

import glob
from io import StringIO
import pandas as pd

frames = []
for f in sorted(glob.glob("exports/*.xls")):
    with open(f, "rb") as fh:
        head = fh.read(8)
    if head.startswith(b"\xd0\xcf\x11\xe0"):          # real .xls (needs xlrd)
        df = pd.read_excel(f, dtype=str)
    elif head.startswith(b"PK"):                        # real .xlsx with the wrong name
        df = pd.read_excel(f, engine="openpyxl", dtype=str)
    else:
        text = open(f, encoding="utf-8", errors="replace").read()
        if "<table" in text.lower():
            df = pd.read_html(StringIO(text))[0].astype(str)  # needs lxml
        else:
            df = pd.read_csv(StringIO(text), sep=None, engine="python", dtype=str)
    df["source_file"] = f
    frames.append(df)

pd.concat(frames, ignore_index=True).to_excel("merged.xlsx", index=False)

If every file turns out to be plain CSV, simply renaming them to .csv lets Power Query's From Folder treat them as text files; see combining Excel files into one spreadsheet for the folder steps. HTML exports may hold more than one table, so check read_html picks the right one.

After converting

• Compare row counts in the original export (or the web report) and the converted file.
• Check numbers and dates converted from HTML text: thousand separators and currency signs can keep them as text.
• Ask the system owner whether a true .xlsx or CSV export exists; it removes the step next month.

Analistable reads real .xlsx and CSV files in your browser, so converting mislabelled exports first is also the way to load them there. The merge Excel files guide compares merge methods once the files are clean.

Why systems export like this

Writing a real .xlsx file needs a library that builds the zipped XML package. An HTML table or a CSV is much simpler to generate, and naming it .xls makes it open in Excel on double-click. Older Excel versions accepted this silently; since Excel 2007 the extension check warns instead. So the exports are not damaged, just mislabelled, and the data inside is usually fine once converted.

That simplicity has side effects worth checking after conversion:

• HTML exports keep formatting, not types. Numbers like 1,250.00 or £1,250 may stay as text, and dates arrive as whatever text the web page showed.
• Title and footer rows (report name, run date, page totals) often sit above and below the table and must be removed before stacking.
• Merged cells in HTML headers become merged cells in Excel; see merged cells breaking sorting and filtering.
• CSV content with an .xls name may use semicolons or tabs, depending on the system.

After removing four extra rows from each HTML file, the combined sales data has 408 + 384 = 792 rows.

Frequently asked questions

Is it safe to open a file with this warning?

If you downloaded it yourself from a system you trust, it is usually just an HTML or XML export with an .xls name. If you don't know where it came from, don't open it.

Why does Power Query fail on these files?

Its Excel connector expects a real workbook. An HTML or text file with an .xls name isn't one, so convert or rename it first.

Can I turn the warning off?

There is a registry setting (ExtensionHardening) that controls it, but turning it off weakens a safety check. Converting the files is the better fix.

How can I tell what format a file really is without opening it in Excel?

Open it in a text editor and look at the first characters, or on a Mac or Linux run file name.xls in a terminal, which reports HTML, XML, text or a Composite Document (real .xls).

Will renaming a file fix the warning?

Only when the content is CSV or a real .xlsx with the wrong extension. Renaming an HTML file to .xlsx makes it worse, because Excel then refuses to open it as a workbook.

Related guides

• How to combine multiple Excel files into one spreadsheet
• Kutools Combine alternatives: built-in Excel and browser options
• How to merge data from closed workbooks in Excel
• How to merge Excel files with Power Automate
• How to merge Excel files without uploading them