Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Character encoding issue exporting rails data to CSV

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"
like image 413
Slick23 Avatar asked Mar 09 '12 18:03

Slick23


People also ask

What is UTF-8 encoding for a CSV?

UTF-8, or "Unicode Transformation Format, 8 Bit" is a marketing operations pro's best friend when it comes to data imports and exports. It refers to how a file's character data is encoded when moving files between systems.

What encoding should I use for CSV?

UTF-8 encoded CSV files will work well with Accompa whether they contain just English characters, or also contain non-English characters such as é, ç, ü.

Does CSV support UTF-8?

and CSV files. Simple CSV files do not support Unicode/UTF-8 characters. This is a limitation of the CSV format and not something that can be changed in DEAR.


2 Answers

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
like image 147
Abdo Avatar answered Sep 18 '22 14:09

Abdo


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

like image 23
Simon Liu Avatar answered Sep 19 '22 14:09

Simon Liu