cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Autoloader Excel Files

erigaud
Honored Contributor

Hello, I looked at the documentation but could not find what I wanted. Is there a way to load Excel files using an autoloader and if yes, what options should be given to specify format, sheet name etc ? 

Thank you friends !

1 ACCEPTED SOLUTION

Accepted Solutions

Vinay_M_R
Databricks Employee
Databricks Employee

Hi @erigaud 

You can use spark.read.format("com.crealytics.spark.excel") while reading excel files using autoloader and to specify format you need to provide com.crealytics.spark.excel and to specify sheet name you can provide it under options. 

Please find the below example code to read load Excel files using an autoloader:

spark.read.format("com.crealytics.spark.excel") \

.option("header", "true") \

.option("dataAddress", "'Data - Current'!A1") \

.option("treatEmptyValuesAsNulls", "true") \

.option("inferSchema", "true") \

.load(location)

And you can also use pandas to read excel files:

import pandas as pd

pd.read_excel(file ,sheet_name = 0, index_col = 0)

View solution in original post

7 REPLIES 7

Vinay_M_R
Databricks Employee
Databricks Employee

Hi @erigaud 

You can use spark.read.format("com.crealytics.spark.excel") while reading excel files using autoloader and to specify format you need to provide com.crealytics.spark.excel and to specify sheet name you can provide it under options. 

Please find the below example code to read load Excel files using an autoloader:

spark.read.format("com.crealytics.spark.excel") \

.option("header", "true") \

.option("dataAddress", "'Data - Current'!A1") \

.option("treatEmptyValuesAsNulls", "true") \

.option("inferSchema", "true") \

.load(location)

And you can also use pandas to read excel files:

import pandas as pd

pd.read_excel(file ,sheet_name = 0, index_col = 0)

Hemant
Valued Contributor II

Where autoloader is being used in the above snippet ๐Ÿค” ?

Hemant Soni

erigaud
Honored Contributor

Thank you very much, great solution !

Hemant
Valued Contributor II

Unfortunately, Databricks autoloader doesn't support Excel file types to incrementally load new files.

Link:https://docs.databricks.com/ingestion/auto-loader/options.html 

If your excel file contains a single sheet then there is a workaround.

Hemant Soni

erigaud
Honored Contributor

What is the workaround ? Is it to transform the excel to a csv file ? 

Hemant
Valued Contributor II

But using autoloader for the conversion into csv.

Hemant Soni

erigaud
Honored Contributor

How would that work exactly ? I specify to the autoloader that the format is csv and it will be able to pick up the excel files, and load the sheet normally, even if the format is .xlsx ?

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group