Rows with empty cells incorrectly marked as corrupt in Permissive mode

[eohehir]

Hi,

I am trying to load an Excel file (suppliers_testing.xlsx) in Permissive mode. This file has empty cells at the end of a row. The dataframe columns for these cells allow nulls.

When I attempt this, the rows are marked as invalid with a comment going into my nominated columnNameOfCorruptRecord column.

The PySpark code I use is:

 schema = StructType() \
                  .add("client_name", StringType(), False) \
                  .add("client_street", StringType(), True) \
                  .add("client_city", StringType(), True) \
                  .add("client_state", StringType(), True) \
                  .add("client_zip", StringType(), True) \
                  .add("client_country", StringType(), True) \
                  .add("client_industry_code", StringType(), True) \
                  .add("contact_name", StringType(), True) \
                  .add("contact_email", StringType(), True) \
                  .add("contact_role", StringType(), True) \
                  .add("_corrupt_record", StringType(), True)

 clients = spark.read.format("excel") \
                  .option("mode", "PERMISSIVE") \
                  .option("header",True) \
                  .option("dataAddress", "'Client Table'!A1") \
                  .option("columnNameOfCorruptRecord", "_corrupt_record") \
                  .option("treatEmptyValuesAsNulls", True) \
                  .schema(schema) \
                  .load('/my/path/here/suppliers_testing.xlsx)

I looked into the underlying xml for suppliers_testing.xlsx and could see that there was no mention at all of the empty columns in the specific row.

1. Rename suppliers_testing.xlsx to suppliers_testing.zip
2. Double click on suppliers_testing.zip in windows explorer
3. Browse to xl\worksheets\sheet1.xml
4. Look for the second row (<row r="2") and observe the columns. There are no H2, I2 or J2 cells.

I edited the excel file and added a border around the empty cells. I can now see the previously missing cells in the underlying xml and the file does load using the code above.

Has anyone come across this issue before? Is there some setting I can use to programmatically deal with. I would prefer not to use the adding a border manual workaround.

Versions:

• Spark: 3.3.0
• spark-excel: 2.12.3.3.1_0.18.5

Thanks,

Eoin.

suppliers_testing.xlsx