Python Forum

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 ?

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?

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)