title: How to load CSV file with records on multiple lines?
body: <p>I use Spark 2.3.0.</p>
  
  <p>As a Apache Spark's project I am using <a href="https://www.kaggle.com/stackoverflow/stacksample" rel="noreferrer">this</a> data set to work on. When trying to read csv using spark, row in spark dataframe does not corresponds to correct row in csv (See sample csv <a href="https://docs.google.com/document/d/122DGn22xkMKGLN65X8cIGHgxyNRZcSfpA4kpIU_atjo/edit?usp=sharing" rel="noreferrer">here</a>) file. Code looks like following:</p>
  
  <pre><code>answer_df = sparkSession.read.csv('./stacksample/Answers_sample.csv', header=True, inferSchema=True, multiLine=True);
  answer_df.show(2)
  </code></pre>
  
  <p><strong>Output</strong></p>
  
  <pre><code>+--------------------+-------------+--------------------+--------+-----+--------------------+
  |                  Id|  OwnerUserId|        CreationDate|ParentId|Score|                Body|
  +--------------------+-------------+--------------------+--------+-----+--------------------+
  |                  92|           61|2008-08-01T14:45:37Z|      90|   13|"&lt;p&gt;&lt;a href=""htt...|
  |&lt;p&gt;A very good re...| though.&lt;/p&gt;"|                null|    null| null|                null|
  +--------------------+-------------+--------------------+--------+-----+--------------------+
  only showing top 2 rows
  </code></pre>
  
  <p>However,
  When i used pandas, it worked like a charm.</p>
  
  <pre><code>df = pd.read_csv('./stacksample/Answers_sample.csv')
  df.head(3) 
  </code></pre>
  
  <p><strong>Output</strong></p>
  
  <pre><code>Index Id    OwnerUserId CreationDate    ParentId    Score   Body
  0   92  61  2008-08-01T14:45:37Z    90  13  &lt;p&gt;&lt;a href="http://svnbook.red-bean.com/"&gt;Vers...
  1   124 26  2008-08-01T16:09:47Z    80  12  &lt;p&gt;I wound up using this. It is a kind of a ha...
  </code></pre>
  
  <p><strong>My Observation:</strong>
  Apache spark is treating every line in csv file as a record for dataframe( which is reasonable) but on the other hand, pandas intelligently ( not sure based on which parameters) figures out where the record end actually.</p>
  
  <p><strong>Question</strong>
  I would like to know, how can i instruct Spark to load the dataframe properly.</p>
  
  <p>The data to be loaded is as follows with the lines starting with <code>92</code> and <code>124</code> being two records.</p>
  
  <pre><code>Id,OwnerUserId,CreationDate,ParentId,Score,Body
  92,61,2008-08-01T14:45:37Z,90,13,"&lt;p&gt;&lt;a href=""http://svnbook.red-bean.com/""&gt;Version Control with Subversion&lt;/a&gt;&lt;/p&gt;
  
  &lt;p&gt;A very good resource for source control in general. Not really TortoiseSVN specific, though.&lt;/p&gt;"
  124,26,2008-08-01T16:09:47Z,80,12,"&lt;p&gt;I wound up using this. It is a kind of a hack, but it actually works pretty well. The only thing is you have to be very careful with your semicolons. : D&lt;/p&gt;
  
  &lt;pre&gt;&lt;code&gt;var strSql:String = stream.readUTFBytes(stream.bytesAvailable);      
  var i:Number = 0;
  var strSqlSplit:Array = strSql.split("";"");
  for (i = 0; i &amp;lt; strSqlSplit.length; i++){
      NonQuery(strSqlSplit[i].toString());
  }
  &lt;/code&gt;&lt;/pre&gt;
  "
  </code></pre>