While Reading CSV, last column is coming as Null in Spark, Scala

user_11797565 0
Viewed 534

When I am trying to read a pipe separated file using Spark and scala like below:

1|Consumer Goods|101|
2|Marketing|102|

I am using the command:

val part = spark.read
    .format("com.databricks.spark.csv")
    .option("delimiter","|")
    .load("file_name")

I am getting result as:

+---+--------------+---+----+
|_c0|           _c1|_c2| _c3|
+---+--------------+---+----+
|  1|Consumer Goods|101|null|
|  2|     Marketing|102|null|
+---+--------------+---+----+

Spark is reading last column which is not present in source file because of delimiter mentioned as pipe. Is there any alternative to this where I can get result as:

+---+--------------+---+
|_c0|           _c1|_c2|
+---+--------------+---+
|  1|Consumer Goods|101|
|  2|     Marketing|102|
+---+--------------+---+
pyspark apache-spark dataframe csv scala
2 Answers

One solution would be to simply drop the last column like this:

part
  .select(part.columns.dropRight(1).map(col) : _*)
  .show(false)
+---+--------------+---+
|_c0|_c1           |_c2|
+---+--------------+---+
|1  |Consumer Goods|101|
|2  |Marketing     |102|
+---+--------------+---+

Another solution would be to read the file as a text file and split it yourself like this:

val text = spark.read.text("file_name")
// Note that the split functions in java/scala/spark ignores a separator that ends
// a string, but that one that starts one
val size = text.head.getAs[String]("value").split("\\|").size

text
  .withColumn("value", split('value, "\\|"))
  .select((0 until size).map(i => 'value getItem i as s"_c$i") : _*)
  .show(false)
+---+--------------+---+
|_c0|_c1           |_c2|
+---+--------------+---+
|1  |Consumer Goods|101|
|2  |Marketing     |102|
+---+--------------+---+
user_88936860

You can use below options

df.drop(df.columns(0)) -- for dropping last column in scala

df.drop(df.columns[-1]) -- for dropping last column in pyspark
user_70762750
Related