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 Tool Developer
**Task:** Create an "Excel Merge Desktop App".

**Requirements:**
1.  **GUI (Tkinter):**
    *   **Input Section:** Browse button to select Input Folder.
    *   **Output Section:** Filename field (default: Merge_Result.xlsx).
    *   **Options:** Encoding dropdown (UTF-8, Latin-1, Auto-detect).
    *   **Preview:** Label showing "Found X files to merge".
    *   **Action:** Big green "Merge Files" button.
    *   **Feedback:** Progress Bar + Status Label ("Processing 1/50...").

2.  **Logic (Pandas):**
    *   Scan for .xlsx/.xls files (skip ~$* temp files).
    *   Try-except for each file (show error popup if locked).
    *   Add Source_File column.
    *   pd.concat and save with openpyxl engine.
    *   Show success popup with file count.

3.  **Deliverables:**
    *   `setup_gui.py`
    *   `run.bat` (auto-install pandas, openpyxl, xlrd)
    *   `run.sh`
    *   `requirements.txt`

🧠 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.