Re: pyspark.pandas.read_excel(engine = xlrd) readi...

youssefmrini · ‎08-08-2023

It sounds like you're trying to open an Excel file that has some invalid references, which is causing an error when you try to read it with pyspark.pandas.read_excel().

One way to handle invalid references is to use the openpyxl engine instead of xlrd. openpyxl can handle invalid references and replace them with a None value.

Here's an example of how you can read your Excel file using pyspark.pandas and the openpyxl engine:

python

import pandas as pd
from pyspark.sql.functions import col
from pyspark.sql.types import StringType
import pyspark.pandas as ps

# Set up the file path and sheet name
file_path = "/path/to/your/file.xlsx"
sheet_name = "sheet1"

# Set up the options and read the file
options = dict(header=1, keep_default_na=False, engine="openpyxl")
df_pandas = pd.read_excel(file_path, sheet_name=sheet_name, **options)

# Convert the pandas dataframe to a PySpark DataFrame
df_spark = ps.DataFrame(df_pandas).to_spark()

# Replace #REF values with None
df_spark = df_spark.withColumn(
"_tmp",
col("invalid_column_name").cast(StringType()).cast("double")
).drop("invalid_column_name")

# Show the resulting dataframe
df_spark.show()

In this example, read_excel() is configured to use the openpyxl engine instead of xlrd using the engine="openpyxl" option. This allows you to read the Excel file and handle invalid references.

After reading the file, the resulting Pandas dataframe is converted to a PySpark dataframe using pyspark.pandas.DataFrame(df_pandas).to_spark(). A temporary column ("_tmp") is then created by casting the problematic column to a double, and is then cast again to string. Finally, #REF values are replaced with None.

This approach should allow you to read your Excel file into PySpark and handle invalid references.