Error truncating #REF with spark.read

[arthurandraderj]

Hello guys

I am trying to read an excel file and even using PERMISSIVE mode, its truncating the records that contains #REF in any column 😥

Can anyone please help me on that?

schema = StructType([\

        StructField('Col1', DateType(), True), \ <----------THIS COLUMN HAS #REF

        StructField('Col2', DateType(), True), \

        StructField('Col3', StringType(), True)])

test = (

                            spark.read.format("com.crealytics.spark.excel")

                            .option("header", header_option)

                            .option("parseMode", "PERMISSIVE")

                            .option("keepUndefinedRows", True)

                            .option("useNullForErrorCells", True)

                            .option("treatEmptyValuesAsNulls", True)

                            .option("setErrorCellsToFallbackValues", "true")

                            .option("maxRowsInMemory", 1000)

                            .option("useNullForErrorCells", True)

                            .option("dataAddress", "Test!A1:C200")

                            .schema(schema)

                            .load("File.xlsx")

[Kaniz_Fatma]

Hi @arthurandraderj, You can try reading the Excel file using Pandas with the xlrd engine, which supports the errors parameter. Set errors='coerce' to convert #REF values to NaN.