How to read an Excel file from Azure Databricks using PySpark

The Excel file cannot be read directly using Py-Spark in Databricks so necessary Libraries (com.crealytics.spark.excel) have to be installed in the Cluster to successfully run the Python code.

Step 1
Navigate to the Cluster that will be used to run the Python script under Compute in Databricks.

Step 2
Click on the tab Libraries -> Install new.

Step 3
Select Maven as a Library source and click on Search Packages.

Step 4
Type com.crealytics in the search bar and select Maven Central.

Step 5
Select the com.crealytics.spark.excel package version that matches with the version of Scala (Cluster -> Configuration -> Databricks Runtime Version) in your Cluster.

Step 6
Click Install

Use the following code to load the Excel file:-

// Specify the path to the Excel file
val excelFilePath = "/mnt/<your-mount-name>/path_to_your_excel_file.xlsx";  // Replace with your actual spark file path

// Read the Excel file into a Spark DataFrame
val df = spark.read
  .format("com.crealytics.spark.excel")
  .option("location", excelFilePath)
  .option("useHeader", "true")  // Use this option if your Excel file has headers
  .load();

Whereas using Pandas Library the following Python code could be used:-

import pandas 
excelFilePath = "/mnt/<your-mount-name>/path_to_your_excel_file.xlsx"; # Replace with your actual file path

ef=pandas.ExcelFile(excelFilePath); # Load the Excel file as an object

# Mention the Sheet_Name or use ef.sheet_names to iterate through each sheet data

df=ef.parse(Sheet_Name); # Load the required Excel sheet data

Done