Merge multiple Excel files using Python

type

status

date

slug

summary

Introduction

This article solves a common problem: how to efficiently merge multiple Excel files using Python.

Use cases: When you have multiple similarly formatted Excel files that need consolidation, manual merging is both time-consuming and error-prone. This Python solution is particularly useful for data analysts, business report processors, and anyone who needs to regularly handle multiple Excel files. Instead of opening each file individually to copy and paste, a simple Python script can automate the entire process.

Example scenarios: Perhaps your system limits data exports to one month at a time, but you need to combine monthly data into a single Excel file. Or maybe you've received separate reports from multiple departments that need consolidation for analysis, or customer survey responses stored in different files require unified processing, or even multiple CSV files containing product inventory information need integration into a master file.

Python Solution

For Python Users

Easily merge multiple Excel files with this Python code! 🚀 Using pandas and os, this script explores files in a specified folder, combining them into a clean merged_excel.xlsx file. Simply insert your folder path, run the code, and voila – streamlined data! 📊💻

In this code, we're using two powerful tools: pandas for data manipulation and os (a module for interacting with the operating system).

The append function is the key component. It traverses all Excel files in the specified folder ('path') and collects them into a DataFrame, which functions as a clean, organized table for our data.

Now for the exciting part: the final two lines! They utilize our append function to merge all Excel data from the specified folder into one consolidated file named merged_excel.xlsx.

For Non-Python Users

If you don't have Python installed on your computer, online Python platforms are an excellent alternative. Here are steps to merge Excel files using Deepnote, a popular online platform:

Using Deepnote to Merge Excel Files

Step 1: Create a Deepnote Account Visit the Deepnote website and register for a free account. You can sign up using Google, GitHub, or email.

Step 2: Create a New Project After logging in, click "New Project" to create a new project.

Step 3: Upload Excel Files In the left navigation bar, locate the "Files" section and click the upload button to upload all Excel files you want to merge to Deepnote.

Step 4: Create a New Notebook In your project, click the "+" button and select "Notebook" to create a new Python notebook.

Step 5: Install Required Libraries Deepnote typically has pandas pre-installed, but to ensure everything works correctly, run this in the first code cell:

Step 6: Write the Merge Code In a new code cell, copy and paste the following code:

Step 7: Run the Code Click the run button to the left of the code cell or use the Shift+Enter shortcut to execute the code.

Step 8: Download the Merged File After the code execution completes, a "merged_excel.xlsx" file will appear in the "Files" section on the left. Right-click this file and select "Download" to download the merged Excel file.