Home » Python

Copy data from one excel file to another in Python

Working with excel in Python: Here, we are going to learn how to copy data from one excel file to another in Python programming language?
Submitted by Sapna Deraje Radhakrishna, on October 03, 2019

Excel workbooks are a major source of data collections. Python programming language provides few libraries to perform operations on the excel workbooks, like copying the data from one workbook to another.

Note: The library mentioned below needs to be installed using pip in the virtual environment.

openpyxl

https://pypi.org/project/openpyxl/

Openpyxl is a python library to read/write Excel files (xlsx, xlsm, xltx, xltm). This library provides an option to read every cell of the workbook and either copies it or modify it by using openpyxl.worksheet.Worksheet.cell() method. This method allows accessing each cell by the row and column as a numerical value.

The below example, will demonstrate the process of copying the data from a source excel file to the destination file, by row/column.

Step 1: Consider a source workbook, say source.xlsx and destination workbook, say destination.xlsx. The latter file is empty to where the contents of the former file will be copied. Below is the example of the former file (source.xlsx).

Step 2: Load the workbooks

    from openpyxl import load_workbook

    src_wb = load_workbook('source.xlsx')
    dest_wb = load_workbook('destination.xlsx')

Step 3: Read the sheets to be copied

    src_sheet = src_wb.get_sheet_by_name('source_sheet')
    dest_sheet = dest_wb.get_sheet_by_name('destination')

Step 4: Copy all the rows and columns

for i in range(1, src_sheet.max_row+1):
    for j in range(1, src_sheet.max_column+1):
        dest_sheet.cell(row=i, column=j).value = src_sheet.cell(row=i, column=j).value

Step 5: Save the workbooks

    src_wb.save('source.xlsx')
    dest_wb.save('destination.xlsx')

The contents from the source is now copied and saved in the destination file.

Python code to Copy data from one excel file to another

from openpyxl import load_workbook

src_wb = load_workbook('source.xlsx')
dest_wb = load_workbook('destination.xlsx')

src_sheet = src_wb.get_sheet_by_name('source_sheet')
dest_sheet = dest_wb.get_sheet_by_name('destination')

for i in range(1, src_sheet.max_row+1):
    for j in range(1, src_sheet.max_column+1):
        dest_sheet.cell(row=i, column=j).value = src_sheet.cell(row=i, column=j).value

src_wb.save('source.xlsx')
dest_wb.save('destination.xlsx')