10

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 enter image description here

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)
Improve this question
5
  • Did you try to open the file with text editor instead of Excel? If it doesn't give these (é) symbols, you just need to correctly import the file in Excel. Don't double click the file but open a blank document, go to data in the ribbon - import from text - follow dialog instructions. Important in this is to set the correct encoding (after you selected which file to import). Commented Jun 24, 2016 at 14:03
  • Please see the edit. Commented Jun 24, 2016 at 14:12
  • In that case take a look at this question Commented Jun 24, 2016 at 14:14
  • You do not need the sys.setdefaultencoding("utf-8") line. Remove it before you break something unexpectedly Commented Jun 25, 2016 at 20:47
  • Wouldn't it be easier to just generate an Excel file instead of that annoying CSV format? Commented Jun 25, 2016 at 20:50

3 Answers 3

Reset to default
18

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

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

1 Comment

Saved my day! Works perfect good with latin characters, such as ç, á, é, í.
8

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'中国人'])
Improve this answer

Comments

4

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)
Improve this answer

5 Comments

Overkill. 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 an encoding parameter. In fact, in Python 2 csv doesn't support encodings directly (see csv documentation for a workaround). In Python 3, all that is required is opening the file with an encoding.
Adding a BOM is the solution to opening a CSV correctly in Excel, however.
Sorry, I didn't see you were using unicodecsv. My comments about csv.writer pertain to the built-in csv module.
example works everywhere but on my Mac's excel app
That just doesn't work. myFile = csv.writer(csvfile, dialect="excel", encoding='utf-8-sig') will result in ERROR] TypeError: 'encoding' is an invalid keyword argument for this function

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.