Dears,
One of the tasks needed by DE is to ingest data from files, for example, Excel file.
Thanks for OnerFusion-AI for the below thread that give us the steps of reading from one file
https://community.databricks.com/t5/get-started-discussions/how-to-import-excel-on-databricks/td-p/4...
in addition, I provide the below code in case of reading all the Excel files in a folder:
IMP Note:
- All files must have the same structure.
Steps:
1- You need to upload the Excel files under a DBFS folder.
2- Use the below code to read each file and combine them to a single CSV file
from pyspark.sql import SparkSession
# Create a SparkSession
spark = SparkSession.builder \
.appName("ReadExcelWithHeader") \
.config("spark.jars.packages", "com.crealytics:spark-excel_2.12:0.13.5") \
.getOrCreate()
# Define the directory containing Excel files
excel_dir_path = "/FileStore/tables"
# List all files in the directory using dbutils.fs.ls
all_files = dbutils.fs.ls(excel_dir_path)
# Filter to get only the .xlsx files
excel_files = [file.path for file in all_files if file.path.endswith(".xlsx")]
# Initialize an empty DataFrame
df_combined = None
# Loop through each Excel file and read it into a DataFrame
for excel_file in excel_files:
df = spark.read \
.format("com.crealytics.spark.excel") \
.option("header", "true") \
.option("inferSchema", "true") \
.load(excel_file)
# Combine the DataFrames
if df_combined is None:
df_combined = df
else:
df_combined = df_combined.union(df)
# Check if df_combined is not None before writing to CSV
if df_combined is not None:
# Define the output CSV file path
csv_file_path = "/FileStore/tables/output_file.csv"
# Save the combined DataFrame as a CSV file
df_combined.write.csv(csv_file_path, header=True, mode='overwrite')
print(f"Excel files in {excel_dir_path} have been successfully converted to {csv_file_path}")
else:
print(f"No Excel files found in {excel_dir_path}")
# Stop the Spark session
#spark.stop()
Thanks
