Nicole Marie Nicole Marie - 1 year ago 103
Python Question

Split CSV file using Python shows not all data in Excel

I am trying to dump the values in my Django database to a csv, then write the contents of the csv to an Excel spreadsheet which looks like a table (one value per cell), so that my users can export a spreadsheet of all records in the database from Django admin. Right now when I export the file, I get this (only one random value out of many and not formatted correctly):

enter image description here

What am I doing wrong? Not sure if I am using list comprehensions wrong, reading the file incorrectly, or if there is something wrong with my

loop. Please help!

def dump_table_to_csv(db_table, io):
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM %s" % db_table, [])
row = cursor.fetchall()
writer = csv.writer(io)
writer.writerow([i[0] for i in cursor.description])

with open('/Users/nicoletorek/emarshal/myfile.csv', 'w') as f:
dump_table_to_csv(Attorney._meta.db_table, f)

with open('/Users/nicoletorek/emarshal/myfile.csv', 'r') as f:
db_list =
split_db_list = db_list.split(',')

output = BytesIO()
workbook = xlsxwriter.Workbook(output)
worksheet_s = workbook.add_worksheet("Summary")

header = workbook.add_format({
'bg_color': '#F7F7F7',
'color': 'black',
'align': 'center',
'valign': 'top',
'border': 1

row = 0
col = 0

for x in split_db_list:
worksheet_s.write(row + 1, col + 1, x, header)

Answer Source

The immediate problem with your sample code, as Jean-Francois points out, is that you aren't incrementing your counters in the loop. Also you may also find it more readable to use xlsxwriter.write_row() instead of xlsxwriter.write(). At the moment a secondary complication is you aren't preserving row information when you read in your data from the CSV.

If your data looks like this:

row_data = [[r1c1, r1c2], [r2c1, r2c2], ... ]

You can then use:

for index, row in enumerate(row_data):
    worksheet_s.write_row(index, 0, row)

That said, I assume you are interested in the .xlsx because you want control over formatting. If the goal is to just to generate the .xlsx and there is no need for the intermediate .csv, why not just create the .xlsx file directly? This can be accomplished nicely in a view:

import io
from django.http import HttpResponse

def dump_attorneys_to_xlsx(request):

    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet = workbook.add_worksheet('Summary')

    attorneys = Attorney.objects.all().values()

    # Write header
    worksheet.write_row(0, 0, attorneys[0])

    # Write data
    for row_index, row_dict in enumerate(attorneys, start=1):
        worksheet.write_row(row_index, 0, row_dict.values())

    response = HttpResponse(, content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=summary.xlsx'

    return response