xls

Legacy Excel (.xls) reader

Reads legacy Excel workbooks (.xls, BIFF8 — Excel 97-2003) in pure Power Query M. No ACE OLEDB provider, no gateway software, no custom connector.

Power Query reads .xls through the Access Database Engine (ACE), with the usual ACE conditions attached: it must be present, its bitness must match, and it cannot be installed in cloud environments — so in Power Query Online a .xls file forces a gateway even when the file is already in SharePoint or OneDrive. Twenty-year-old exports from instruments, ERPs and finance systems are exactly the files that sit on locked-down shares. This reader parses the binary format itself.

Correctness, not just portability: ACE infers each column's type by sniffing the first rows and returns nulls for values that don't match its guess. This reader decodes every cell from its record type and never drops a value.

Usage

Paste Xls.Workbook.pq into a blank query and name the query Xls.Workbook. Then:

let
    Source = File.Contents("C:\data\legacy-export.xls"),
    Wb     = Xls.Workbook(Source),           // navigation table of sheets
    Sheet1 = Wb{[Name = "Sheet1"]}[Data]
in
    Sheet1

The result is a navigation table with one row per worksheet (Name, Data, Hidden, plus navigator plumbing). Each Data cell is the sheet as a table with columns Column1..ColumnN, like Excel.Workbook without header promotion. Xls.Date1904 in the table metadata reports the date system.

Options

Second argument, optional record, all keys optional:

Key	Default	Effect
IncludeHiddenSheets	false	List hidden and very-hidden sheets (the Hidden column marks them)
PromoteHeaders	false	Use each sheet's first row as column headers
DetectDates	true	Convert date/time-formatted numbers to datetime; false returns raw serials
MaxRows	all rows	Return at most N rows per sheet
Strict	false	Error on tolerated malformations (out-of-range shared-string index, inline string truncated at a record boundary)

Supported

• The CFB (compound file) container, including streams small enough to live in the mini-stream.
• All BIFF8 cell records: RK, MULRK, NUMBER, LABELSST, inline LABEL/RSTRING, BOOLERR (booleans and error cells), BLANK, MULBLANK, and FORMULA with cached number/string/boolean/error results.
• The shared string table across CONTINUE records, including strings split mid-text where each continuation restarts with a fresh flags byte and may switch between 8-bit and 16-bit characters.
• Date/time detection from builtin format ids and custom format codes, including the 1904 date system.
• Hidden and very-hidden sheets; chart and macro sheets are recognised and skipped.

Limitations

• BIFF8 only. Excel 5.0/95 files (BIFF5/7) produce a clear error naming the version. Encrypted or password-protected workbooks (FILEPASS) produce a clear error.
• Formulas are not evaluated; the cached result Excel stored is returned.
• Error cells surface as cell-level errors (like Excel.Workbook), with the Excel error name (#DIV/0!, #N/A, ...) in the message. Use Table.RemoveRowsWithErrors or try to handle them.
• Inline LABEL strings that span a CONTINUE record are truncated at the boundary (error under Strict = true). Excel puts cell text in the SST, where continuation is fully supported, so this is rare in real files.
• Excel's leap-year bug: serials 1..59 are mapped so dates display as Excel shows them; the fictitious 1900-02-29 (serial 60) becomes 1900-03-01.
• Memory: the whole file is buffered; peak memory is a multiple of file size.
• Pivot caches, comments, shapes and other non-cell content are ignored.

How it works

An .xls file is a CFB compound document: a FAT filesystem in miniature, with 512-byte sectors, a sector allocation table reached through the header's DIFAT, a directory, and a mini-stream (with its own miniFAT) for streams under 4096 bytes. The reader walks that container to extract the Workbook stream, then parses BIFF8 records: a globals substream (date mode, FORMAT and XF records for date detection, BOUNDSHEET offsets, the SST) followed by one substream per sheet at the offsets BOUNDSHEET gave. Cell records each carry their own row and column. RK values pack integers or truncated doubles into 30 bits with scale-by-100 and integer flags; "compressed" strings are the low bytes of UTF-16 (Latin-1, decoded manually since TextEncoding has no Latin-1 member).

Testing

Fixtures in test/ are small, synthetic, and generated by test/make_fixtures.py (xlwt) and test/make_edge_fixture.py (handcrafted records xlwt never writes, in a mini-stream container); test/expected.md describes what each one proves and the exact expected output. The parse logic has been cross-validated against xlrd on every fixture.