Python: Merge All .xlsx Files
About
This script will merge the contents of all .xlsx files found within a folder and create a new one containing the output.
Raw Script
merge-excel-files.py
import pandas as pd
import os
# Get all excel files in the current directory
excel_files = [f for f in os.listdir() if f.endswith(".xlsx")]
# Create an empty list to store DataFrames
all_data = []
# Loop through each excel file
for file in excel_files:
# Read the excel file with pandas
df = pd.read_excel(file)
# Add a new column named 'Source_File' with the file name
df['Source_File'] = file
# Append the DataFrame to the list
all_data.append(df)
# Concatenate all DataFrames in the list
merged_df = pd.concat(all_data, ignore_index=True)
# Create a new excel file named 'merged_data.xlsx' and write the merged DataFrame
merged_df.to_excel('merged_data.xlsx', index=False)
print("Excel files merged successfully!")
Dependencies
pip install pandas
pip install openpyxl