title: How to get past Excel CSV encoding nightmare: "\xEF" from ASCII-8BIT to UTF-8" using Ruby on Rails
body: <p>I'm trying to parse CSV files in Rails, which works great except for anything saved in Excel (testing with Version 16.26) for both Windows and Mac (CSVs saved in Numbers &amp; Google sheets work fine). Any character with an accent produces <code>"Encoding::UndefinedConversionError: "\xEF" from ASCII-8BIT to UTF-8".</code></p>
  
  <p>Excel claims it saves in UTF-8.</p>
  
  <p>I want accented characters to not throw errors when I upload CSVs saved in Excel.</p>
  
  <p>Things I've tried:</p>
  
  <ol>
  <li><p>setting the read encoding to bom|utf-8 (as per the <a href="https://stackoverflow.com/questions/543225/how-to-avoid-tripping-over-utf-8-bom-when-reading-files">BOM link</a>), utf-8, iso-8859-1, utf-16, windows-1252, ascii-8bit (and cycling through each of these in an array incase one fails then dropping it out of the array)</p></li>
  <li><p>current code uses ISO8859-1:UTF-8 which is supposed to read in ISO8859-1 then encode in UTF-8</p></li>
  <li><p>Creating a tempfile, converting it to binmode, CSV.parse(temp.path,  encoding: "bom|utf-8") per <a href="https://stackoverflow.com/questions/51756008/file-encoding-issue-when-downloading-file-from-aws-s3">the first answer in this thread</a>.</p></li>
  </ol>
  
  <pre><code>data = CSV.parse(csv, headers: true, header_converters: :symbol, skip_blanks: true, encoding: 'ISO8859-1:UTF-8')
  </code></pre>
  
  <p>It also works if I take a csv saved in Excel, then save it in google sheets or Numbers then upload it. Unfortunately, Excel is the most common CSV uploaded by our users.</p>