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')
TOP Interview Coding Problems/Challenges