PathNotFound error message: openFileForRead must be used with files and not directories

user_12141741 0
Viewed 152

I am using below code to read excel file using Azure databricks:

dfSource = spark \
  .read \
  .format("com.crealytics.spark.excel") \
  .option("Header", "true") \
  .option("inferSchema", "true") \
  .load(sSourcePath)#.withColumn("SourceFile",F.input_file_name())

But I get the error below:

PathNotFound error message: openFileForRead must be used with files and not directories

When I give the full filename, Spark properly read the file.

Note: I want to read all the files present in the folder.

pyspark azure-databricks databricks apache-spark python
1 Answers

If you need to read multiple Excel files from a directory, then you just need to iterate over the files, read each file into a dataframe, and union all these dataframes. On Databricks you can use dbutils.fs.ls function to list files in the given directory, something like this:

all_data = None
sSourcePath = "path_to_directory"
for f in dbutils.fs.ls(sSourcePath):
  if not f.isFile or not f.name.endswith(".xlsx"):
    continue
  df = spark \
    .read \
    .format("com.crealytics.spark.excel") \
    .option("Header", "true") \
    .option("inferSchema", "true") \
    .load(f.path)
  if all_data:
    all_data = all_data.union(df)
  else:
    all_data = df

There is a chance that dataframes may not be compatible by structure, in this case you may need to do some explicit casting, etc.

Alex_Ott_186270
Related