# Excel Merging and Transformation Pipeline

> Merges multiple Excel files selected via GUI, applies specific column transformations (filename extraction, pipe-delimited splitting, arithmetic calculation, renaming), and enriches data using a mapping file.

- Skill: `ecnu-icalk/excel-merging-and-transformation-pipeline` (Agent Skill)
- Install (CLI): `npx skillmds@latest add ecnu-icalk/excel-merging-and-transformation-pipeline`
- Raw SKILL.md: https://api.skillmd.com/api/skills/ecnu-icalk/excel-merging-and-transformation-pipeline/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: ECNU-ICALK (https://skillmd.com/u/ecnu-icalk)
- Updated: 2026-09-08
- Page: https://skillmd.com/skills/ecnu-icalk/excel-merging-and-transformation-pipeline

---


# Excel Merging and Transformation Pipeline

Merges multiple Excel files selected via GUI, applies specific column transformations (filename extraction, pipe-delimited splitting, arithmetic calculation, renaming), and enriches data using a mapping file.

## Prompt

# Role & Objective
Write a Python script using pandas to merge multiple Excel files and perform specific data transformations.

# Operational Rules & Constraints
1. **Input Method**: Use `tkinter.filedialog.askopenfilenames` for input files and `askopenfilename` for the mapping file.
2. **Date Extraction**: Extract the filename without extension from the file path and populate the 'Date' column.
3. **Project Extraction**: Create a 'Project' column by splitting the 'Name' column by "|" and selecting the third value (index 2).
4. **Calculation**: Create a 'Current' column as the difference between 'Debit' and 'Credit'.
5. **Renaming**: Rename 'Closing balance' to 'Cumulative'.
6. **Enrichment**: Perform a left join with the mapping file on 'Main account' to add the 'Name' column.
7. **Output**: Save the result to 'merged_data.xlsx' without index.

## Triggers

- merge excel files with transformations
- process erp data files
- extract project from name column
- join with staff gl mapping