UnicodeEncodeError: 'ascii' codec can't encode character u'\xa3'

A very easy way around all the "'ascii' codec can't encode character…" issues with csvwriter is to instead use unicodecsv, a drop-in replacement for csvwriter.

Install unicodecsv with pip and then you can use it in the exact same way, eg:

import unicodecsv file = open('users.csv', 'w') w = unicodecsv.writer(file) for user in User.objects.all().values_list('first_name', 'last_name', 'email', 'last_login'): w.writerow(user) 

For what it's worth: I'm the author of xlrd.

Does xlrd produce unicode?
Option 1: Read the Unicode section at the bottom of the first screenful of xlrd doc: This module presents all text strings as Python unicode objects.
Option 2: print type(text), repr(text)

You say """If I rewrite this to x.encode('utf-8') it stops throwing an error, but unfortunately when I then write the data out somewhere else (as latin-1), the £ signs have all become garbled.""" Of course if you write UTF-8-encoded text to a device that's expecting latin1, it will be garbled. What do did you expect?

You say in your edit: """I get the same error even if I uncomment the Latin-1 line""". This is very unlikely -- much more likely is that you got a slightly different error (mentioning the latin1 codec instead of the ascii codec) in a different source line (the uncommented latin1 line instead of the writerow line). Reading error messages carefully aids understanding.

Your problem here is that in general your data is NOT encodable in latin1; very little real-world data is. Your POUND SIGN is encodable in latin1, but that's not all your non-ASCII data. The problematic character is U+2022 BULLET which is not encodable in latin1.

It would have helped you get a better answer sooner if you had mentioned up front that you were working on Mac OS X ... the usual suspect for a CSV-suitable encoding is cp1252 (Windows), not mac-roman.

x = x.decode("ISO-8859-1") UnicodeEncodeError: 'ascii' codec can't encode character u'\xa3' in position 0: ordinal not in range(128) 

Look closely: You got a Unicode***Encode***Error calling the decode method.

The reason for this is that decode is intended to convert from a byte sequence (str) to a unicode object. But, as John said, xlrd already uses Unicode strings, so x is already a unicode object.

In this situation, Python 2.x assumes that you meant to decode a str object, so it "helpfully" creates one for you. But in order to convert a unicode to a str, it needs an encoding, and chooses ASCII because it's the lowest common denominator of character encodings. Your code effectively gets interpreted as

x = x.encode('ascii').decode("ISO-8859-1") 

which fails because x contains a non-ASCII character.

Since x is already a unicode object, the decode is unnecessary. However, now you run into the problem that the Python 2.x csv module doesn't support Unicode. You have to convert your data to str objects.

for item in items: item = [x.encode('latin-1') for x in item] cleancsv.writerow(item) 

This would be correct, except that you have the • character (U+2022 BULLET) in your data, and Latin-1 can't represent it. There are several ways around this problem:

• Write x.encode('latin-1', 'ignore') to remove the bullet (or other non-Latin-1 characters).
• Write x.encode('latin-1', 'replace') to replace the bullet with a question mark.
• Replace the bullets with a Latin-1 character like * or ·.
• Use a character encoding that does contain all the characters you need.

These days, UTF-8 is widely supported, so there is little reason to use any other encoding for text files.

xlrd works with Unicode, so the string you get back is a Unicode string. The £-sign has code point U+00A3, so the representation of said string should be u'\xa3'. This has been read in correctly; it is the string that you should be working with throughout your program.

When you write this (abstract, Unicode) string somewhere, you need to choose an encoding. At that point, you should .encode it into that encoding, say latin-1.

>>> book = xlrd.open_workbook( "test.xls" ) >>> sh = book.sheet_by_index( 0 ) >>> x = sh.cell_value( 0, 0 ) >>> x u'\xa3' >>> print x £ # sample outputs (for e.g. writing to a file) >>> x.encode( "latin-1" ) '\xa3' >>> x.encode( "utf-8" ) '\xc2\xa3' # garbage, because x is already Unicode >>> x.decode( "ascii" ) Traceback (most recent call last): File "<stdin>", line 1, in <module> UnicodeEncodeError: 'ascii' codec can't encode character u'\xa3' in position 0: ordinal not in range(128) >>> 

Working with xlrd, I have in a line ...xl_data.find(str(cell_value))... which gives the error:"'ascii' codec can't encode character u'\xdf' in position 3: ordinal not in range(128)". All suggestions in the forums have been useless for my german words. But changing into: ...xl_data.find(cell.value)... gives no error. So, I suppose using strings as arguments in certain commands with xldr has specific encoding problems.