1

I'm working in a web application and I need to generate CSV file. the CSV file contains prices in euro like 140 € but when I open the CSV file, Excel doesn't show the Euro symbol correctly 140 € .

I read data from Oracle database, store CSV file content in List<String> and write the result in HttpServletResponse response like 

List<String> data = getCsvContent();
response.getOutPutStream().write(data.toString().getBytes("UTF-8"));

I'm working in Windows 7, MS Office 2010 and I would like to know if there is any way to tell Excel to open this file in UTF-8 encoding? I tried to add BOM but there is no effect.

please help me to solve this problem, thanks in advance

Edit:

I'm using AJAX Call to download file, before donwloading file, i add BOM to the result. when i open the file by double click Excel open the CSV correctly in developpement environnement (local) but when i deploy the application in remote server, the euro symbol in the csv file is not written correctly, that's mean Excel can't show the symbol correctly.

any one can tell what can be the problem?

loulid39
  • 21
  • 1
  • 4
  • You have to check with an other version of your Excel ( if you are opening your CSV with Excel ). The problem appears in some version of Excel for CSV. Also, you can set maybe an encoding – Fizik26 Nov 21 '19 at 13:05
  • I can reproduce your problem with a "regular UTF8 encoded CSV file with no BOM". However, if I add the BOM to the beginning of the file, it renders the euro symbol properly. Perhaps you are not properly adding the BOM? Have you examined the resultant CSV file with a hex editor to ensure the BOM is properly added at the beginning? – Ron Rosenfeld Nov 22 '19 at 01:29
  • Perhaps [How to add a UTF-8 BOM in java](https://stackoverflow.com/questions/4389005/how-to-add-a-utf-8-bom-in-java) will help. – Ron Rosenfeld Nov 22 '19 at 01:32

1 Answers1

3

I had the same problem. You can import it the following way:

  1. Create a new Excel Sheet.
  2. Go to Data --> From Text, like shown in the image below (German Excel Version)

enter image description here

  1. Set File origin to Unicode (UTF-8) inside the Popup.
  2. Click next and set your separator according to your .csv file.. (most likely comma)
  3. Click next to review all of your columns. You can set specific data formats for each colmn (like date, text...)

Finish the wizard and it should be working.

toffler
  • 1,231
  • 10
  • 27
  • 1
    i know this solution and some other ways to do it. but i need a solution because we can't tell client to open files by this way. client need to download file and double click in file and see the content correctly – loulid39 Nov 21 '19 at 14:07
  • I didn't know you are looking for a way client's can open your csv. Your question wasn't clear about that. Another way to change Excels Default Settings to accept utf-8 encoding is shown in this [answer](https://superuser.com/questions/911369/change-default-encoding-of-excel-to-utf-8) but I'm not sure that this is an easier way for clients – toffler Nov 21 '19 at 14:20