Load pipe delimited CSV data having " (double quote) in one of the column in hive

Question

I have data as below:-

Rollno|Name|height|department
101|Aman|5"2|C.S.E

Taking all the columns as string. When I am loading above data in hive I am getting extra quote at start and end as below:- Rollno:-"101 Name:-Aman Height:-5"2 Department:-C.S.E" Can anyone help me with the solution.

Answer 1

Specify your separator such as:

val df = spark.read.option("header","true").option("inferSchema","true").option("sep", "|").csv("test.csv")
df.show(false)

+------+----+------+----------+
|Rollno|Name|height|department|
+------+----+------+----------+
|101   |Aman|5"2   |C.S.E     |
+------+----+------+----------+