views:

227

answers:

2

Hey, I want to export some data from my jruby on rails webapp to excel, so I create a csv string and send it as a download to the client using

send_data(text, :filename => "file.csv", :type => "text/csv; charset=CP1252", :encoding => "CP1252")

The file seems to be in UTF-8 which Excel cannot read correctly. I googled the problem and found that iconv can convert encodings. I try to do that with:

ic = Iconv.new('CP1252', 'UTF-8')
text = ic.iconv(text)

but when I send the converted text it does not make any difference. It is still UTF-8 and Excel cannot read the special characters. there are several solutions using iconv, so this seems to work for others. When I convert the file on the linux shell manually with iconv it works.

What am I doing wrong? Is there a better way?

Im using: - jruby 1.3.1 (ruby 1.8.6p287) (2009-06-15 2fd6c3d) (Java HotSpot(TM) Client VM 1.6.0_19) [i386-java] - Debian Lenny - Glassfish app server - Iceweasel 3.0.6

Edit: Do I have to include some gem to use iconv?

Solution: S.Mark pointed out this solution: You have to use UTF-16LE encoding to make excel understand it, like this:

text= Iconv.iconv('UTF-16LE', 'UTF-8', text)

Thanks, S.Mark for that answer.

+1  A: 

According to my experience, Excel cannot handle UTF-8 CSV files properly. Try UTF-16 instead.

Note: Excel's Import Text Wizard appears to work with UTF-8 too

Edit: A Search on Stack Overflow give me this page, please take a look that.

According to that, adding a BOM (Byte Order Mark) signature in CSV will popup Excel Text Import Wizard, so you could use it as work around.

S.Mark
My problem is that I can not convert the text at all. So UTF-16 is not an option.Thanks for the workaround I will try it now and use it when plan A fails. Unfortunately, requiring the users to select the correct encoding means more training effort and a lot of work for the support to explain that ...
Arne
@Arne, I just tried testing Iconv with UTF16le with this way, `Iconv.new('UTF-16LE', 'UTF-8')`, converting is working on irb, you may want to test that out too. Note that `UTF-16LE` and `UTF-16` is different, `UTF-16` has BOM signature in it.
S.Mark
That one worked! Thank you very much for your help. I highly appreciate that.I will post it in my OP for others to understand.
Arne
Oh ok, great to know that @Arne
S.Mark
A: 

Do you get the same result with the following?

cp1252= Iconv.conv("CP1252", "UTF8", text)
Jonas Elfström
Yes, it produces the same result. It is like no conversion at all.
Arne
does it work for you? what is different with your setup then?
Arne
Strange, I will have to test and get back to you.
Jonas Elfström