Data Transformation using Python/PySpark in Lakehouse

Hi datagig ,
Thank you for the update. Based on your description, the problem isn't with your loop or how you index sheets. Instead, it's that Fabric notebooks can't reliably open Excel files using standard file system paths. Files stored in Lakehouse / Files typically aren't accessible as local OS files within the notebook, so functions like pd.read_excel(path) or pd.ExcelFile(path) may not work as expected.

To access workbooks stored in a Lakehouse, the recommended approach is.

1. Use mssparkutils.fs.open() to read the file and load the bytes into a  BytesIO buffer.

2. Pass that buffer to pandas.ExcelFile(), then read each sheet by name rather than by index.

This method generally resolves the cannot read sheet errors.

Here’s an example you can try in your Fabric notebook. I can’t test this in your environment, but this follows the recommended pattern for accessing Excel files from Lakehouse Files.

from mssparkutils import fs
import pandas as pd
import io

def open_workbook(path):
    with fs.open(path, "rb") as f:
        raw = f.read()
    bio = io.BytesIO(raw)

    ext = path.lower()
    engine = "openpyxl" if ext.endswith(("xlsx","xlsm")) else "xlrd"

    return pd.ExcelFile(bio, engine=engine)

xls = open_workbook("/lakehouse/default/Files/X/Subfolder/sample.xlsx")
print("Sheets found:", xls.sheet_names)

Could you give this a try and let me know if you get any error messages. That will help determine if the problem is related to permissions, the workbook format, or the engine.

Thanks for your time.