1

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.

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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, 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.
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.
Oh ok, great to know that @Arne
0

Do you get the same result with the following?

cp1252= Iconv.conv("CP1252", "UTF8", text)
Improve this answer

3 Comments

Yes, it produces the same result. It is like no conversion at all.
does it work for you? what is different with your setup then?
Strange, I will have to test and get back to you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.