How do you read an Excel spreadsheet with Databricks

[LPlates]

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

[LPlates · Accepted solution]

Okay, I've 'resolved' my issue

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

[Kaniz]

Awesome!

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

[Anonymous]

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