Proper rendering of Flask produced CSV files in Excel

Question

Here is a minimal example of a flask view, which produces a CSV (python 2.7).

# -*- coding: utf-8 -*-

import csv
import StringIO

from flask import Flask, Response

app = Flask(__name__)


@app.route('/example.csv')
def example_csv():
    f = StringIO.StringIO()
    writer = csv.writer(f)
    writer.writerow(['Header 1', 'Header 2', 'Header 3'])
    writer.writerows([unicode(_).encode('utf-8') for _ in row] for row in (['1', '2', '3'], 
                                                                           ['a', 'b', 'c'],
                                                                           [u'£', u'€', u'¥']))
    response = Response(f.getvalue(), mimetype='text/csv')
    response.headers['Content-Disposition'] = u'attachment; filename=example.csv'
    return response


if __name__ == '__main__':
     app.run(debug=True)

Opening in Excel gives:

Header 1    Header 2    Header 3
1   2   3
a   b   c
Â£  â‚¬ Â¥

With Apple's Numbers App, the last line renders properly. How can I get Excel to render properly? Am I missing some sort of encoding setting?

Looking at the headers coming back when you request this endpoint, is the `Content-Type` set to `text/csv; charset=utf-8`? — Sean Vieira, May 04 '16 at 03:03
Yes, ➜ ~ curl -v http://127.0.0.1:5000/example.csv * Trying 127.0.0.1... * Connected to 127.0.0.1 (127.0.0.1) port 5000 (#0) > GET /example.csv HTTP/1.1 > Host: 127.0.0.1:5000 > User-Agent: curl/7.43.0 > Accept: */* > * HTTP 1.0, assume close after body < HTTP/1.0 200 OK < Content-Type: text/csv; charset=utf-8 < Content-Length: 53 < Content-Disposition: attachment; filename=example.csv < Server: Werkzeug/0.10.4 Python/2.7.9 < Date: Wed, 04 May 2016 08:21:54 GMT < Header 1,Header 2,Header 3 1,2,3 a,b,c £,€,¥ * Closing connection 0 — user964375, May 04 '16 at 08:22

score 4 · Answer 1 · edited May 23 '17 at 12:02

4

Is it possible to force Excel recognize UTF-8 CSV files automatically? suggests that the issue is that Excel does not consider the file to be UTF-8 unless it starts with a Byte Order Mark (for reasons of backwards compatibility with its previous behavior).

Try adding an encoded BOM as the first three bytes of your response:

response = Response(u'\uFEFF'.encode('utf-8') + f.getvalue(), mimetype='text/csv')

Alternatively, the first answer suggests that if that doesn't work, shipping the contents as UTF-16 will often work (but not for every version of Excel, even then).

edited May 23 '17 at 12:02

Community

1
1

answered May 04 '16 at 12:15

Sean Vieira

140,251
31
286
277

*Laughs* Good catch! Fixed. – Sean Vieira May 04 '16 at 12:20
The BOM added gives: ï»¿Header 1 Header 2 Header 3 1 2 3 a b c Â£ â‚¬ Â¥ How would sending as utf-16 be done? – user964375 May 05 '16 at 08:02
Just add a `charset='utf-16'` keyword argument to your `Response` call and change `encode('utf-8')` to `encode('utf-16')` – Sean Vieira May 06 '16 at 14:49
Response doesn't take a charset argument. Changing encode() to utf-16 gives: Header 1 Header 2 Header 3 ÿþ1 ÿþ2 ÿþ3 ÿþa ÿþb ÿþc ÿþ£ ÿþ& ÿþ¥ – user964375 May 09 '16 at 13:30

score 0 · Answer 2 · answered Mar 08 '20 at 22:40

0

Try to use utf-8 with signature, so replacing "utf-8" with "utf-8-sig" should work.

In your code: unicode(_).encode('utf-8-sig')

answered Mar 08 '20 at 22:40

Mahyar Zarifkar

96
1
7

score 0 · Answer 3 · answered Dec 01 '20 at 11:58

This works for me:

import codecs
from flask import make_response

output = make_response(
    codecs.BOM_UTF8.decode("utf8") + codecs.BOM_UTF8.decode() + f.getvalue())
output.headers["Content-Disposition"] = "attachment; filename=export.csv"
output.headers["Content-type"] = "text/csv"
return output

Proper rendering of Flask produced CSV files in Excel

3 Answers3