CODEWITHSUNDEEP
pythonexcelcsvflask
user964375
user964375

Reputation: 2421

Proper rendering of Flask produced CSV files in Excel

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?

Upvotes: 2

Views: 2407

Answers (4)

tsenghc
tsenghc

Reputation: 31

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?

Upvotes: 3

Yu-Lin Chen
Yu-Lin Chen

Reputation: 559

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

Upvotes: 1

Mahyar Zarifkar
Mahyar Zarifkar

Reputation: 196

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')

Upvotes: 0

Sean Vieira
Sean Vieira

Reputation: 159875

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).

Upvotes: 5

Related Questions