5 Best Ways to Merge All Excel Files in a Folder Using Python

by

💡 Problem Formulation: You have a folder filled with numerous Excel files containing different pieces of data but structured in a similar way. Your goal is to combine all these files into a single Excel file, incorporating all the data into one place for easier manipulation and analysis. For instance, you might have a directory with monthly sales reports and you want one comprehensive annual report as your output.

Method 1: Using pandas.concat

This method entails using the pandas library to read each Excel file into a DataFrame and then concatenate all DataFrames into one. The pandas.concat() function is efficient for combining multiple DataFrames with similar structures. This is a standard approach and requires that you have the pandas library installed.

Here’s an example:

import pandas as pd
import os

folder_path = 'path_to_excel_files'
files = [f for f in os.listdir(folder_path) if f.endswith('.xlsx')]

all_data = pd.DataFrame()
for file in files:
    df = pd.read_excel(os.path.join(folder_path, file))
    all_data = pd.concat([all_data, df])

all_data.to_excel('merged_file.xlsx', index=False)

The output will be a single Excel file named “merged_file.xlsx” containing the merged data from all the Excel files in the specified folder.

This code snippet starts by importing the necessary libraries, identifies Excel files in the given folder, and iterates over these files to read them into individual DataFrames. These DataFrames are then concatenated into one master DataFrame, which is finally written out to a new Excel file.

Method 2: Using ExcelWriter for Multiple Sheets

If the Excel files to be merged have different sheets that are also to be combined, the pandas.ExcelWriter() function can be utilized. This method is especially useful when dealing with Excel files that contain more than one sheet and you want to maintain the structure of each sheet in the merged file.

Here’s an example:

import pandas as pd
import os

folder_path = 'path_to_excel_files'
files = [f for f in os.listdir(folder_path) if f.endswith('.xlsx')]
writer = pd.ExcelWriter('merged_multisheet.xlsx')

for file in files:
    excel_file = pd.ExcelFile(os.path.join(folder_path, file))
    for sheet_name in excel_file.sheet_names:
        df = excel_file.parse(sheet_name)
        df.to_excel(writer, sheet_name=file.strip('.xlsx') + '_' + sheet_name, index=False)

writer.save()

The output will be a single Excel file named “merged_multisheet.xlsx” wherein data from different sheets are maintained separately, named accordingly to their original file and sheet names.

This snippet leverages ExcelFile and ExcelWriter from pandas to read in each sheet from every file and write them to different sheets within a new Excel workbook, preserving the original sheet names with a prefix based on the original file name.

Method 3: Using openpyxl for Large Files

The openpyxl library is an effective alternative for dealing with very large Excel files. Instead of loading entire DataFrames into memory, this method works directly with the Excel file, making it memory efficient. This may require additional coding effort but can be necessary for large datasets.

Here’s an example:

from openpyxl import load_workbook, Workbook
import os

folder_path = 'path_to_excel_files'
files = [f for f in os.listdir(folder_path) if f.endswith('.xlsx')]
wb_output = Workbook()
ws_output = wb_output.active

for file in files:
    wb = load_workbook(os.path.join(folder_path, file))
    ws = wb.active
    for row in ws.iter_rows(values_only=True):
        ws_output.append(row)

wb_output.save("merged_large.xlsx")

The output is a new Excel file “merged_large.xlsx” with all the rows from the source files combined in one worksheet.

This code utilizes the openpyxl library to iterate through each file and row, appending them to the new workbook’s active worksheet. This method is more memory efficient because it does not read the entire file into memory at once.

Method 4: Using glob and pandas.read_excel

By using the glob module along with pandas, we can streamline the file retrieval process and read them into pandas DataFrames. The glob module allows for pattern matching on file names, which can be especially handy when working with a large number of files in a directory.

Here’s an example:

import pandas as pd
from glob import glob

excel_files = glob('path_to_excel_files/*.xlsx')
all_data = pd.concat([pd.read_excel(file) for file in excel_files], ignore_index=True)

all_data.to_excel('merged_glob.xlsx', index=False)

The output of this snippet is a single Excel file “merged_glob.xlsx” that concatenates all the data from the matched Excel files.

This code uses glob.glob() to generate a list of all Excel file paths, which are then read and concatenated into a single DataFrame with pandas.concat(). The resulting DataFrame is then saved to a new Excel file.

Bonus One-Liner Method 5: Quick Dataframe Concatenation

For a quick and dirty one-liner approach, you can combine the Power of Unix command line (find, xargs) with pandas inside a Python one-liner. This method is for advanced users who are comfortable with command line interfaces.

Here’s an example:

import pandas as pd; pd.concat([pd.read_excel(file) for file in input().split()]).to_excel('merged_oneliner.xlsx', index=False)

Input: You would need to pass the list of files as input, potentially by using $(find path_to_excel_files -name "*.xlsx" | xargs).

Running the above Python one-liner right after outputting the find command will generate a merged Excel file “merged_oneliner.xlsx”.

This condenses the file finding and DataFrame concatenation into one line to be used in an environment where Python can receive input through a pipe or direct input redirection.

Summary/Discussion

  • Method 1: Using pandas.concat. Standard approach suitable for files with single sheets. Requires pandas. Good with small to medium-sized data. May be memory-intensive with large files.
  • Method 2: Using ExcelWriter for Multiple Sheets. Ideal for preserving multi-sheet structure. Requires pandas. It is a bit more complex but yields a neatly organized output.
  • Method 3: Using openpyxl for Large Files. Best for large or memory-demanding datasets. Less memory usage, but requires more code and may be slower than pandas for smaller files.
  • Method 4: Using glob and pandas.read_excel. Provides easy file pattern matching and is suitable for simple merges where no additional operations are needed.
  • Bonus One-Liner Method 5. Best for command-line enthusiasts and for quick scripts. Not ideal for complex merging or those uncomfortable with Unix commands.