[Fixed]-Split CSV file using Python shows not all data in Excel

by

1👍

✅

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].keys())

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

    output.seek(0)

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

    return response
👤StvnW

0👍

Your CSV file could be read in and written as follows:

import csv

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

with open(r'\Users\nicoletorek\emarshal\myfile.csv', 'rb') as f_input:
    csv_input = csv.reader(f_input)

    for row_index, row_data in enumerate(csv_input):
        worksheet_s.write_row(row_index, 0, row_data)

workbook.close()

This uses the csv library to ensure the rows are correctly read in, and the write_row function to allow the whole row to be written using a single call. The enumerate() function is used to provide a running row_index value.

👤Martin Evans

Leave a comment