How to Append Multiple Excel Files together in python?

One of the most common tasks when working with large datasets is the need to combine data from multiple sources. This can be especially challenging when the data is stored in different Excel files, as you may need to manually copy and paste the data into a single file.

Fortunately, Python makes it easy to automate this process using the openpyxl library, which allows you to read and write to Excel files. With just a few lines of code, you can combine multiple Excel files into a single file, saving you time and effort.

In this tutorial, we will walk you through the steps to append multiple Excel files together using Python. We will start by installing the openpyxl library, then we will open the Excel files and access their sheets. Next, we will iterate over the rows in each sheet and copy them to the end of the other sheet. Finally, we will save the combined workbook.

Let's get started!

Step 1: Install the openpyxl Library

The first step is to install the openpyxl library, which allows you to read and write to Excel files in Python. To install it, open a terminal or command prompt and run the following command:

pip install openpyxl

Step 2: Open the Excel Files

Next, you will need to open each Excel file that you want to append. You can do this using the openpyxl.load_workbook() function, which returns a Workbook object that you can use to access the contents of the file. For example:

import openpyxl
 
# Open the first Excel file
wb1 = openpyxl.load_workbook('file1.xlsx')
 
# Open the second Excel file
wb2 = openpyxl.load_workbook('file2.xlsx')
 

Step 3: Access the Sheets

Once you have opened the Excel files, you can access their sheets using the Workbook.worksheets attribute, which returns a list of Worksheet objects. For example:

# Get the first sheet in the first workbook
ws1 = wb1.worksheets[0]
 
# Get the first sheet in the second workbook
ws2 = wb2.worksheets[0]
 

Step 4: Append the Sheets

To append the sheets together, you can use a loop to iterate over the rows in each sheet and copy them to the end of the other sheet. For example:

# Iterate over the rows in the first sheet
for row in ws1.rows:
    # Append the row to the end of the second sheet
    ws2.append(row)
 

Step 5: Save the Combined Workbook

Finally, you can save the combined workbook using the Workbook.save() method. For example:

# Save the combined workbook
wb2.save('combined.xlsx')

Putting It All Together Here is the complete code that puts everything together:

import openpyxl
 
# Open the first Excel file
wb1 = openpyxl.load_workbook('file1.xlsx')
 
# Open the second Excel file
wb2 = openpyxl.load_workbook('file2.xlsx')
 
# Get the first sheet in the first workbook
ws1 = wb1.worksheets[0]
 
# Get the first sheet in the second workbook
ws2 = wb2.worksheets[0]
 
# Iterate over the rows in the first sheet
for row in ws1.rows:
    # Append the row to the end of the second sheet
    ws2.append(row)
 
# Save the combined workbook
wb2.save('combined.xlsx')