How to Upload Excel File to Databricks

Reading excel file in pyspark (Databricks notebook)

This blog we will learn how to read excel file in pyspark (Databricks = DB , Azure = Az).

Most of the people have read CSV file every bit source in Spark implementation and even spark provide direct support to read CSV file but as I was required to read excel file since my source provider was stringent with not providing the CSV I had the task to notice a solution how to read information from excel file and to increase my difficulty I had to read from different sheets of the same excel file.

After searching on google I couldn't detect any direct answers merely then thought of writing this web log so that people who want to read the Excel file in spark (Python) can read this weblog and exercise information technology chop-chop.

And then I will be explaining step wise on how to read excel file in pyspark (DB Az).

Library required for reading excel file is "crealytics/spark-excel" this library saved me lots of fourth dimension to read excel and made my life happier kudos to the developers and contributors. This is an active community which is managing this plugin.

You might discover it foreign only the GIT page shows sample of lawmaking in Scala and all the documentation is for Scala and non a single line of lawmaking for pyspark, just I tried my luck and information technology worked for me in pyspark.

This library requires Spark ii.0+

You can link confronting this library in your program at the following coordinates:

Scala ii.12

          groupId: com.crealytics
artifactId: spark-excel_2.12
version: 0.13.1        

Scala 2.11

          groupId: com.crealytics
artifactId: spark-excel_2.11
version: 0.13.1        

Install library in DB cluster in Az.

1. Go to clusters in DB workspace and click on the cluster yous desire to install the library on, once you click on the cluster name you will land on cluster details folio, switch to Libraries tab and click on "Install New".

Cluster Libraries tab

1. 1 Afterward clicking install library, yous will get pop up window were you need to click on Maven and requite the following co-ordinates.

com.crealytics:spark-excel_2.12:0.13.five

Or if yous want you tin can click on Search Packages and popular up window will open named "Search Packages". From dropdown select "Maven Central" and type "com.crealytics" in the text search box and select latest version of the plugin or as per your scala version in Cluster on DB Az. I am going with "spark-excel_2.12" every bit per my scala version on cluster. Every bit you click on select it volition populate the co-ordinates as testify in the above screenshot and and so click install.

crealytics maven selection

In one case your library is install you it will be shown as beneath.

We are all ready to start writing our code to read information from excel file.

ii. Code in DB notebook for reading excel file.

Sample Code

#sample data file path
sampleDataFilePath = "dbfs:/FileStore/tables/sample_xls_file_for_testing-1.xls"

#flags required for reading the excel
isHeaderOn = "true"
isInferSchemaOn = "false"

#canvas accost in excel
sample1Address = "'sample1'!A1"
sample2Address = "'sample2'!A1"

#read excelfile
sample1DF = spark.read.format("com.crealytics.spark.excel") \
.option("header", isHeaderOn) \
.pick("inferSchema", isInferSchemaOn) \
.pick("treatEmptyValuesAsNulls", "false") \
.option("dataAddress", sample1Address) \
.load(sampleDataFilePath)
brandish(sample1DF)

This above code will read all the information from sheet "sample1" from the sample xls file starting from cell A1 to all the columns.

Y'all can also specify range of cells in the code, incase you want to read only specific cells.

Sample code to read for specific cell range.

#sample information file path
sampleDataFilePath = "dbfs:/FileStore/tables/sample_xls_file_for_testing-ane.xls"

#flags required for reading the excel
isHeaderOn = "truthful"
isInferSchemaOn = "false"

#sheet address in excel
sample1Address = "'sample1'!A1:P35"
sample2Address = "'sample2'!A1:P35"

#read excelfile
sample1DF = spark.read.format("com.crealytics.spark.excel") \
.choice("header", isHeaderOn) \
.option("inferSchema", isInferSchemaOn) \
.pick("treatEmptyValuesAsNulls", "false") \
.choice("dataAddress", sample1Address) \
.load(sampleDataFilePath)
display(sample1DF)

Data Addresses

Every bit you lot tin can see in the examples above, the location of information to read or write can be specified with the dataAddress option. Currently the post-obit address styles are supported:

• B3: Offset cell of the information. Reading will return all rows below and all columns to the right. Writing volition showtime here and utilise as many columns and rows as required.
• B3:F35: Cell range of information. Reading will return only rows and columns in the specified range. Writing volition kickoff in the start jail cell (B3 in this example) and apply simply the specified columns and rows. If in that location are more than rows or columns in the DataFrame to write, they volition be truncated. Make certain this is what you lot want.
• 'My Canvas'!B3:F35: Same as above, merely with a specific sheet.

If the sail name is unavailable, it is possible to pass in an index:

          #sheet accost in excel
sample1Address = "'0'!A1"          #read excelfile
sample1DF = spark.read.format("com.crealytics.spark.excel") \
            .pick("header", isHeaderOn) \
            .option("inferSchema", isInferSchemaOn) \
            .choice("treatEmptyValuesAsNulls", "simulated") \
            .selection("dataAddress", sample1Address) \
            .load(sampleDataFilePath)
display(sample1DF)        

brownwarvervaind.blogspot.com

Source: https://mayur-saparia7.medium.com/reading-excel-file-in-pyspark-databricks-notebook-c75a63181548

Share this post