Character encoding issue exporting rails data to CSV

Question

I'm exporting data to a CSV file in rails and in some of my fields, I'm getting character encoding issues like this when I open in Excel:

didnâ€™t

I borrowed this code from an example and I'm assuming the encoding is off. Any idea what it should be?

send_data csv_data,
      :type => 'text/csv; charset=iso-8859-1; header=present',
      :disposition => "attachment; filename=#{filename}.csv"

How is `csv_data` being generated? Rails usually defaults to UTF-8 so it may be enough to change it to `charset=utf-8;` in the second line. — Jordan Running, Mar 09 '12 at 18:43

score 18 · Accepted Answer · answered Jul 12 '12 at 11:43

18

When Excel opens the CSV file it just assumes an "iso-8859-1" character encoding. I guess it doesn't even know about the encoding information you send along within your HTTP reply. That's why setting this to UTF-8 doesn't work.

So in order to export your CSV file for Excel in Rails you could do this:

send_data Iconv.conv('iso-8859-1//IGNORE', 'utf-8', csv_data),
  :type => 'text/csv; charset=iso-8859-1; header=present',
  :disposition => "attachment; filename=#{filename}.csv"

This re-encodes your UTF-8 data string (that's the Rails default) to ISO-8859 and sends it. Along goes the information that this reply is actually ISO-8859-1 encoded (which won't make a difference for Excel but is technically correct if you should open it in a browser etc.).

answered Jul 12 '12 at 11:43

bass-t

706
7
8

15

With Ruby 1.9 you can handle the character encoding with the String class. Also the disposition default can be used. This results in a simpler: `send_data csv_string.encode("iso-8859-1"), :filename => fname, :type => 'text/csv; charset=iso-8859-1; header=present'` – bass-t Jun 19 '13 at 09:39
1

In my case the accepted answer did not work (Iconv constant not recognized). However, the solution proposed in the comment above worked just fine. I'm still up-voting this answer and the comment, since the overall idea of converting the string encoding is correct. – Ernesto Nov 27 '14 at 16:25
2

`Iconv` is deprecated as of Ruby 1.9.3. However, `send_data csv_string.encode("iso-8859-1"), :filename => fname, :type => 'text/csv; charset=iso-8859-1; header=present'` didn't work for me. – netwire May 27 '16 at 21:44

score 15 · Answer 2 · answered Oct 30 '13 at 09:27

15

This worked for me, with Chinese characters!excel csv fromat (BOM + UTF8)

def export_csv_excel
  ....

  # Add BOM to make excel using utf8 to open csv file
  head = 'EF BB BF'.split(' ').map{|a|a.hex.chr}.join()

  csv_str = CSV.generate(csv = head) do |csv|
    csv << [ , , , ...]
    @invoices.each do |invoice|
      csv << [ , , , ...]
    end
  end

  send_data csv_str, filename: "Invoices-#{Time.now.strftime("%y%m%d%H%M%S")}.csv", type: "text/csv"
end

source(Chinese): http://blog.inheart.tw/2013/09/rubyraisl-csv-excel.html

answered Oct 30 '13 at 09:27

Simon Liu

463
5
12

1

Hi, can you explain how this works as CSV.generate(csv = head) accepts a hash? and where does the 'csv' come from? its haven't been defined yet? – user1883793 Oct 31 '13 at 09:19
ships with ruby. [ruby 1.9.3 csv](http://ruby-doc.org/stdlib-1.9.3/libdoc/csv/rdoc/CSV.html#method-i-headers) – Simon Liu Nov 12 '13 at 06:34
require 'csv' # add this! – Simon Liu Nov 12 '13 at 06:43
It's working without `csv =` just `CSV.generate(head)` – Artem P May 28 '17 at 22:12
1

More concise : `head = %w[EF BB BF].map { |a| a.hex.chr }.join` – ArnoHolo Oct 13 '17 at 10:09

score 10 · Answer 3 · edited May 23 '17 at 12:24

The answers above did not work for me on Mac Excel: Using iso-8859-1 would require I replace/remove weird characters, which is not a good enough solution for me, and using BOM with UTF8 worked under Windows but not under Mac Excel.

What worked for me is the WINDOWS-1252 encoding as suggested by https://stackoverflow.com/a/20194266/226255

def self.to_csv(options = {})
  (CSV.generate(options) do |csv|
    csv << self.headers

    all.each do |e|
      csv << e.values
    end
   end).encode('WINDOWS-1252', :undef => :replace, :replace => '')
end

score 0 · Answer 4 · answered Mar 17 '22 at 16:15

module DownloadService
  def student_list
   File.open("#{file_name}", "w+:UTF-16LE:UTF-8") do |f|
      file = CSV.generate({:col_sep => "\t"}) do |c| 
        c << ['Canción ', 'años', 'etc']
      end
      f.write "\xEF\xBB\xBF"
      f.write(file)
   end
  end
end

Character encoding issue exporting rails data to CSV

4 Answers4

Linked