The easiest general-purpose solution I found until now to process Excel files in Fabric using only notebooks (PySpark) is illustrated below.
Conditions:
- The Excel source file has been uploaded in a lakehouse not attached to the notebook.
- A specific range in an Excel spreadsheet must be loaded into a dataframe for processing.
- The processed dataframe must be saved in a delta table in another lakehouse not attached to the notebook.
Strategy:
Use the Pandas Python library to read the source Excel file content.
Challenges:
The Pandas library (as well as other binary I/O activities in Python, like copy an Excel File from Sharepoint to lakehouse, but this is another topic) can't manage absolute lakehouse "abfss" file paths, only locally mounted filesystem paths.
Solution:
- Mount the source lakehouse in the notebook local filesystem (use Notebookutils)
- Get the detailed locally mounted filesystem full path (use Notebookutils)
- Read the Excel file in a Pandas dataframe (use Pandas)
- Do whatever you have to do with that dataframe (use Pandas)
- Convert the Pandas dataframe in a Spark dataframe (use pyspark.sql.SparkSession)
- because, in my case, Pandas can't write a dataframe in a delta table using an absolute path
- Write the Spark dataframe to the destination lakehouse e.g. in a delta table (use pyspark.sql.SparkSession)
Details:
import notebookutils as nu # required to mount lakehouse filesystem for Pandas
import pandas as pd
from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate() # you can omit this row since the shell automatically creates the session in the variable spark for users.
# Source parameters
src_ws_id = 'any_workspace_id1'
src_lh_id = 'any_lakhouse_id1'
src_folder = 'any_subfolder' # (in the form 'subdir/.../subdir' or empty string '')
src_file = 'any_Excel_file_name'
# Destination parameters
dst_ws_id = 'any_workspace_id2'
dst_lh_id = 'any_lakhouse_id2'
dst_table = 'any_table_name'
def mount_lakehouse(workspace_id, lakehouse_id, mount_point='/mnt'):
"""
Mount a remote lakehouse filesystem to the local filesystem of the notebook.
:param workspace_id: ID of the lakehouse workspace.
:param lakehouse_id: ID of the lakehouse.
:param mount_point: Mount point in the local filesystem (optional, default is '/mnt').
:return: The mount point in the local filesystem.
"""
try:
# Construct the URL of the remote filesystem
remote_url = f'abfss://{workspace_id}@onelake.dfs.fabric.microsoft.com/{lakehouse_id}'
# Mount the remote filesystem to the local filesystem
nu.fs.mount(remote_url, mount_point)
except Exception as e:
# Handle exceptions and exit the notebook in case of an error
exit_value = f'Error type: {type(e).__name__}\nError description: {e}'
nu.notebook.exit(exit_value)
else:
print('Lakehouse filesystem successfully mounted!')
return mount_point
def unmount_lakehouse(mount_point='/mnt'):
"""
Unmount a lakehouse filesystem from the local filesystem.
:param mount_point: Mount point in the local filesystem (optional, default is '/mnt').
:return: Result of the unmount operation.
"""
try:
# Unmount the filesystem from the local mount point
result = nu.fs.unmount(mount_point)
except Exception as e:
# Handle exceptions and exit the notebook in case of an error
exit_value = f'Error type: {type(e).__name__}\nError description: {e}'
nu.notebook.exit(exit_value)
else:
print('Lakehouse filesystem successfully unmounted!')
return result
# Mount source lakehouse (because Pandas can't read abfss absolute paths)
src_mount_point = mount_lakehouse(src_ws_id, src_lh_id, src_mount_point)
# Get full local fs path for source lakehouse
src_path = f'{nu.fs.getMountPath(src_mount_point)}/Files/{src_folder}/{src_file}'
# Read an Excel file into a Pandas dataframe
# in this case I read the first 4 columns in the second sheet skipping the first 4 rows and using the first row (row n#5 because 4 are skipped) as header
df = pd.read_excel(f'{src_path}', sheet_name = 2, skiprows = 4, usecols = 'A:D')
# at this point do what you have to do with this dataframe
# and then...
# Convert pandas df in spark df
spark_df = spark.createDataFrame(df)
# Construct the URL of the remote filesystem
delta_table_abfss_path = f'abfss://{dst_ws_id}@onelake.dfs.fabric.microsoft.com/{dst_lh_id}/Tables/{dst_table}'
# Write the Spark dataframe in a delta table
spark_df.write.format("delta").mode("overwrite").option("mergeSchema", "true").save(delta_table_abfss_path)
# Exit notebook reporting success
exit_value = f'The notebook succeeded!'
nu.notebook.exit(exit_value)
The above solution is a general-purpose solution working in any situation, please make only sure you have permission to access the source and destination lakehouses for write operations.
I hope thi may help
