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: 

How do you read an Excel spreadsheet with Databricks

LPlates
New Contributor III

My cluster has Scala 2.12

I've installed Maven Library com.crealytics:spark-excel_2.12:0.14.0

I get an error

java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell

when trying to execute the following

%python

excelFileName="/mnt/dlstor/raw/sales/Budget vols FY 21-22 FY 22-23.xlsx"

excelWorksheetName="'22-23'!A2"

isHeaderOn="true"

isInferSchemaOn="true"

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

        .option("header", isHeaderOn) \

        .option("inferSchema", isInferSchemaOn) \

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

        .option("dataAddress", excelWorksheetName) \

        .load(excelFileName)

display(df)

I couldn't find a similar post. Any suggestions would be gratefully received.

Regards

1 ACCEPTED SOLUTION

Accepted Solutions

LPlates
New Contributor III

Okay, I've 'resolved' my issue

I changed the isHeaderOn="true" to isHeaderOn="false" and was able to load the dataframe.

View solution in original post

3 REPLIES 3

LPlates
New Contributor III

Okay, I've 'resolved' my issue

I changed the isHeaderOn="true" to isHeaderOn="false" and was able to load the dataframe.

Kaniz
Community Manager
Community Manager

Awesome!

Thank you for sharing the solution with us @Mike Knox​!

Anonymous
Not applicable

Another way also help for your case is usign Pandas to read excel then convert Pandas Dataframe to Pyspark Dataframe 🙂

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!