Writing unicode data in python

Question

I have an xlsx file that I need to convert to csv, I used openpyxl module along with unicodecsv for this. My problem is that while writing some files I am getting some junk characters in output. Details below

One of my file has unicode code point u'\xa0' in it which corresponds to NON BREAK SPACE, but when converted to csv my file shows the Â instead of space. While printing the same data on console using Python GUI it prints perfectly without any Â. What am I doing wrong here? any help is appreciated.

Sample Code:

import unicodecsv
from openpyxl import load_workbook

xlsx_file=load_workbook('testfile.xlsx',use_iterators=True)
with open('test_utf.csv','wb') as open_file:
    csv_file=unicodecsv.writer(open_file)
    sheet=xls_file.get_active_sheet()
    for row in sheet.iter_rows():
        csv_file.writerow(cell.internal_value for cell in row)

P.S: The type of data written is Unicode.

Are you loading the csv back in microsoft excel? The handling of unicode in csv files is a little wonky. Excel by default usually expects csv data to be latin1 — Tim Seguine
– Tim Seguine, Commented Dec 9, 2013 at 10:01
yes, I just tried opening it in Notepad++ and it shows space there, so does that mean the Â was a result of MS excel internal decoding? — rahul
– rahul, Commented Dec 9, 2013 at 10:06
Yes. It is normally windows codepage 1252. Before I answer, is this for your personal use or does it need to work for other people? — Tim Seguine
– Tim Seguine, Commented Dec 9, 2013 at 10:08
@Tim: Excel expects CSV data to be encoded in the currently configured codepage. That's a real pain. — Martijn Pieters
– Martijn Pieters, Commented Dec 9, 2013 at 10:09

Tim Seguine · Accepted Answer · 2013-12-09 10:26:13Z

2

Okay, so what is going on is that Excel likes to assume that you are using the currently configured codepage. You have a couple of options:

Write your data in that codepage. This requires however that you know which one your users will be using.
Load the csv file using the "import data" menu option. If you are relying on your users to do this, don't. Most people will not be willing to do this.
Use a different program that will accept unicode in csv by default, such as Libre Office.
Add a BOM to the beginning of the file to get Excel to recognise utf-8. This may break in other programs.

Since this is for your personal use, if you are only ever going to use Excel, then appending a byte order marker to the beginning is probably the easiest solution.

edited Dec 9, 2013 at 10:26

answered Dec 9, 2013 at 10:13

Tim Seguine

2,91528 silver badges39 bronze badges

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

Comments

Mark Tolonen · Accepted Answer · 2013-12-09 10:13:54Z

1

Microsoft likes byte-order marks in its text files. Even though a BOM doesn't make sense with UTF-8, it is used as a signature to let Excel know the file is encoded in UTF-8.

Make sure to generate your .csv as UTF-8 with BOM. I created the following using Notepad++:

English,Chinese
American,美国人
Chinese,中国人

The result saved with BOM:

Result with BOM

The result without BOM:

Result without BOM

answered Dec 9, 2013 at 10:13

Mark Tolonen

181k26 gold badges182 silver badges278 bronze badges

4 Comments

Tim Seguine Over a year ago

The problem with that is that a lot of (other) programs will choke on the BOM

Martijn Pieters Over a year ago

@Tim: Not so much choke; the BOM is a valid zero-width space character. They may not particularly like that character if you are doing string matching against the first column without stripping.

Tim Seguine Over a year ago

@MartijnPieters Valid or not, I have seen a lot of programs that don't handle them correctly at all. Sometimes you even get them displayed erroneously as glyphs. Often these programs have mostly correct handling of utf-16 with a BOM or utf8 without a BOM. I would prefer bug free support everywhere obviously, but the last I checked (about a year ago admittedly) it still seems to be a common issue, and worth mentioning.

Martijn Pieters Over a year ago

I haven't personally come across such programs, but I am not a fan of the way Microsoft has adopted the ZERO WIDTH NO-BREAK SPACE for a UTF-8 BOM, which needs no byte order marker at all, either.

Collectives™ on Stack Overflow

Writing unicode data in python

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related