Reading an Excel file might sound like a basic task, but getting that data from a .xlsx file into a Spark DataFrame in Databricks has a few quirks. Whether you need a quick, simple solution for a small file or a scalable method for massive datasets, this guide has you covered. We'll walk through the most practical ways to read Excel files in Databricks, explaining the pros and cons of each approach.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Does Reading Excel in Databricks Require a Specific Method?

Unlike simple text-based files like CSV or JSON, which Apache Spark can read natively, Excel files (.xlsx or .xls) are more complex. They are essentially zipped archives of XML files containing data, formatting, formulas, and even multiple sheets. Spark’s core engine doesn’t have a built-in parser for this structure, so we need to rely on external libraries to do the heavy lifting.

You can't just point Spark's standard reader at an Excel file and expect it to work. Attempting to do so will result in errors because Spark won't know how to interpret the binary format. This is why we need one of the dedicated methods below, which either use a library to add this functionality to Spark or use a workaround to convert the data into a format that Spark does understand.

Prerequisites

Before we start, make sure you have the following:

• An active Databricks workspace.
• An Excel file uploaded to a location accessible by your cluster, like the Databricks File System (DBFS). You can upload a file by navigating to Data -> DBFS and using the Upload button.
• A running Databricks cluster.
• Basic familiarity with PySpark and Databricks notebooks.

Method 1: The Quick & Easy Way with pandas

For small to medium-sized Excel files (think a few hundred thousand rows at most), using the pandas library is often the most straightforward approach. Most Databricks runtimes come with pandas pre-installed. The strategy here is simple: read the Excel file on the driver node using pandas, and then convert the resulting pandas DataFrame into a Spark DataFrame for distributed processing.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step Instructions

First, you need to make sure you have the necessary libraries to read Excel files with pandas. openpyxl is a popular choice for .xlsx files.

1. Install the Required Library

You can easily install this within your notebook using a %pip magic command. Run this in a notebook cell:

%pip install openpyxl

2. Get the Local File Path

When you use a non-Spark library like pandas, it runs on the driver node, which interacts with the local file system. DBFS files are mounted to the driver node, so you can access them by simply replacing the dbfs:/ URI prefix with /dbfs/.

For example, if your file is located at dbfs:/FileStore/tables/my_data.xlsx, the local path for pandas would be /dbfs/FileStore/tables/my_data.xlsx.

3. Read the Excel File into a pandas DataFrame

Now, use the pd.read_excel() function to load the data. You can specify which sheet to read by name or by its index (starting from 0 for the first sheet).

4. Convert to a Spark DataFrame

Once the data is in a pandas DataFrame, you can convert it to a Spark DataFrame using spark.createDataFrame(). This action distributes the data from the driver node across your cluster's worker nodes, allowing you to use Spark's full power.

Putting It All Together: Complete Code Snippet

import pandas as pd

1. Define the file path for your uploaded Excel file

Note the path starts with /dbfs/, not dbfs:/

excel_file_path = "/dbfs/FileStore/tables/sales_data.xlsx"

2. Read the Excel file into a pandas DataFrame

You can specify the sheet name or index

For example, to read the first sheet: sheet_name=0

To read a sheet named "Q1-Data": sheet_name="Q1-Data"

try: pandas_df = pd.read_excel(excel_file_path, sheet_name="Sheet1")

# 3. Convert the pandas DataFrame to a Spark DataFrame
spark_df = spark.createDataFrame(pandas_df)

# 4. Show the Spark DataFrame to verify
display(spark_df)

# You can also print the schema
spark_df.printSchema()

except FileNotFoundError: print(f"Error: The file was not found at {excel_file_path}") except Exception as e: print(f"An error occurred: {e}")

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Pros and Cons of Using pandas

• Pros:
• Cons:

Method 2: The Scalable Way with spark-excel

When you're working with large Excel files or building a production-grade data pipeline, you need a solution that leverages Spark's distributed nature from the start. The best tool for this is the open-source spark-excel library.

