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 risking alignment errors.

🚀 Agentic Solution

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

Key Features:

• Traceability: Adds a Source_File column so you know which row came from which employee.
• Smart Skip: Ignores temporary files (~$temp.xlsx) to prevent crashes.

⚔️ Phase 1: Commander (Quick Fix)

For quick ad-hoc merging.

Prompt:

“I have a folder ‘Reports’ with 20 Excel files of identical structure. Write a Python script to merge them all into Master.xlsx. Add a column ‘Source’ indicating the original filename.”

Result: A basic merge script.

🏗️ Phase 2: Architect (Permanent Tool)

For Regular Reporting.

Engineering Prompt:

**Role:** Python Data Engineer
**Task:** Create an "Excel Merge App" (GUI).
**Requirements:**
1.  **GUI:**
    *   Select Input Folder.
    *   Select Output Filename.
    *   "Merge" button & Progress Bar.
2.  **Logic:**
    *   Loop through `.xlsx` files using `glob`.
    *   Use Pandas `.concat()`.
    *   **Feature:** Add filename as a new column `Source_File`.
    *   Handle empty files gracefully.
3.  **Deliverables:** `merge_app.py`, `run.bat` (Windows), `run.sh` (Mac).

🧠 Prompt Decoding

• Source_File Column: The crucial feature. Without this, after merging, you have no idea who sold what. Engineers focus on Data Integrity.

🛠️ Instructions

1. Copy Prompt -> Paste -> Run.
2. Select Folder -> Merge.