如何在指定文件夹及子文件夹中递归查找并合并同名.xlsx文件

Step 1: Install Required Libraries #

First, make sure you have the necessary tools installed. Run this in your terminal:

pip install pandas openpyxl

Step 2: Python Script to Handle the Task #

Here's a complete script that does two key things:

• Recursively scans your target folder and groups all .xlsx files by their filename
• Merges each group of duplicate-named files into a single Excel file

import os
import pandas as pd
from pathlib import Path

def find_and_merge_duplicate_xlsx(root_folder, output_folder):
    # Create output folder if it doesn't exist
    Path(output_folder).mkdir(parents=True, exist_ok=True)
    
    # Dictionary to hold filename -> list of file paths
    file_groups = {}
    
    # Recursively walk through all folders
    for dirpath, _, filenames in os.walk(root_folder):
        for filename in filenames:
            # Check if it's an Excel file
            if filename.lower().endswith('.xlsx'):
                file_path = os.path.join(dirpath, filename)
                # Add to the group using filename as key
                if filename not in file_groups:
                    file_groups[filename] = []
                file_groups[filename].append(file_path)
    
    # Process each group of files
    for filename, file_paths in file_groups.items():
        # Only process groups with multiple files (duplicate names)
        if len(file_paths) > 1:
            print(f"Merging {len(file_paths)} instances of {filename}...")
            merged_data = []
            
            for path in file_paths:
                try:
                    # Read the first sheet of each Excel file
                    df = pd.read_excel(path, engine='openpyxl')
                    merged_data.append(df)
                except Exception as e:
                    print(f"Failed to read {path}: {str(e)}")
                    continue
            
            # Combine all dataframes
            if merged_data:
                final_df = pd.concat(merged_data, ignore_index=True)
                # Save merged file to output folder
                output_path = os.path.join(output_folder, f"merged_{filename}")
                final_df.to_excel(output_path, index=False, engine='openpyxl')
                print(f"Saved merged file to {output_path}")
        else:
            # Optional: Single files can be copied to output if needed
            print(f"Only one instance of {filename} found, skipping merge.")

# --------------------------
# Customize these paths!
# --------------------------
TARGET_FOLDER = "/path/to/your/target/folder"  # Replace with your folder path
OUTPUT_FOLDER = "/path/to/your/output/folder"  # Replace with where you want merged files

# Run the function
find_and_merge_duplicate_xlsx(TARGET_FOLDER, OUTPUT_FOLDER)

How to Use This Script #

1. Replace TARGET_FOLDER with the path to the folder you want to scan (including subfolders)
2. Replace OUTPUT_FOLDER with the path where you want to save the merged files
3. Run the script—you'll see progress messages in the terminal

Notes & Customizations #

• Handling Multiple Sheets: If your Excel files have multiple sheets and you want to merge all of them (not just the first), you can modify the pd.read_excel part to loop through each sheet using pd.ExcelFile(path).sheet_names
• Data Formatting: If your files have different column structures, pd.concat will still work but might create NaN values for missing columns. You can add checks for column consistency if needed
• Error Handling: The script includes basic error handling for unreadable files, but you can expand this if you need to handle specific edge cases (like password-protected files)

内容的提问来源于stack exchange，提问作者Siddharth Chavan