spark excel reading custom cell

[Jothia]

Hi all,

some one help me to read excel with custom format cells _(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_) from databricks using spark.excel read

[Vidhi_Khaitan]

Hi @Jothia 

I believe you need to replicate the display format, implement the formatting logic in Spark after reading.

Use spark excel normally which will give you raw numeric/text values -

df = (spark.read.format("com.crealytics.spark.excel")
.option("header", "true")
.option("inferSchema", "true")
.load("dbfs:/mnt/path/to/your/file.xlsx"))

df.show()

If you want to mimic the Excel format, you can apply it later in Spark -

from pyspark.sql import functions as F

df_formatted = df.withColumn(
"formatted_value",
F.when(F.col("your_col") < 0,
F.concat(F.lit("("), F.format_number(F.col("your_col"), 0), F.lit(")")))
.when(F.col("your_col") == 0, F.lit("-"))
.otherwise(F.format_number(F.col("your_col"), 0))
)

 Hope this helps!

[BS_THE_ANALYST]

@Jothia I'd be happy to jump in and take a look if the problem isn't resolved with the post from @Vidhi_Khaitan . Seems like an interesting problem! 👌.

I guess, as @Vidhi_Khaitan mentions, we'll need to apply those steps after we've read it in. There may also be alternate options with Python libraries which have richer support for Excel files to read the custom components in. 

The .xlsx file is just a .zip file, there will certainly be ways to tear it apart and read the appropriate bits should it need to get to that. There is consideration needed here for risk vs reward and time spent. Does this really need to be automated for reading the data? How often does this occur etc? Or can you just format it once it's read it as per @Vidhi_Khaitan's answer. 

All the best,
BS

[SebastianRowan]

Anyone managed to read excel files with weird custom number formats in Spark?

[BS_THE_ANALYST]

I don't think this should be too hard to handle with a python library @SebastianRowan. I'm happy to take a look if you could provide an example of where it doesn't work 👍.

All the best,
BS