Задать вопрос

Тел: +7 965 3737 888

597

Просмотров

6

Ответов

UnicodeWriter and UnicodeDictWriter - write unicode strings out to Excel compatible CSV files

Exporting unicode data to Excel in a CSV file is surprisingly difficult. After much experimentation, it turns out the magic combination is UTF-16, a byte order mark and tab-delimiters. This snippet provides two classes - UnicodeWriter and UnicodeDictWriter - which can be used to output Excel-compatible CSV.

Вопрос полезен? Да0/Нет0
file_3331.py(2.5Кб)
None

Ответы (6):

Ответkalvin_jones:17.02.2012
Ответ полезен? Да0/Нет0

by speciying the encoding as "utf-16-le" rather then "utf-16", and manually writing the BOM [0xff, 0xfe] before to the response before calling writerows(), this works for me on 2007 with sp3, no garbage characters.

the problem with the orig version is that the encode call to utf-16 prepends the BOM to each and every row, whhen it should only appear at the start of the file, hence the garbage characters.

Ответmsanders:13.07.2010
Ответ полезен? Да0/Нет0

jsoderba's solution works for me with Windows Office 2007

Ответjsoderba:16.06.2010
Ответ полезен? Да0/Нет0

Note that my solution would still produce an excess BOM if the file was reopened in a new UnicodeWriter object, so you might want to check if there's a BOM at the start of the file.

Something like (untested):

# read first two bytes in file
fpos = f.tell()
f.seek(0)
fstartbytes = f.read(2)
f.seek(fpos)

# Write BOM if needed
if fstartbytes != codecs.BOM_UTF16:
    f.write(codecs.BOM_UTF16)

Ответjsoderba:16.06.2010
Ответ полезен? Да0/Нет0

Writing the BOM by hand and stripping it from each row seems to work:

def __init__(self, f, dialect=csv.excel_tab, encoding="utf-16", **kwds):
    # Redirect output to a queue
    self.queue = StringIO.StringIO()
    self.writer = csv.writer(self.queue, dialect=dialect, **kwds)
    self.stream = f

    # Force BOM
    if encoding=="utf-16":
        import codecs
        f.write(codecs.BOM_UTF16)

    self.encoding = encoding

def writerow(self, row):
    # Modified from original: now using unicode(s) to deal with e.g. ints
    self.writer.writerow([unicode(s).encode("utf-8") for s in row])
    # Fetch UTF-8 output from the queue ...
    data = self.queue.getvalue()
    data = data.decode("utf-8")
    # ... and reencode it into the target encoding
    data = data.encode(self.encoding)

    # strip BOM
    if self.encoding == "utf-16":
        data = data[2:]

    # write to the target stream
    self.stream.write(data)
    # empty queue
    self.queue.truncate(0)

Ответmsanders:16.01.2009
Ответ полезен? Да0/Нет0

I have the same issue as stephendwolff. The first line of the CSV file is fine and then all subsequent lines are prefixed with a U+FEFF character (ZERO WIDTH NO-BREAK SPACE). This is with Python 2.5.2.

Ответstephendwolff:17.09.2008
Ответ полезен? Да0/Нет0

This all seemed to work very nicely for a mac when opening the CSV files with Excel (2004). But... on a PC, opening the CSV with either Office 2003 or 2000, some lovely square characters appear (ie control code). I had a look for mention of Unicode in the Office conversion dialogs (for csv to excel), but no mention. just a heap of different character sets from around the world.