This library provides a Spark data source for Excel files, allowing Spark to read the data in a distributed and parallel fashion without loading everything onto the driver node. This is the recommended approach for any serious data processing task.

Step-by-Step Instructions

1. Install the spark-excel Library on Your Cluster

Before you can use it, you need to attach the library to your cluster.

1. Navigate to your cluster’s settings page.
2. Click on the Libraries tab.
3. Click Install New.
4. Select Maven as the Library Source.
5. In the Coordinates box, enter the library’s Maven coordinates. A common version is com.crealytics:spark-excel_2.12:3.5.0_0.20.3. Be sure to check the official repository for the latest version compatible with your Spark and Scala versions.
6. Click Install. The cluster will restart, and the library will be available.

2. Read the Excel File Directly into a Spark DataFrame

With the library installed, you can now use spark.read.format() to read the Excel file. The path you provide should use the dbfs:/ format that Spark APIs expect.

Customizing the Read Operation

The spark-excel library offers many helpful options for precise data loading:

• .option("header", "true"): Tells Spark to use the first row of the Excel sheet as the column headers.
• .option("inferSchema", "true"): Asks Spark to automatically guess the data type for each column (e.g., String, Integer, Timestamp). This is convenient but can be slow and sometimes inaccurate for very large files. For production jobs, defining the schema manually is often better.
• .option("dataAddress", "'YourSheetName'!A1"): This is an important one. It lets you specify which sheet to read and even the cell range. For example, " 'Sheet1'!A1" reads all data starting from cell A1 in "Sheet1".

Complete Code Snippet

The file path here should use dbfs:/

excel_file_path = "dbfs:/FileStore/tables/sales_data.xlsx"

try: # Read the Excel file using the spark-excel data source spark_df = (spark.read .format("com.crealytics.spark.excel") .option("header", "true") # Use the first row as header .option("inferSchema", "true") # Infer column types .option("dataAddress", "'Sheet1'") # Specify the sheet name .load(excel_file_path) )

# Show the results
display(spark_df)

# Print the schema inferred by Spark
spark_df.printSchema()

except Exception as e: print(f"An error occurred: {e}. Check if the spark-excel library is attached to your cluster.")

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Pros and Cons of Using spark-excel

• Pros:
• Cons:

Bonus Method: The Quick Fallback (Convert to CSV)

Sometimes you just need to get the job done without messing with new libraries. If your Excel file is simple (one sheet, no complex formatting), a perfectly valid workaround is to convert it to a CSV file first.

Here’s the process:

1. Open the .xlsx file in Microsoft Excel, Google Sheets, or another spreadsheet program.
2. Select "Save As" or "Download" and choose the CSV (Comma Separated Values) format.
3. Upload this new .csv file to DBFS.
4. Read it using Spark’s highly optimized, built-in CSV reader.

Code to Read the CSV

Path to your newly created CSV file

csv_file_path = "dbfs:/FileStore/tables/sales_data.csv"

Use Spark's native CSV reader

spark_df_from_csv = (spark.read .format("csv") .option("header", "true") .option("inferSchema", "true") .load(csv_file_path) )

display(spark_df_from_csv)

This method circumvent the problem entirely by changing the file into a format Spark loves. It's best reserved for quick one-off analyses, as it adds a manual step to your workflow and doesn't work for Excel files with multiple sheets.

Final Thoughts

Getting your data out of Excel and into Databricks is a common first step in any data analysis project. While there are a few ways to approach it, choosing the right method depends on your specific needs. Use pandas for quick tests on smaller files, but default to the scalable and robust spark-excel library for larger datasets and production-level workflows.

Tasks like reading and wrangling files are often just the barriers between you and the insights hiding in your data. At Graphed, we automate the initial data connection and reporting drudgery. Instead of spending time in notebooks writing boilerplate code, we let you connect directly to your business applications (like Google Analytics, Salesforce, or Shopify) and use natural language to ask questions, create dashboards, and find answers in seconds. This lets you focus on making decisions, not wrangling data formats.