90. Databricks | Pyspark | Interview Question: Read Excel File with Multiple Sheets

Azure Databricks Learning: Interview Question: Read Excel File with Multiple Sheets
================================================================================
How to create dataframe reading multiple excel sheets ?
Though creating dataframe by reading excel sheets is not very common, still there are certain scenarios where we need to read excel data. Reading data from all excel sheets is bit challenging as there is no direct solution. I have created an automated solution in this video for that requirement
To get through understanding of this concept, please watch this video
#DatabricksExcel, #SparkExcelReading, #PysparkReadingMultipleExcelSheets,#PysparkTips, #DatabricksRealtime, #SparkRealTime, #DatabricksInterviewQuestion, #DatabricksInterview, #SparkInterviewQuestion, #SparkInterview, #PysparkInterviewQuestion, #PysparkInterview, #BigdataInterviewQuestion, #BigdataInterviewQuestion, #BigDataInterview, #PysparkPerformanceTuning, #PysparkPerformanceOptimization, #PysparkPerformance, #PysparkOptimization, #PysparkTuning, #DatabricksTutorial, #AzureDatabricks, #Databricks, #Pyspark, #Spark, #AzureDatabricks, #AzureADF, #Databricks, #LearnPyspark, #LearnDataBRicks, #DataBricksTutorial, #azuredatabricks, #notebook, #Databricksforbeginners

[@oiwelder]

I had a similar case, but it was several positional cells. With your function I managed to reduce 12 dataframes to just 1. Thank you for sharing this precious knowledge.

[@rajasdataengineering7585]

That's amazing. Thank you for sharing your experience

[@oiwelder]

@@rajasdataengineering7585 however I found another challenge, the loop will repeat 12 times, and each framework represents 1 month. I needed to create an incremental to count from 1 to 12. I used a function monotonically_increasing_id() but it doesn't generate the correct sequence, it starts at 1 and the variation ends at 2000. Would there be a solution for this case?

[@rajasdataengineering7585]

Please share the requirement in detail to [e-mail]

[@KG-rw9sr]

please post the code for UDF to create DF from Multiple sheets and Perform Union in the description like in other previous videos! thanks again for amazing tutorials. You are amazing!

[@rajasdataengineering7585]

def createExcelDataFrame(path, sheets): firstSheet =sheets[0] df = spark.read.format("com.crealytics.spark.excel").option("inferschema",True).option("header",True).option("dataAddress", f"{firstSheet}!").load(path) schema =df.schema for sheet in sheets[1:]: sheetDF =spark.read.format("com.crealytics.spark.excel").schema(schema).option("header",True).option("dataAddress", f"{sheet}!").load(path) df = df.union(sheetDF) return df

[@venkatasai4293]

Good scenario based question …please bring more like this Raja ...

[@rajasdataengineering7585]

Thanks Venkat, sure will post more real-time based videos

[@ashokkumar-wm6yr]

This question was asked in Winwire azure dataengineer L1 discussion.

[@rajasdataengineering7585]

Thank you for sharing your interview experience. It certainly helps everyone in this community

[@stoyyeti3671]

Waiting for your videos on delta live tables ,workflows, jobs , pipelines, spark structured streaming in databricks

[@rajasdataengineering7585]

Have already created videos on all topics that you mentioned except delta live table Will create play list for DLT in near future

[@datoalavista581]

Thank you for sharing !

[@rajasdataengineering7585]

My pleasure!

[@PradyutJoshi]

Great example but your microphone is having interference. Please resolve that. Thanks!

[@sravankumar1767]

Nice explanation Raja 👌 👍 👏

[@rajasdataengineering7585]

Thanks Sravan!

[@rushikeshsuryawanshi9679]

Please create a video for xml read and streaming also

[@rajasdataengineering7585]

Sure will do

[@user-ng3oc9pv5v]

How to do when sheets names are space separated. I am getting error while reading Excel file having space separated sheet name

[@Learn2Share786]

How to install the excel Library at notebook level instead of cluster level. Could you pls share the steps?

[@suryateja5323]

How to do when Sheet names have spaces like Sheet Name , by giving dataAddress="Sheet Name!" Throwing an error

[@sravankumar1767]

Hi Raja I have one doubt in my current project. They have given complex logic mapping document. They given multiple joins in that document. Could you please help me . They said need to finish in Monday. Please help me. How shd I contact you

[@rajasdataengineering7585]

Sure Sravan, I will contact t

[@sravankumar1767]

@@rajasdataengineering7585 Thank you very much

[@sravankumar1767]

@@rajasdataengineering7585 can you please share your email, I will forward the mapping document..

[@ab0515dat]

But how to list the Excel sheets using spark ...not using python Because of u are reading from adls.. u will need spark only not python

[@rajasdataengineering7585]

Why shouldn't we use python? Any specific reason? Pyspark means combination of python+ spark

[@chakhil8000]

Sir,I am getting this error please help i installed library as you mentioned, after executing the code to read excel file ,getting this error :java.lang.NoClassDefFoundError: Could not initialize class com.crealytics.spark.excel.WorkbookReader$

[@dvsrikanth22]

sir i am getting error as "java.lang.NoSuchMethodError: scala.collection.immutable.Seq.map(Lscala/Function1;)Ljava/lang/Object;" Please tell me how to resolve this issue

[@shubhikatiwari9259]

i am getting same error "java.lang.NoClassDefFoundError: org/apache/poi/ss/usermodel/WorkbookProvider"