Issue with UTF-/ encoding on csv file for excel
Solution 1
I fixed this issue using UTF-8 BOM encoding.
# -*- coding: utf-8-sig-*-
import unicodecsv as csv
import codecs
import sys
reload(sys)
sys.setdefaultencoding("utf-8-sig")
def write_csv(file,headers):
resultFile =codecs.open(file, "w+", "utf-8-sig")
#headers=[s.encode('utf-8') for s in headers]
wr = csv.writer(resultFile, dialect='excel',delimiter=";",encoding="utf-8-sig")
wr.writerow(headers)
resultFile.close()
headers=[""]
headers.append("Command")
headers.append("Vérification")
write_csv(r"C:\Users\ATHENA-HDA\AppData\Local\Temp\test2.txt",headers)
Solution 2
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'中国人'])
Solution 3
In python3 I just do this:
with open(file, "w+", encoding='utf-8-sig') as f:
f.write("Vérification")
Pretty simple, right? :) You can search "utf-8-sig" in the python docs
isoman
Updated on June 28, 2022Comments
-
isoman almost 2 years
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:\test2.csv",headers)
-
Mark Tolonen almost 8 yearsOverkill. The
reload(sys)
trick is not needed and can cause errors. See: why setdefaultencoding will break code. The#coding
statement declares the encoding of the source file, so hopefully you saved the source file in UTF-8 w/ BOM.csv.writer
doesn't support anencoding
parameter. In fact, in Python 2csv
doesn't support encodings directly (seecsv
documentation for a workaround). In Python 3, all that is required is opening the file with an encoding. -
Mark Tolonen almost 8 yearsAdding a BOM is the solution to opening a CSV correctly in Excel, however.
-
Mark Tolonen almost 8 yearsSorry, I didn't see you were using
unicodecsv
. My comments aboutcsv.writer
pertain to the built-incsv
module. -
Michel Fernandes almost 4 yearsSaved my day! Works perfect good with latin characters, such as ç, á, é, í.
-
Ricky Levi almost 2 yearsexample works everywhere but on my Mac's excel app