Home » Python

Copy data from one excel file to another in Python

Working with excel in Python: In this tutorial, we will learn how to copy data from one excel file to another in Python programming language? By Sapna Deraje Radhakrishna Last updated : December 21, 2023

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. (Learn: How to install pip?

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.

Copying the data from a source excel file to the destination file

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 program 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')