python - Issue with UTF-/ encoding on csv file for excel

Question

EDIT:

As suggested special chars are displayed correctly if I use notepad++ to open the csv file. They are displayed correctly too when I import the csv file into excel. How can I generate a csv file that is displayed correctly when opened by excel since file importing is not an option for the users

I'm generating a csv file that is being processed using Excel. Special caracters like 'é' are not displayed properly when the file is opened with excel

This the poc I'm using to generate the csv file

# -*- coding: utf-8 -*-
import unicodecsv as csv
import codecs
import sys
reload(sys)
sys.setdefaultencoding("utf-8")
def write_csv(file,headers):


    resultFile =codecs.open(file, "w+", "utf-8")

    #headers=[s.encode('utf-8') for s in headers]
    wr = csv.writer(resultFile, dialect='excel',delimiter=";",encoding="utf-8")
    wr.writerow(headers)

    resultFile.close()

headers=[""]
headers.append("Command")
headers.append("Vérification".encode('utf-8'))
write_csv(r"C:est2.csv",headers)

Answer 1

Python 2 solution using unicodecsv. Note that the documentation for unicodecsv says the module should be opened in binary mode (wb). Make sure to write Unicode strings. #coding is required to support non-ASCII characters in the source file. Make sure to save the source file in UTF-8.

#coding:utf8
import unicodecsv

with open('test.csv','wb') as f:
    # Manually encode a BOM, utf-8-sig didn't work with unicodecsv
    f.write(u'ufeff'.encode('utf8'))
    w = unicodecsv.writer(f,encoding='utf8')
    # Write Unicode strings.
    w.writerow([u'English',u'Chinese'])
    w.writerow([u'American',u'美国人'])
    w.writerow([u'Chinese',u'中国人'])

Python 3 solution. #coding is optional here because it defaults to UTF-8. Just make sure to save the source file in UTF-8. unicodecsv is no longer required. The built-in csv works correctly. csv documentation says to open the file with newline=''.

#coding:utf8
import csv

with open('test.csv','w',newline='',encoding='utf-8-sig') as f:
    w = csv.writer(f)
    # Write Unicode strings.
    w.writerow([u'English',u'Chinese'])
    w.writerow([u'American',u'美国人'])
    w.writerow([u'Chinese',u'中国人'])