Forum Discussion
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.
OK, try this, again AI generated but I think you'll appreciate, your request is hard to replicate and work through. A tip for working with AI generated content, split the code down into different cells so that you can identify what works and what doesn't. Use display(df) often to see where you are at any point:
import os
import re
import pandas as pd
# =========================
# Config – adjust as needed
# =========================
ROOT_DIR = "/lakehouse/default/Files/X" # change X to your main folder name under Files
SKIP_FIRST_N_SUBFOLDERS = 7 # skip first 7 subfolders alphabetically
DETAIL_COLUMNS = [
# e.g., "Customer", "Product", "Amount"
]
SUMMARY_TABLE_NAME = "maturity_summary"
DETAIL_TABLE_NAME = "maturity_detail"
# =========================
# Small helpers
# =========================
def main_folder_tag(folder_name: str) -> str:
"""Take part before first '-' else full name."""
return folder_name.split("-", 1)[0].strip()
def extract_quarter_tag(filename: str) -> str | None:
"""Find 'FYxx Hx' with flexible spacing/underscores/dashes, case-insensitive."""
norm = filename.replace("_", " ").replace("-", " ")
m = re.search(r"(FY)\s*(\d{2})\s*H\s*([12])", norm, re.IGNORECASE)
return f"FY{m.group(2)} H{m.group(3)}" if m else None
def excel_engine_for(path: str) -> str:
"""
Choose engine by extension:
- .xlsx / .xlsm -> openpyxl
- .xls -> xlrd
"""
lp = path.lower()
if lp.endswith(".xlsx") or lp.endswith(".xlsm"):
return "openpyxl"
elif lp.endswith(".xls"):
return "xlrd"
else:
raise ValueError(f"Unsupported Excel extension: {path}")
def select_columns_ci(df: pd.DataFrame, wanted: list[str]) -> pd.DataFrame:
if not wanted:
raise ValueError("DETAIL_COLUMNS is empty. Populate with your 3 column names.")
# case-insensitive map
lower_map = {c.lower(): c for c in df.columns}
actual = []
missing = []
for w in wanted:
k = w.lower()
if k in lower_map:
actual.append(lower_map[k])
else:
missing.append(w)
if missing:
raise KeyError(f"Detail columns not found (case-insensitive): {missing}\nAvailable: {list(df.columns)}")
return df[actual]
# =========================
# Scan folders & process
# =========================
summary_parts = []
detail_parts = []
# deterministic subfolder order (alphabetical)
subfolders = sorted([f for f in os.listdir(ROOT_DIR) if os.path.isdir(os.path.join(ROOT_DIR, f))])
to_process = subfolders[SKIP_FIRST_N_SUBFOLDERS:]
print(f"[INFO] Subfolders found: {len(subfolders)}; skipping first {SKIP_FIRST_N_SUBFOLDERS}; processing {len(to_process)}.")
for sf in to_process:
sf_path = os.path.join(ROOT_DIR, sf)
tag = main_folder_tag(sf)
print(f"\n[Folder] {sf} -> tag: '{tag}'")
# Find Excel files that contain 'Maturity' in filename (case-insensitive)
excel_files = []
for root, _, files in os.walk(sf_path):
for fn in files:
if fn.lower().endswith((".xlsx", ".xlsm", ".xls")) and "maturity" in fn.lower():
excel_files.append(os.path.join(root, fn))
if not excel_files:
print(" [INFO] No matching Excel files.")
continue
print(f" [INFO] {len(excel_files)} matching file(s).")
for fpath in excel_files:
fname = os.path.basename(fpath)
quarter = extract_quarter_tag(fname)
engine = excel_engine_for(fpath)
print(f" [File] {fname} | quarter: {quarter} | engine: {engine}")
# Use ExcelFile to list sheets safely before reading
try:
xls = pd.ExcelFile(fpath, engine=engine)
sheet_names = xls.sheet_names # ordered
sheet_count = len(sheet_names)
except Exception as e:
print(f" [WARN] Cannot open workbook: {e}")
continue
# ---- Summary: 3rd sheet (index 2) if exists
if sheet_count >= 3:
sheet3_name = sheet_names[2]
try:
df_sum = pd.read_excel(xls, sheet_name=sheet3_name)
if not df_sum.empty:
df_sum["main_folder"] = tag
df_sum["quarter"] = quarter
df_sum["source_file"] = fname
summary_parts.append(df_sum)
else:
print(" [INFO] Summary sheet (3rd) is empty.")
except Exception as e:
print(f" [WARN] Failed reading Summary (sheet 3='{sheet3_name}'): {e}")
else:
print(" [INFO] Workbook has fewer than 3 sheets; Summary skipped.")
# ---- Detail: sheets 4..20 (indexes 3..19) if exist
if sheet_count >= 4:
start = 3
end = min(19, sheet_count - 1) # cap to last available
for idx in range(start, end + 1):
sname = sheet_names[idx]
try:
df_det = pd.read_excel(xls, sheet_name=sname)
if df_det.empty:
continue
# select only needed columns (case-insensitive)
try:
df_sel = select_columns_ci(df_det, DETAIL_COLUMNS)
except Exception as col_err:
# If columns fail for just one sheet, skip that sheet but continue others
print(f" [WARN] Sheet {idx+1} ('{sname}') column selection error: {col_err}")
continue
df_sel["main_folder"] = tag
df_sel["quarter"] = quarter
df_sel["source_file"] = fname
detail_parts.append(df_sel)
except Exception as e:
print(f" [WARN] Failed reading Detail sheet {idx+1} ('{sname}'): {e}")
else:
print(" [INFO] Workbook has fewer than 4 sheets; no Detail sheets to process.")
# =========================
# Combine & Save to Delta
# =========================
summary_df = pd.concat(summary_parts, ignore_index=True) if summary_parts else pd.DataFrame()
detail_df = pd.concat(detail_parts, ignore_index=True) if detail_parts else pd.DataFrame()
print("\n[RESULT] Combined shapes:")
print(f" Summary: {summary_df.shape}")
print(f" Detail : {detail_df.shape}")
if not summary_df.empty:
spark.createDataFrame(summary_df).write.format("delta").mode("overwrite").saveAsTable(SUMMARY_TABLE_NAME)
print(f"[SAVE] Lakehouse table written: {SUMMARY_TABLE_NAME}")
else:
print("[SAVE] Summary empty; no table written.")
if not detail_df.empty:
spark.createDataFrame(detail_df).write.format("delta").mode("overwrite").saveAsTable(DETAIL_TABLE_NAME)
print(f"[SAVE] Lakehouse table written: {DETAIL_TABLE_NAME}")
else:
print("[SAVE] Detail empty; no table written.")
Why this should fix your errors
- No blind indexing: We enumerate sheet_names first, so we only read sheets that exist.
- Right engine per format:
- .xlsx / .xlsm ⇒ openpyxl
- .xls ⇒ xlrd
- Hidden/merged sheets: Using ExcelFile often handles these better than direct read_excel by index.
- Partial workbooks: If a workbook has < 3 or <4 sheets, we skip cleanly with a log message.
- Granular logging: You’ll see exactly which sheet name/index failed, per file.
--------------------------------
I hope this helps, please give kudos and mark as solved if it does!
Connect with me on LinkedIn.
Subscribe to my YouTube channel for Fabric/Power Platform related content!
I really appreciate your effort. Thank you! Yes, trying to get through it.
Hi wardy912, I think the script is not able to read or open workbooks and sheets with some Python or Spark methods I tried using. Do you know a function that will work in this case when I am trying to open a workbook from a notebook and the workbooks are present in lakehouse/files? I tried using a couple of them, but nothing works.
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.