title: Ruby : sanitize CSV with irregular fields
body: <p>I have a CSV file with quite irregular entries. The first entry of a row doesn't have any surrounding quotes, the whole line is quoted, and every field is double quoted like this :</p>
  
  <pre><code># my_file.csv, opened with sublime text :
  
  # Headers
  "first_name,""last_name"",""username"",""phone_number"",""address"",""email_address"",""email_address_confirmed"",""joined_at"",""status"",""is_admin"",""accept_emails_from_admin"",""language"",""can_post_listings"""
  
  # Sample entry
  "Mr X,""Mr X"",""mrxxx"","""","""",""mr@mrx.com"",""true"",""2015-09-21 09:08:51 UTC"",""accepted"",""true"",""true"",""fr"",""true"""
  </code></pre>
  
  <p>I could preprocess the file using something else than Ruby (Excel, a simple regex/replace, or anything you could think of), but since I am likely to have to do this several times, a Ruby solution would be great.</p>
  
  <p>Currently I am using just</p>
  
  <pre><code>csv = File.open(csv_file_path)
  CSV.parse(csv, :headers =&gt; true)
  </code></pre>
  
  <p>And I don't really see how I could easily fix this difference just for the first entry of each row...</p>
  
  <p>The problem is the CSV is not parsed correctly and instead it considers each row to be one single string (instead of an array with as many items as columns).</p>
  
  <pre><code># csv.headers : note this is an array with a single string
  ["first_name,\"last_name\",\"username\",\"phone_number\",\"address\",\"email_address\",\"email_address_confirmed\",\"joined_at\",\"status\",\"is_admin\",\"accept_emails_from_admin\",\"language\",\"can_post_listings\""]
  
  # csv.to_a.last
  ["xxx,\"xxxx\",\"martin\",\"\",\"\",\"xxx@xxxx.com\",\"false\",\"2016-05-12 13:06:53 UTC\",\"pending_email_confirmation\",\"false\",\"true\",\"fr\",\"false\""]
  </code></pre>
  
  <p>EDIT : I have tried the following</p>
  
  <pre><code>processed = File.readlines(path).map do |row|
      row.strip                 # strip newlines
        .gsub(/^\"|\"$/, '')   # remove outer quotes
        .gsub(/\"\"/, '"')     # fix double quotes
  end
  CSV.parse(processed.join('\n'))
  </code></pre>
  
  <p>I run into a <code>CSV::MalformedCSVError: Missing or stray quote in line 1</code></p>
  
  <p>Sample outputs</p>
  
  <pre><code># File.readlines(path).first
  # =&gt; "\"first_name,\"\"last_name\"\",\"\"username\"\",\"\"phone_number\"\",\"\"address\"\",\"\"email_address\"\",\"\"email_address_confirmed\"\",\"\"joined_at\"\",\"\"status\"\",\"\"is_admin\"\",\"\"accept_emails_from_admin\"\",\"\"language\"\",\"\"can_post_listings\"\"\"\n"
  
  # processed.first
  # =&gt; "first_name,\"last_name\",\"username\",\"phone_number\",\"address\",\"email_address\",\"email_address_confirmed\",\"joined_at\",\"status\",\"is_admin\",\"accept_emails_from_admin\",\"language\",\"can_post_listings\""
  </code></pre>
  
  <p>EDIT 2</p>
  
  <p>Ouch, I have some nested commas sometimes, and @Dave's answer seem to fail for those cases. There is this field</p>
  
  <blockquote>
    <p>""45, street_addr - Place""</p>
  </blockquote>
  
  <p>which contains a comma that is not a separator.
  The full entry</p>
  
  <pre><code>"Mr x,""Mr xx"",""bbernelin"","""",""45, street_addr - Place"",""xxx@xxx.fr"",""true"",""2016-04-13 11:14:08 UTC"",""accepted"",""false"",""true"",""fr"",""true"""
  </code></pre>