How to construct Dataframe from a Excel (xls,xlsx) file in Scala Spark?

by

The solution to your problem is to use Spark Excel dependency in your project.

Spark Excel has flexible options to play with.

I have tested the following code to read from excel and convert it to dataframe and it just works perfect

def readExcel(file: String): DataFrame = sqlContext.read
    .format("com.crealytics.spark.excel")
    .option("location", file)
    .option("useHeader", "true")
    .option("treatEmptyValuesAsNulls", "true")
    .option("inferSchema", "true")
    .option("addColorColumns", "False")
    .load()

val data = readExcel("path to your excel file")

data.show(false)

you can give sheetname as option if your excel sheet has multiple sheets

.option("sheetName", "Sheet2")

I hope its helpful

More Related Contents:

  1. How to select the first row of each group?
  2. Querying Spark SQL DataFrame with complex types
  3. How to convert rdd object to dataframe in spark
  4. How to define and use a User-Defined Aggregate Function in Spark SQL?
  5. Automatically and Elegantly flatten DataFrame in Spark SQL
  6. Spark extracting values from a Row
  7. Caused by: java.lang.NullPointerException at org.apache.spark.sql.Dataset
  8. Provide schema while reading csv file as a dataframe
  9. Spark Strutured Streaming automatically converts timestamp to local time
  10. Including null values in an Apache Spark Join
  11. Defining a UDF that accepts an Array of objects in a Spark DataFrame?
  12. Processing multiple files as independent RDD’s in parallel
  13. MatchError while accessing vector column in Spark 2.0
  14. How to save DataFrame directly to Hive?
  15. Spark / Scala: forward fill with last observation
  16. About how to add a new column to an existing DataFrame with random values in Scala
  17. Joining Spark dataframes on the key
  18. How to use Column.isin with list?
  19. How to write unit tests in Spark 2.0+?
  20. How to compare two dataframe and print columns that are different in scala
  21. What are the various join types in Spark?
  22. Renaming column names of a DataFrame in Spark Scala
  23. Spark Row to JSON
  24. Change output filename prefix for DataFrame.write()
  25. What is going wrong with `unionAll` of Spark `DataFrame`?
  26. Exploding nested Struct in Spark dataframe
  27. How to get keys and values from MapType column in SparkSQL DataFrame
  28. How to create DataFrame from Scala’s List of Iterables?
  29. How to create a Dataset of Maps?
  30. Difference between === null and isNull in Spark DataDrame

Leave a Comment