Copying data from an excel to another one using pandas-CodePudding

I have two files that contains data. The main file has some messy data. The second file already has formulas and it will arrange everything from the messy data. The problem is: when I'm trying to copy data from the main file, it deletes everything for the second file. I already have the sheet that I need in the second file, I only need to copy the messy data from main file in a specific range. So it should look like this: Main file copy data -> Insert into second file with the already sheet created but starting with Row 2 and without deleting anything else. I couldn't find any solution on this. I am currently at this one but it deletes everything:

df.to_excel(filename_out, sheet_name = sheet_name1, startrow = len(df) 1, header=False, index=False)

Searching google, found an incomplete solution and deletes everything.

CodePudding user response：

To copy data from one Excel file to another without deleting any existing data, you can use the openpyxl library in Python. This library allows you to read and write Excel files using Python.

The following utilizes openpyxl to copy data from a "main" file to a "second" file:

import openpyxl

# Open the main file and the second file
main_file = openpyxl.load_workbook("main_file.xlsx")
second_file = openpyxl.load_workbook("second_file.xlsx")

# Select the sheet in the main file that contains the data you want to copy
main_sheet = main_file["Sheet1"]

# Select the sheet in the second file where you want to insert the data
second_sheet = second_file["Sheet1"]

# Determine the starting row where you want to insert the data in the second file
start_row = 2

# Iterate through the rows of the main sheet
for row in main_sheet.iter_rows(min_row=1, max_row=main_sheet.max_row):
    # Iterate through the cells in the row
    for cell in row:
        # Insert the cell value into the second sheet at the specified row and column
        second_sheet.cell(row=start_row, column=cell.column).value = cell.value
    # Increment the starting row for the next row of data
    start_row  = 1

# Save the second file with the updated data
second_file.save("second_file.xlsx")

This code reads the data from the main file one row at a time, and then inserts each cell value into the second file at the specified starting row and column. The data in the second file is not deleted, and any existing data is retained.

CodePudding user response：

If you wanna start main row 2 try this => startrow=1