title: PySpark is not reading CSV properly
body: <p>I am saving data to a csv file from a Pandas dataframe with 318477 rows using <code>df.to_csv("preprocessed_data.csv")</code>. When I load this file in another notebook with:</p>
  
  <pre><code>df = pd.read_csv("preprocessed_data.csv")
  len(df)
  
  # out: 318477
  </code></pre>
  
  <p>The number of rows is as expected. However, when I try load the dataset with PySpark:</p>
  
  <pre><code>spark_df = spark.read.format("csv")
                       .option("header", "true")
                       .option("mode", "DROPMALFORMED")
                       .load("preprocessed_data.csv")
  spark_df.count()
  
  # out: 6422020
  </code></pre>
  
  <p>or</p>
  
  <pre><code>df_test = spark.sql("SELECT * FROM csv.`preprocessed_data.csv`")
  df_test.count()
  
  # out: 6422020
  </code></pre>
  
  <p>The number of rows is incorrect. The number of rows it reads, 6422020, is the number of lines in the csv file. Since there are rows whose contents spans multiple lines (i.e. <a href="https://imgur.com/a/qWd9jtq" rel="nofollow noreferrer">https://imgur.com/a/qWd9jtq</a>)</p>
  
  <p>How can I solve this problem? Am I required to save the csv somehow with no newline charaters in any text, or can I specify the csv reading in PySpark more specifically? </p>
  
  <p>This is continuing on from my previous question, where I now understand the problem more <a href="https://stackoverflow.com/questions/56257225/pyspark-read-csv-reading-incorrectly">link</a></p>
  
  <p>Lines from CSV file:</p>
  
  <pre><code>120,teacher industrial design technology mabel park state high school,teach queensland,2018-10-07,brisbane,southern suburbs logan,education training,teaching secondary,mabel park state high school invites applications for a industrial design and technology teacher,,0,30,,0.0,0.03003003003003003
  121,fabricatorinstaller,workplace access safety,2018-10-07,melbourne,bayside south eastern suburbs,trades services,welders boilermakers,trade qualified person with skills in welding and fabrication to assist in the manufacturing and installation of our custom height safety products,"&lt;p&gt;&amp;nbsp;&lt;/p&gt;
          &lt;p&gt;&lt;strong&gt;&lt;em&gt;*&amp;nbsp; Secure long term role with genuine career path to supervisor&lt;/em&gt;&lt;/strong&gt;&lt;/p&gt;
          &lt;p&gt;&lt;strong&gt;&lt;em&gt;*&amp;nbsp; Competitive hourly rate with regular opportunity for overtime&lt;/em&gt;&lt;/strong&gt;&lt;/p&gt;
          &lt;p&gt;&lt;strong&gt;&lt;em&gt;*&amp;nbsp; Full on-the-job training&lt;/em&gt;&lt;/strong&gt;&lt;/p&gt;
          &lt;p&gt;&lt;strong&gt;About the&amp;nbsp;role&lt;/strong&gt;&lt;/p&gt;
          &lt;p&gt;Having recently won a significant new national contract we are looking for another trade qualified person with welding and fabrication skills to help manage increased demands on our production and installation departments.&amp;nbsp; This role will
            see you involved in both manufacturing and on-site installation and there is a genuine career path to supervisor if that is your goal.&amp;nbsp; Initially your role will require you to:-&lt;/p&gt;
          &lt;ul&gt;
            &lt;li&gt;read and interpret drawings&amp;nbsp;&lt;/li&gt;
            &lt;li&gt;fabricate and assemble orders as required&lt;/li&gt;
            &lt;li&gt;provide input to enhance factory processes&lt;/li&gt;
            &lt;li&gt;pack&amp;nbsp;and dispatch orders&lt;/li&gt;
            &lt;li&gt;perform on-site installations (full training will be given)&lt;/li&gt;
          &lt;/ul&gt;
          &lt;p&gt;&lt;strong&gt;About you&lt;/strong&gt;&lt;/p&gt;
          &lt;p&gt;This role is ideal for a trade qualified person&amp;nbsp;(welder, boilermaker, fabricator etc) with good hands-on skills who will enjoy&amp;nbsp;dividing their time between&amp;nbsp;factory/manufacturing and on-site installations.&amp;nbsp; Because installations
            invariably take place on the roof, physical fitness is&amp;nbsp;essential.&lt;/p&gt;
          &lt;p&gt;&lt;strong&gt;What we offer&lt;/strong&gt;&lt;/p&gt;
          &lt;ul&gt;
            &lt;li&gt;A secure, long-term role with a successful, well-established organisation&lt;/li&gt;
            &lt;li&gt;Full, ongoing on-the-job training&lt;/li&gt;
            &lt;li&gt;Opportunity for career progression to supervisor for the right person&lt;/li&gt;
            &lt;li&gt;Opportunity to work&amp;nbsp;in a safe, supportive and friendly environment&lt;/li&gt;
            &lt;li&gt;Competitive hourly rate with regular opportunities for overtime&lt;/li&gt;
            &lt;li&gt;Occasional regional and interstate travel in response to major projects&lt;/li&gt;
          &lt;/ul&gt;
          &lt;p&gt;&lt;strong&gt;How to apply&lt;/strong&gt;&lt;/p&gt;
          &lt;p&gt;Please copy and paste the URL below into your browser (it is &lt;em&gt;not&lt;/em&gt; a live link so&amp;nbsp;must be copied and pasted).&amp;nbsp; This will take you to our custom online application form which includes a number of screening questions&amp;nbsp;and a
            profiling checklist which is an essential part of our application process.&lt;/p&gt;
          &lt;p&gt;&lt;strong&gt;https://exenet.expr3ss.com/jobDetails?selectJob=296&amp;amp;&lt;/strong&gt;&lt;/p&gt;
          &lt;p&gt;If you have any difficulties or would like more information please email &lt;a class=""_2L3qcJ0"" data-contact-match=""true"" href=""mailto:gayle@exhr.com.au""&gt;gayle@exhr.com.au&lt;/a&gt; or phone &lt;a class=""_2hhDNI-"" data-contact-match=""true"" href=""tel:0468 336 224""&gt;0468 336 224&lt;/a&gt;.&lt;/p&gt;",0,30,full time,0.0,0.03003003003003003
  122,boilermaker,rpm contracting qld pl,2018-10-07,brisbane,southern suburbs logan,trades services,welders boilermakers,perm rate 30 structural steel fab weld out located southside full time hours ongoing work ot modern clean facility offering great conditions,"&lt;p&gt;One of Australia's best engineering workshops is hiring!&lt;/p&gt;
          &lt;p&gt;They have ongoing, rolling projects and need good people now.&lt;/p&gt;
          &lt;p&gt;They are partnered with state and federal governments, international minerals and energy companies, and other market leading entities.&lt;/p&gt;
          &lt;p&gt;The workshop is state of the art, clean, and well-managed. There is a genuine focus on the safety and wellbeing of their people.&lt;/p&gt;
          &lt;p&gt;The facility and conditions are truly exceptional.&lt;/p&gt;
          &lt;p&gt;Secure and long term positions are on offer for forward-thinking, cooperative and professional tradesmen.&lt;/p&gt;
          &lt;p&gt;We are looking for qualified and/or ticketed boilermakers and 1st class welders that can offer high level trade skills.&lt;/p&gt;
          &lt;p&gt;Equally important is a cooperative, team-orientated attitude and a willingness to become involved and take ownership of their important role in this company.&lt;/p&gt;
          &lt;p&gt;They are building on a stable, permanent team, so candidates who step up can look forward to a secure future.&lt;/p&gt;
          &lt;p&gt;The position is ongoing, offering full-time hours, exceptional conditions, and penalties.&lt;/p&gt;
          &lt;p&gt;You require own car and licence, PPE and tools, relevant experience and to be available for an immediate start.&lt;/p&gt;
          &lt;p&gt;Good luck and kind regards,&lt;/p&gt;
          &lt;p&gt;RPM&lt;/p&gt;",0,30,full time,0.0,0.03003003003003003
  
  
  
  </code></pre>