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 5 · Answer 1 · edited May 23 '17 at 12:02

5

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

155,703
32
311
293

*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

shiya · Answer 2 · 2021-10-21T23:23:35.453

This is an example of python3, create StringIO, write \uFEFF and then create csv writer to render correctly in Excel 2019.

This method also solves the problem of the Chinese not being rendered correctly.

flask version=2.0.1

    import csv
    from io import StringIO
    
    from flask import Flask, Response
    
    app = Flask(__name__)
    
    
    @app.route('/example.csv')
    def example_csv():
        def generate():
            f = StringIO()
            f.seek(0)
            f.write(u'\uFEFF')
            writer = csv.writer(f)
            writer.writerow(('Header 1', 'Header 2', 'Header 3'))
            dataset = [['1', '2', '3'],
                       ['a', 'b', 'c'],
                       ['£', '€', '¥'],
                       ['壹', '貳', '參']]
            for row in dataset:
                writer.writerow(tuple(row))
                yield f.getvalue()
                f.seek(0)
                f.truncate(0)
    
        response = Response(generate(), mimetype='text/csv')
        response.headers.set("Content-Disposition",
                             "attachment",
                             filename='example.csv')
        return response
    
    
    if __name__ == '__main__':
        app.run(debug=True)

Excel render thumbnail

References:

Create and download a CSV file from a Flask view

Is it possible to force Excel recognize UTF-8 CSV files automatically?

score 1 · 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

score 0 · Answer 4 · 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

176
1
12

Proper rendering of Flask produced CSV files in Excel

4 Answers4