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"
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.).
send_data csv_string.encode("iso-8859-1"), :filename => fname, :type => 'text/csv; charset=iso-8859-1; header=present'
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.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
csv = just CSV.generate(head)
head = %w[EF BB BF].map { |a| a.hex.chr }.joinThe 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
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
csv_databeing generated? Rails usually defaults to UTF-8 so it may be enough to change it tocharset=utf-8;in the second line.