Saving data from python to excel file as CSV UTF-8 file format

Question

I have been trying to save the data as a excel file as a type of
CSV UTF-8 (Comma delimited) (*.csv) which is different then the normal
CSV (Comma delimited) (*.csv) file. It display the unicode text when opened in excel. I can save as that file easily from excel but from python i am only able to save it as normal csv. Which will not cause loss of data but when opened it shows this kind of text "à¤à¤‰à¤Ÿà¤¾" instead of "एउटा" this text.

If I copied the text opening it with notepad to the excel file and then manually save the file as CSV UTF-8 then it preserves the correct display. But doing so is time consuming since all values appear in same line in notepad and i have to separate it in excel file. So i just want to know how can i save data as CSV UTF-8 format of excel using python.

I have tried the follwing code but it results in normal csv file.

import codecs
import unicodecsv as csv

input_text = codecs.open('input.txt', encoding='utf-8')
all_text = input_text.read()
text_list = all_text.split()

output_list = [['Words','Tags']]
for input_word in text_list:
    word_tag_list = [input_word,'O']
    output_list.append(word_tag_list)

with codecs.open("output.csv", "wb") as f:
    writer = csv.writer(f)
    writer.writerows(output_list)

fantabolous · Accepted Answer · 2023-04-14 00:10:09Z

4

You need to indicate to Excel that this is a UTF-8 file. Unfortunately the only way to do this is by prepending a special byte sequence to the front of the file. Python will do this automatically if you use a special encoding.

with codecs.open("output.csv", "w", encoding="utf_8_sig") as f:

edited Apr 14, 2023 at 0:10

fantabolous

22.9k8 gold badges58 silver badges52 bronze badges

answered Aug 17, 2019 at 18:05

Mark Ransom

310k44 gold badges423 silver badges660 bronze badges

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

2 Comments

Sujil Devkota Over a year ago

with codecs.open("output.csv", "w", encoding="utf_8_sig") as f: this result in TypeError:utf_8_encode() argument 1 must be str, not bytes

Sujil Devkota Over a year ago

Yeah I have done that, same error when encoding="utf_8_sig" is written

Sujil Devkota · Accepted Answer · 2019-08-18 04:49:49Z

I have found the answer. The encoding="utf_8_sig" should be given to csv.writer method to write the excel file as CSV UTF-8 file. Previous code can be witten as:

with open("output.csv", "wb") as f:
    writer = csv.writer(f, dialect='excel', encoding='utf_8_sig')
    writer.writerows(output_list)

However there was problem when data has , at the end Eg: "भने," For this case i didn't need the comma so i removed it with following code within the for loop.

import re

if re.search(r'.,$',input_word):
    input_word = re.sub(',$','',input_word)

Finally I was able to obtain the output as desired with Unicode character correctly displayed and removing extra comma which is present at the end of data. So, if anyone know how to ignore comma at the end of data in excel file then you can comment here. Thanks.

Collectives™ on Stack Overflow

Saving data from python to excel file as CSV UTF-8 file format

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related