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:-

<span>//</span> <span>Specify</span> <span>the</span> <span>path</span> <span>to</span> <span>the</span> <span>Excel</span> <span>file</span>
<span>val</span> <span>excelFilePath</span> <span>=</span> <span>"</span><span>/mnt/<your-mount-name>/path_to_your_excel_file.xlsx</span><span>"</span><span>;</span>  <span>//</span> <span>Replace</span> <span>with</span> <span>your</span> <span>actual</span> <span>spark</span> <span>file</span> <span>path</span>
<span>//</span> <span>Read</span> <span>the</span> <span>Excel</span> <span>file</span> <span>into</span> <span>a</span> <span>Spark</span> <span>DataFrame</span>
<span>val</span> <span>df</span> <span>=</span> <span>spark</span><span>.</span><span>read</span>
  <span>.</span><span>format</span><span>(</span><span>"</span><span>com.crealytics.spark.excel</span><span>"</span><span>)</span>
  <span>.</span><span>option</span><span>(</span><span>"</span><span>location</span><span>"</span><span>,</span> <span>excelFilePath</span><span>)</span>
  <span>.</span><span>option</span><span>(</span><span>"</span><span>useHeader</span><span>"</span><span>,</span> <span>"</span><span>true</span><span>"</span><span>)</span>  <span>//</span> <span>Use</span> <span>this</span> <span>option</span> <span>if</span> <span>your</span> <span>Excel</span> <span>file</span> <span>has</span> <span>headers</span>
  <span>.</span><span>load</span><span>();</span>
<span>//</span> <span>Specify</span> <span>the</span> <span>path</span> <span>to</span> <span>the</span> <span>Excel</span> <span>file</span>
<span>val</span> <span>excelFilePath</span> <span>=</span> <span>"</span><span>/mnt/<your-mount-name>/path_to_your_excel_file.xlsx</span><span>"</span><span>;</span>  <span>//</span> <span>Replace</span> <span>with</span> <span>your</span> <span>actual</span> <span>spark</span> <span>file</span> <span>path</span>

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

Enter fullscreen mode Exit fullscreen mode

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

<span>import</span> <span>pandas</span> 
<span>excelFilePath</span> <span>=</span> <span>"</span><span>/mnt/<your-mount-name>/path_to_your_excel_file.xlsx</span><span>"</span><span>;</span> <span># Replace with your actual file path </span>
<span>ef</span><span>=</span><span>pandas</span><span>.</span><span>ExcelFile</span><span>(</span><span>excelFilePath</span><span>);</span> <span># Load the Excel file as an object </span>
<span># Mention the Sheet_Name or use ef.sheet_names to iterate through each sheet data </span>
<span>df</span><span>=</span><span>ef</span><span>.</span><span>parse</span><span>(</span><span>Sheet_Name</span><span>);</span> <span># Load the required Excel sheet data </span>
<span>import</span> <span>pandas</span> 
<span>excelFilePath</span> <span>=</span> <span>"</span><span>/mnt/<your-mount-name>/path_to_your_excel_file.xlsx</span><span>"</span><span>;</span> <span># Replace with your actual file path </span>
<span>ef</span><span>=</span><span>pandas</span><span>.</span><span>ExcelFile</span><span>(</span><span>excelFilePath</span><span>);</span> <span># Load the Excel file as an object </span>
<span># Mention the Sheet_Name or use ef.sheet_names to iterate through each sheet data </span>
<span>df</span><span>=</span><span>ef</span><span>.</span><span>parse</span><span>(</span><span>Sheet_Name</span><span>);</span> <span># Load the required Excel sheet data </span>
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 

Enter fullscreen mode Exit fullscreen mode

Done

原文链接：How to read an Excel file from Azure Databricks using PySpark