Inserting data from one Excel file to another using Python openpyxl

by

What will you learn?

In this tutorial, you will learn how to seamlessly transfer data between two Excel files using the powerful openpyxl library in Python.

Introduction to the Problem and Solution

When dealing with Excel files, the need often arises to migrate or duplicate data from one file to another. Our goal here is to move data records from a source Excel file to a destination Excel file. This task can be efficiently accomplished by leveraging the capabilities of the openpyxl library in Python. To tackle this challenge, we will extract data from the source Excel file and then insert it into the destination Excel file. This process entails opening both files, retrieving data from one, and inserting it into the other.

Code

import openpyxl

# Load both source and destination workbooks
source_wb = openpyxl.load_workbook('source.xlsx')
dest_wb = openpyxl.load_workbook('destination.xlsx')

# Select specific sheets from each workbook
source_sheet = source_wb['Sheet1']
dest_sheet = dest_wb['Sheet1']

# Iterate through rows in source sheet and append them to destination sheet
for row in source_sheet.iter_rows(values_only=True):
    dest_sheet.append(row)

# Save changes to destination workbook
dest_wb.save('destination.xlsx')

# Copyright PHD

Explanation

  • Import the openpyxl module for handling Excel files.
  • Load both the source and destination workbooks using load_workbook.
  • Choose specific sheets within each workbook containing relevant data.
  • Iterate through rows of the source sheet, extract values (values_only=True), and append them row by row into the destination sheet.
  • Save these modifications back into the destination workbook.
    How do I install openpyxl?

    You can install openpyxl via pip by executing:

    pip install openpyxl

# Copyright PHD

    Can I transfer specific columns instead of entire rows?

    Yes, you can adapt the provided code snippet to selectively choose columns while transferring data.

    Is there a limit on transferred data size?

    The amount of data that can be moved depends on system memory; optimizing large datasets may require batch processing techniques.

    Does this method retain formatting like colors or formulas?

    No, this basic method only copies cell values. Additional logic is required for retaining formatting.

    What if my files have multiple sheets or different structures?

    Handling multiple sheets or varied structures necessitates additional logic tailored to your Excel files’ complexity.

    Conclusion

    In conclusion, mastering openpyxyl enables effortless and efficient data transfer between two Excel files. Proficiency in these techniques equips you for similar tasks involving programmatically manipulating spreadsheet-like documents.

    Leave a Comment