Spark: Read dat file with a special case

user_3725491 0
Viewed 5556

I have a .dat file delimited by \u0001. It should look like so

+---+---+---+
|A  |B  |C  |
+---+---+---+
|1  |2,3|4  |
+---+---+---+
|5  |6  |7  |
+---+---+---+

But the file I get has a lot of spaces between fields of a few rows

A\u0001B\u0001C
1\u0001"2,3"                                                                               \u00014
5\u00016\u00017

In the second row above, there are 79 spaces between two columns. Now when I read the file in Spark

val df = spark.read.format("csv").option("header", "true").option("delimiter", "\u0001").load("path")

df.show(false)

+---+-----------------------------------------------------------------------------------+----+
|A  |B                                                                                  |C   |
+---+-----------------------------------------------------------------------------------+----+
|1  |2,3                                                                               4|null|
+---+-----------------------------------------------------------------------------------+----+
|5  |6                                                                                  |7   |
+---+-----------------------------------------------------------------------------------+----+

Is there a way to fix this without changing the input file?

apache-spark-sql apache-spark
3 Answers

Try adding .option("ignoreTrailingWhiteSpace", true)

From the documentation:

ignoreTrailingWhiteSpace (default false): a flag indicating whether or not trailing whitespaces from values being read should be skipped.

EDIT: you need to turn off quotation to make it work with your example:

val df2 = spark.read.format("csv")
  .option("header", true)
  .option("quote", "")
  .option("delimiter", "\u0001")
  .option("ignoreTrailingWhiteSpace", true)
  .load("data2.txt")

Result:

df2.show()
+---+-----+---+
|  A|    B|  C|
+---+-----+---+
|  1|"2,3"|  4|
|  5|    6|  7|
+---+-----+---+

To remove the quotes you can try (note this will remove quotes inside your string):

import org.apache.spark.sql.functions.regexp_replace

df2.withColumn("B", regexp_replace(df2("B"), "\"", "")).show()
+---+---+---+
|  A|  B|  C|
+---+---+---+
|  1|2,3|  4|
|  5|  6|  7|
+---+---+---+
user_3771180

Do this, by setting: 'ignoreLeadingWhiteSpace' and 'ignoreTrailingWhiteSpace' to true, you will remove any white spaces:

val df = spark.read.format("csv")
         .option("header", true)
         .option("delimiter", "\u0001")
         .option("ignoreLeadingWhiteSpace", true)
         .option("ignoreTrailingWhiteSpace", true)
         .load("path")

df.show(10, false)
user_97360510

You need to remove the quote character property and set trailing property as

val df = spark.read.format("csv")
         .option("header", true)
         .option("delimiter", "\u0001")
         .option("quote", '')
         .option("ignoreLeadingWhiteSpace", true)
         .option("ignoreTrailingWhiteSpace", true)
         .load("path")
user_53527480
Related