DROPMALFORMED not returning proper result in apache spark

Question

I need to load text file into Spark dataframe where I'm trying to skip header and footer using DROPMALFORMED mode, but its not being honored.

Code:

val df1 = sparkSession.read.format("csv")
  .option("header", “true”) 
  .option("mode", "DROPMALFORMED") 
  .option("delimiter",";")
  .load(“/xxx/xxx/xxxx/test.txt")
  //df1.show(false)

File test.txt content:

04/11/2020

name;age;id
asdildsh;12;1
ram;13;2
oma;23;3
radahea;14;4
hellohow

Desired output:

+--------+----+---+
|name    |age |id |
+--------+----+---+
|asdildsh| 12 | 1 |
|ram     | 13 | 2 |
|oma     | 23 | 3 |
|radahea | 14 | 4 |
+--------+----+---+ 

Answer 1

Perhaps this is useful-

  val path  = getClass.getResource("/header_footer_file.txt").getPath
    /**
      * File content - header_footer_file.txt
      * ---------------------------------------
      * 04/11/2020
      *
      * name;age;id
      * asdildsh;12;1
      * ram;13;2
      * oma;23;3
      * radahea;14;4
      * hellohow
      */
    val stringDS = spark.read.text(path).as(Encoders.STRING)
        .filter(s => s.contains(";"))
     stringDS.show(false)
    /**
      * +-------------+
      * |value        |
      * +-------------+
      * |name;age;id  |
      * |asdildsh;12;1|
      * |ram;13;2     |
      * |oma;23;3     |
      * |radahea;14;4 |
      * +-------------+
      */

    val df = spark.read
      .option("sep", ";")
      .option("inferSchema", "true")
      .option("header", "true")
      .option("nullValue", "null")
      .csv(stringDS)

    df.show(false)
    df.printSchema()

    /**
      * +--------+---+---+
      * |name    |age|id |
      * +--------+---+---+
      * |asdildsh|12 |1  |
      * |ram     |13 |2  |
      * |oma     |23 |3  |
      * |radahea |14 |4  |
      * +--------+---+---+
      *
      * root
      * |-- name: string (nullable = true)
      * |-- age: integer (nullable = true)
      * |-- id: integer (nullable = true)
      */