Btukfyl

I have TSV file with many of lines. Much of the lines work fine but I have the issue of working with the following line:

tt7841930   tvEpisode   "Stop and Hear the Cicadas/Cold-Blooded "Stop and Hear the Cicadas/Cold-Blooded 0   2018    N  24  Animation,Family

I use Spark and Scala in order to load the file into DataFrame:

val titleBasicsDf = spark.read

  .format("org.apache.spark.csv")

  .option("header", true)

  .option("inferSchema", true)

  .option("delimiter", "    ")

  .csv("title.basics.tsv.gz")

As result I receive:

+---------+---------+-------------------------------------------------------------------------------+-------------+-------+---------+-------+----------------+------+-------------+--------+------------+------------+-------------+

|tconst   |titleType|primaryTitle                                                                   |originalTitle|isAdult|startYear|endYear|runtimeMinutes  |genres|averageRating|numVotes|parentTconst|seasonNumber|episodeNumber|

+---------+---------+-------------------------------------------------------------------------------+-------------+-------+---------+-------+----------------+------+-------------+--------+------------+------------+-------------+

|tt7841930|tvEpisode|"Stop and Hear the Cicadas/Cold-Blooded    "Stop and Hear the Cicadas/Cold-Blooded|0            |2018   |N       |24     |Animation,Family|null  |null         |null    |tt4947580   |6           |2            |

+---------+---------+-------------------------------------------------------------------------------+-------------+-------+---------+-------+----------------+------+-------------+--------+------------+------------+-------------+

So as you may see, the following data in the line:

"Stop and Hear the Cicadas/Cold-Blooded "Stop and Hear the Cicadas/Cold-Blooded

is not properly split into two different values for primaryTitle and originalTitle columns and primaryTitle contains both of them:

{

   "runtimeMinutes":"Animation,Family",

   "tconst":"tt7841930",

   "seasonNumber":"6",

   "titleType":"tvEpisode",

   "averageRating":null,

   "originalTitle":"0",

   "parentTconst":"tt4947580",

   "startYear":null,

   "endYear":"24",

   "numVotes":null,

   "episodeNumber":"2",

   "primaryTitle":""Stop and Hear the Cicadas/Cold-Bloodedt"Stop and Hear the Cicadas/Cold-Blooded",

   "isAdult":2018,

   "genres":null

}

What am I doing wrong and how to configure Spark to properly understand and split this line? As I mentioned previously, many of other lines from this file are split correctly into the proper columns.

I found the answer here: https://github.com/databricks/spark-csv/issues/89

The way to turn off the default escaping of the double quote character
(") with the backslash character () - i.e. to avoid escaping for all
characters entirely, you must add an .option() method call with just
the right parameters after the .write() method call. The goal of the
option() method call is to change how the csv() method "finds"
instances of the "quote" character as it is emitting the content. To
do this, you must change the default of what a "quote" actually means;
i.e. change the character sought from being a double quote character
(") to a Unicode "u0000" character (essentially providing the Unicode
NUL character assuming it won't ever occur within the document).

the following magic option did the trick:

.option("quote", "u0000")