How do I write all csv records to Excel ?

[Revox]

Dear Pro !!

Can you Help me please with this issue ?

I wrote the following code to copy all csv to exist excel file
but It Copy just the last record and I can't understand why?

def emptyrow():

    # Load the Excel file and select the worksheet
    workbook = openpyxl.load_workbook('test.xlsx')
    worksheet = workbook.active

    # Find the last row with data in the worksheet
    last_row = worksheet.max_row
    print(last_row)

    # Load the CSV file and read the data
    with open('D:\Downloads\A (2).csv', 'r') as csv_file:
        csv_reader = csv.reader(csv_file)
        data = list(csv_reader)
        #print(data)        

    # Write the CSV data to the worksheet
    for row in data:
        for col, value in enumerate(row):
            worksheet.cell(row=last_row+1, column=col+1, value=value)

    # Save the changes to the Excel file
    workbook.save('test.xlsx')

emptyrow()

Please Advise ?

Larz60+ write Mar-23-2023, 11:47 AM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
Fixed for you this time. Please use BBCode tags on future posts.

[rob101]

Not fully tested, but from a quick look I can see that in the nested for loops (lines 18 to 20) the variable row here for row in data: is overwritten at line 20: worksheet.cell(row=last_row + 1, column=col + 1, value=value) (see the row=... part?).

You're going to want to correct that and see if it fixes your problem.

Sorry: scrub that; I'm reading it wrong.

I've just done a quick test of your code with a simple .csv file:

Output:
10,20,30,40,50

... and all five numbers have been appended to row 4 (which was the first empty row in my .xlsx file) , cols A to E. That said, the numbers have been appended as 'text', rather than integers.

A quick fix for that would be to have worksheet.cell(row=last_row + 1, column=col + 1, value=int(value))

Maybe the issue is with your .csv file?

[deanhystad]

This is your problem:

    for row in data:
        for col, value in enumerate(row):
            worksheet.cell(row=last_row+1, column=col+1, value=value)

You set the row to last_row+1 and last_row never changes.

You could do this:

    for row in data:
        last_row += 1
        for col, value in enumerate(row, start=1):
            worksheet.cell(row=last_row, column=col, value=value)

or this:

    for r, row in enumerate(data, start=last_row+1):
        for c, value in enumerate(row, start=1):
            worksheet.cell(row=r, column=c, value=value)