1

Hi I have a simple view which returns a csv file of a queryset which is generated from a mysql db using utf-8 encoding:

def export_csv(request):
    ...
    response = HttpResponse(mimetype='text/csv')
    response['Content-Disposition'] = 'attachment; filename=search_results.csv'
    writer = csv.writer(response, dialect=csv.excel)             
    for item in query_set:
        writer.writerow(smart_str(item))  
    return response 
    return render(request, 'search_results.html', context)

This works fine as a CSV file, and can be opened in text editors, LibreOffice etc. without problem.

However, I need to supply a file which can be opened in MS Excel in Windows without errors. If I have strings with latin characters in the queryset such as 'Española' then the output in Excel is 'Española'.

I tried this blogpost but it didn't help. I also know abut the xlwt package, but I am curious if there is a way of correcting the output, using the CSV method I have at the moment.

Any help much appreciated.

Darwin Tech
  • 18,449
  • 38
  • 112
  • 187

3 Answers3

0

Looks like there is not a uniform solution for all version of Excel.

  1. Your best bet migth be to go with openpyxl, but this is rather complicated and requiers separate handling of downloads for excel users which is not optimal.

  2. Try adding byte order marks at the beginnign (0xEF, 0xBB, 0xBF) of file. See microsoft-excel-mangles-diacritics-in-csv-files

There is another similar post.

Community
  • 1
  • 1
Aleš Kotnik
  • 2,654
  • 20
  • 17
  • I tried a couple of different encodings. cp1250 as with the others yields the following error: `'ascii' codec can't decode byte 0xc3 in position 4: ordinal not in range(128)` – Darwin Tech May 27 '12 at 18:09
  • Could you render result (2 lines) to variable and post it here so I can try? – Aleš Kotnik May 27 '12 at 18:38
  • Here is a simplified ´.values_list´ of the first two lines: `[(u'Abutilon depauperatum', u'Espa\xf1ola'), (u'Abutilon depauperatum', u'Espa\xf1ola')]` – Darwin Tech May 27 '12 at 19:17
  • Which is your windows codepage cp1252? Export to utf-8 and use iconv: `iconv -f utf-8 -t cp1252 a.csv > b.csv` – Aleš Kotnik May 27 '12 at 19:49
  • Sorry, You will have to explain that a little more for me. – Darwin Tech May 27 '12 at 20:33
0

You might give python-unicodecsv a go. It replaces the python csv module which doesn't handle Unicode too gracefully.

  1. Put the unicodecsv folder somehwere you can import it or install via setup.py

  2. Import it into your view file, eg :

    import unicodecsv as csv
cyberdelia
  • 5,343
  • 1
  • 19
  • 16
ikalooby
  • 1
0

I found out there are 3 things to do for Excel to open unicode csv files properly:

  1. Use utf-16-le charset
  2. Insert utf-16 byte order mark to the beginning of exported file
  3. Use tabs instead of commas in csv

So, this should make it work in Python 3.7 and Django 2.2

import codecs
...

def export_csv(request):
    ...
    response = HttpResponse(content_type='text/csv', charset='utf-16-le')
    response['Content-Disposition'] = 'attachment; filename=search_results.csv'
    response.write(codecs.BOM_UTF16_LE)

    writer = csv.writer(response, dialect='excel-tab')
    for item in query_set:
        writer.writerow(smart_str(item))
    return response
jnmbk
  • 1