Read excel file in a directory using pyspark

Question

`Hi , I am trying to read excel file in a directory using pyspark but i am getting fielnotfound error

`env_path='dbfs:/mnt'

raw='dev/raw/work1'

path=env_path+raw

file_path=path+'/'

objects = dbutils.fs.ls(file_path)

for file_name in objects:

`if file_name.isFile():

   sample_df=spark.read.format("com.crealytics.spark.excel").option("header", "false").load(objects+file_name) `

I am trying this code to read my excel file but getting file not found error .can someone help me with this?`

Answer 1

I think in load you are not passing the proper path. So you are getting the error file not found error.

Try this you will get the output.

objects = dbutils.fs.ls(path)
for file_name in objects:
  if file_name.isFile():
    if file_name[0].endswith("xlsx"): #optional
      sample_df=spark.read.format("com.crealytics.spark.excel").option("header", "true").load(file_name[0])