Spark (Scala) Parsing issue of a fields having timestamp format as ("dd-MMM-yy hh:mm:ss:SSSSSSSSS aa")

user_2878298 0
Viewed 677

I want to parse a excel file. This file having few fields values as a timestamp format ("dd-MMM-yy hh:mm:ss:SSSSSSSSS aa") I have defined field type as timestamp but my application is unable to identify the datatype and failed to load the data, though if I use StringType as a data type then it able to parse the file but I do not want to use this alternate approach. Hence looking for the correct solution. My code is as following:

ReadExcel("C:path\to\the\raw_file\Consignments.xlsx", "A1", MySchema, spark,  "dd-MM-yyyy", "dd-MMM-yy hh:mm:ss:SSSSSSSSS aa")


def ReadExcel(path: String, dataAddress: String = "A2", Schema: StructType, spark: org.apache.spark.sql.SparkSession, datefmt: String = "dd-MM-yyyy", tsfmt: String = "dd-MM-yyyy HH:mm:ss"): DataFrame = {

    /**
     * Though Crealytics accept TimestampFormat Only
     * You can Create CustomSchema with DateType and Date values in data will be typed to Date
     */

    cleanHeaders(spark.read
      .format("com.crealytics.spark.excel")
      .option("dataAddress", dataAddress) //
      .option("useHeader", "false") // Required
      .option("treatEmptyValuesAsNulls", "true") // Optional, default: true
      .option("inferSchema", "false") // Optional, default: false
      .option("addColorColumns", "false") // Optional, default: false
      .option("timestampFormat", "dd-MM-yyyy HH:mm:ss") // Optional, default: yyyy-mm-dd hh:mm:ss[.fffffffff]
      //.option("maxRowsInMemory", 20) // Optional, default None. If set, uses a streaming reader which can help with big files
      //.option("excerptSize", 10) // Optional, default: 10. If set and if schema inferred, number of rows to infer schema from
      .schema(Schema)
      .load(path))
}

Sample Date data: 24-SEP-19 07.17.20.873000000 AM Please Note: I am using the Databricks notebook and crealytics library to read the excel file.

apache-spark-sql apache-spark excel datetime scala
1 Answers

@Venus . I don't think timestamp representation is right. 873000000 milliseconds when converted to days are over 10 days. I think you only have to consider first 3 digits of milliseconds. please check.

You can follow below approach if this is the case:

  1. Read the file first, substring to cut first 3 digits of milliseconds part of the timestamp column.
  2. Then use spark casting, mostly probably within the method withColumn and later use from_unixtime(unix_time(column, 'timestamp format'), 'format')
user_95694980
Related