Allow use of utf-8-sig encoding for Excel-compatible CSV export

[felciano]

Exporting tables with Unicode values to CSV does not encode include the byte-order marker needed by Excel to recognize that the CSV file has Unicode in it. As a result, double-clicking on the exported CSV file will not show the correct charactersets in the cells with non-ASCII values. This is arguably an Excel limitation (see http://www.sqlsnippets.com/en/topic-13412.html) but given that tablib is presumably trying to make life easier for people dealing with Excel, this would be nice to fix.

I believe this can be addressed by using utf-8-sig instead of utf-8 as the encoding during export.

The following demonstrates the problem and solution using a different encoding.

def testCSVandBOM():
    # requires the UnicodeWriter and UnicodeReader classes (see Python csv module docs)
    val = 'Etel\xc3\xa4-Suomi, Finland'.decode('utf-8')
    print val

    # double-clicking this file to open in Excel decodes correctly
    with open('with-BOM.csv', 'wb') as f:
        w = UnicodeWriter(f, delimiter = ",", encoding = 'utf-8-sig' )
        w.writerow(['Someplace I want to visit',val])

    # double-clicking this file to open in Excel does NOT decode correctly
    with open('without-BOM.csv', 'wb') as f:
        w = UnicodeWriter(f, delimiter = ",", encoding = 'utf-8' )
        w.writerow(['Someplace I want to visit',val])

If compatibility with current CSV export behavior is a concern, maybe this could be added as a new export format?

[kennethreitz]

Thanks!

I think replacing the current export behavior with this is best.

[kennethreitz]

Any objections?

[kennethreitz]

Done.

[boatcoder]

What ever happened with this? I'm using tablib==3.2.0 and not finding anything with utf-8-sig and excel is still unable to show the emojis that are in the file correctly.

[claudep]

When you export with tablib in csv, you get an unicode result, so AFAIR it's your business to write it in a file with the proper encoding.

[boatcoder]

I'm using tablib via django-tables2 and this is what I had to do to get the encoding bytes into the file.

        if request.GET.get('_export', None):
            export = TableExport(export_format=request.GET['_export'], table=table)
            response = HttpResponse(content_type=export.content_type())
            filename=f"{self.export_name}.{request.GET['_export']}"
            response["Content-Disposition"] = f'attachment; filename="{filename}"'
            # These 3 bytes at the beginning of the file make excel happy with the emojis
            response.write(b'\xef\xbb\xbf')
            response.write(export.export())
            return response

[claudep]

You could write a single write line with response.write(export.export().encode('utf-8-sig'))