Merge Excel Files

😫 The Pain Point

You are a Sales Manager with 20 staff. Monthly, each sends a report (Sales_Staff1.xlsx, Sales_Staff2.xlsx…). You have to combine them into one Master file. Copy-pasting 20 times takes 60 minutes and risks alignment errors. Files may be locked or corrupted.

🚀 Agentic Solution

A Desktop App that aggregates files: Point it to a folder, and it combines everything with full error handling.

Key Features:

• Traceability: Adds a Source_File column so you know which row came from which employee.
• Smart Skip: Ignores temporary files (~$temp.xlsx) and handles locked/corrupt files gracefully.
• Encoding Support: Handles UTF-8 and other encodings automatically.

⚔️ Phase 1: Commander (Quick Fix)

For quick ad-hoc merging.

Prompt:

“I have a folder Sales_Reports containing multiple Excel files (.xlsx, .xls). Write a robust Python script using Pandas to:

1. Find Files: Scan for all .xlsx and .xls files (skip temp files starting with ~$).
2. Read Safely: Use try-except to handle corrupted or locked files gracefully (skip with warning).
3. Add Metadata: Add columns Source_File (filename) and Row_Index (original row number).
4. Merge: Concatenate all DataFrames, reset index.
5. Export: Save to Master_Report.xlsx with openpyxl engine.
6. Logging: Print progress (e.g., ‘Processing 1/50: sales_jan.xlsx’).

Handle encoding issues (UTF-8) and empty files (skip with warning).”

Result: A robust merge script that handles edge cases.

🏗️ Phase 2: Architect (Permanent Tool)

For Regular Reporting.

Engineering Prompt:

**Role:** Python GUI Developer (PyQt6 Specialist)
**Task:** Create "Excel Merge Master" Desktop App

**Objective:** A reliable desktop tool to consolidate hundreds of Excel files into a single master report with traceability.

**Tech Stack:**
* Language: Python 3.10+
* GUI Library: PyQt6 (Cross-platform)
* Data Engine: Pandas, openpyxl
* Packaging: PyInstaller

**Functional Requirements:**
1.  **UI Layout (PyQt6):**
    *   **Input:** "Browse Folder" button for source reports.
    *   **Settings:** Output Filename field, Dropdown for "Encoding" (Auto/UTF-8).
    *   **Status:** Label showing "Found X valid Excel files".
    *   **Action:** Big "Merge All" button.

2.  **Core Logic:**
    *   Scan folder for `.xlsx/.xls`, ignoring `~$` temp files.
    *   Add `Source_File` column to every row for traceability.
    *   Handle locked/corrupted files with try/except blocks (skip and log).
    *   **Threading:** File I/O operations in `QThread` to prevent freezing.

3.  **Deliverables:**
    *   `main.py`: Complete source code.
    *   `requirements.txt`: Dependencies.
    *   **Build Instructions:**
        *   Windows: `pyinstaller --onefile --noconsole main.py`
        *   macOS: `pyinstaller --windowed --noconsole main.py`

🧠 Prompt Decoding

• Source_File Column: The crucial feature. Without this, after merging, you have no idea who sold what. Engineers focus on Data Integrity.
• Try-Except: Essential for real-world use. Files may be open in Excel or corrupted.

🛠️ Instructions

1. Copy Prompt → Paste to AI → Run generated script.
2. Select Folder → Preview count → Merge.