Spark read for csv file with quotes on one column in file

user_3240790 0
Viewed 670

I have a csv files in my HDFS location and it has quotes on one column . my file is records.csv , here is data of it

 100,"surender,CHN",IND
 101,"ajay,HYD",IND



 scala> val schema = StructType(
 | Array(
 | StructField("emp_id", StringType, true),
 | StructField("emp_name", StringType, true),
 | StructField("emp_city", StringType, true),
 | StructField("emp_country", StringType, true)
 | )
 | )
 schema: org.apache.spark.sql.types.StructType = StructType(StructField(emp_id,StringType,true), StructField(emp_name,StringType,true), StructField(emp_city,StringType,true), StructField(emp_country,StringType,true))

 scala>

 scala> val loc = "/user/omega/records.csv"
 loc: String = /user/omega/records.csv

                                                                                          ^

 scala> val loc = "/user/omega/records.csv"
 loc: String = /user/omega/records.csv

 scala> val df = spark.read.option("delimiter", ",").option("quote", "\"").option("escape", "\"").schema(schema).csv(loc)
 df: org.apache.spark.sql.DataFrame = [emp_id: string, emp_name: string ... 2 more fields]

  scala> df.show(10,false)
  +------+------------+--------+-----------+
  |emp_id|emp_name    |emp_city|emp_country|
  +------+------------+--------+-----------+
  |100   |surender,CHN|IND     |null       |
  |101   |ajay,HYD    |IND     |null       |
  +------+------------+--------+-----------+

but my expected output is

  +------+------------+--------+-----------+
  |emp_id|emp_name    |emp_city|emp_country|
  +------+------------+--------+-----------+
  |100   |surender    |CHN     |IND       |
  |101   |ajay        |HYD     |IND       |
  +------+------------+--------+-----------+

How do i get the expected output ?

I tried another code as below

  val df1 = spark.read.option("delimiter", ",").option("quote", "").option("escape quote", "").schema(schema).csv(loc)

and the above df1 gives below results

 +------+---------+--------+-----------+
 |emp_id| emp_name|emp_city|emp_country|
 +------+---------+--------+-----------+
 |   100|"surender|    CHN"|        IND|
 |   101|    "ajay|    HYD"|        IND|
 +------+---------+--------+-----------+
apache-spark dataframe scala
1 Answers

A simple solution could be to clean the data after reading the CSV

df
  .withColumn("emp_name", split(col("column_with_quotes"), ",").getItem(0))
  .withColumn("emp_city", split(col("column_with_quotes"), ",").getItem(1))
  .drop("column_with_quotes")

Later Update

I looked into the CSV options. Have you checked this options ?

unescapedQuoteHandling BACK_TO_DELIMITER #Defines how the CsvParser will handle values with unescaped quotes
user_153374580
Related