Azure Databricks使用Spark读取文件夹下Excel文件报PathNotFound错误如何解决

报错原因 #

你遇到的PathNotFound error message: openFileForRead must be used with files and not directories报错，是因为crealytics spark excel 库默认将传入的路径作为单个文件处理，不支持直接传入目录路径读取全部文件，和Spark原生支持目录读取的csv、parquet格式逻辑不同。

解决方案 #

方案1：通配符匹配（适合同级目录下所有Excel文件读取，无需递归子目录） #

直接在目录路径后拼接Excel文件后缀通配符即可，代码示例：

from pyspark.sql import functions as F

sSourcePath = "你的目录路径"
dfSource = spark \
  .read \
  .format("com.crealytics.spark.excel") \
  .option("Header", "true") \
  .option("inferSchema", "true") \
  .load(f"{sSourcePath}/*.xlsx") \ # 如需同时匹配xls格式，可修改为/*.xls*
  .withColumn("SourceFile",F.input_file_name())

方案2：遍历获取文件路径列表后批量读取（支持递归读取子目录内的Excel文件） #

如果需要读取多层子目录下的所有Excel文件，可先遍历过滤出所有符合要求的文件路径，再传入load方法读取，代码示例：

from pyspark.sql import functions as F

# 递归遍历获取所有Excel文件路径
def get_all_excel_paths(root_path, suffix_list = [".xlsx", ".xls"]):
    file_paths = []
    for file_info in dbutils.fs.ls(root_path):
        if file_info.isDir():
            file_paths.extend(get_all_excel_paths(file_info.path, suffix_list))
        else:
            if any(file_info.path.endswith(suffix) for suffix in suffix_list):
                file_paths.append(file_info.path)
    return file_paths

sSourcePath = "你的目录路径"
excel_file_list = get_all_excel_paths(sSourcePath)

# 读取所有文件
dfSource = spark \
  .read \
  .format("com.crealytics.spark.excel") \
  .option("Header", "true") \
  .option("inferSchema", "true") \
  .load(excel_file_list) \
  .withColumn("SourceFile",F.input_file_name())

注意事项 #

• 确保目录下所有Excel文件的表头、字段格式一致，否则schema推断可能出现字段类型不匹配的问题，复杂场景建议提前自定义schema传入，避免推断误差
• 确认你使用的crealytics spark excel 库版本和当前Databricks Runtime版本兼容，避免出现版本不匹配导致的读写异常

内容的提问来源于stack exchange，提问作者Akshay