Python CSV write to file unreadable in Excel (Chinese characters)

Question

I am trying to perform text analysis on Chinese texts. The program is given below. I got the result with unreadable characters like 浜烘皯鏃ユ姤绀捐 . And if I change the output file result.csv to result.txt , the characters are correct as 人民日报社论 . So what's wrong with that? I can’t find out. I tried several ways, including add decoder and encoder .

  # -*- coding: utf-8 -*- import os import glob import jieba import jieba.analyse import csv import codecs segList = [] raw_data_path = 'monthly_raw_data/' file_name = ["201010", "201011", "201012", "201101", "201103", "201105", "201107", "201109", "201110", "201111", "201112", "201201", "201202", "201203", "201205", "201206", "201208", "201210", "201211"] jieba.load_userdict("customized_dict.txt") for name in file_name: all_text = "" multi_line_text = "" with open(raw_data_path + name + ".txt", "r") as file: for line in file: if line != '\n': multi_line_text += line templist = multi_line_text.split('\n') for text in templist: all_text += text seg_list = jieba.cut(all_text,cut_all=False) temp_text = [] for item in seg_list: temp_text.append(item.encode('utf-8')) stop_list = [] with open("stopwords.txt", "r") as stoplistfile: for item in stoplistfile: stop_list.append(item.rstrip('\r\n')) text_without_stopwords = [] for word in temp_text: if word not in stop_list: text_without_stopwords.append(word) segList.append(text_without_stopwords) with open("results/result.csv", 'wb') as f: writer = csv.writer(f) writer.writerows(segList) 

Answer 1

UTF-8 encoding for Excel requires the BOM code (byte order) written at the beginning of the file, or it will assume ANSI encoding, which is language dependent. U+FEFF - Unicode specification. Here is an example that opens in Excel correctly:

 #!python2 #coding:utf8 import csv data = [[u'American',u'美国人'], [u'Chinese',u'中国人']] with open('results.csv','wb') as f: f.write(u'\ufeff'.encode('utf8')) w = csv.writer(f) for row in data: w.writerow([item.encode('utf8') for item in row]) 

For completeness, Python 3 makes this easier. Note newline='' instead of wb and utf-8-sig encoding automatically adds the specification. Unicode strings are written directly, and not to encode each element.

 #!python3 #coding:utf8 import csv data = [[u'American',u'美国人'], [u'Chinese',u'中国人']] with open('results.csv','w',newline='',encoding='utf-8-sig') as f: w = csv.writer(f) w.writerows(data) 

There is also a third-party unicodecsv module that simplifies Python 2:

 #!python2 #coding:utf8 import unicodecsv data = [[u'American',u'美国人'], [u'Chinese',u'中国人']] with open('results.csv','wb') as f: w = unicodecsv.writer(f,encoding='utf-8-sig') w.writerows(data) 

Answer 2

Here is another way, some complicated:

 #!python2 #coding:utf8 import csv data = [[u'American',u'美国人'], [u'Chinese',u'中国人']] with open('results.csv','wb') as f: f.write(u'\ufeff'.encode('utf8')) w = csv.writer(f) for row in data: w.writerow([item.encode('utf8') for item in row]) 

This code block generates an utf-8 encoded csv file.

• open a file using notepad ++ (or another editor with encoding function)
• Coding -> Convert to ANSI
• save

Open the file using Excel, this is normal.