csv-excel-merger · diff
git:20260605.5afd849 to git:20260923.537406a
78 added, 54 removed. Audit A to A.
---
name: csv-excel-merger
- description: Merge multiple CSV/Excel files with intelligent column matching, data deduplication, and conflict resolution. Handles different schemas, formats, and combines data sources. Use when users need to merge spreadsheets, combine data exports, or consolidate multiple files into one.
+ description: Merges, appends, joins, and deduplicates multiple CSV, TSV, and Excel files (including multi-sheet workbooks) with pandas - mapping mismatched column names to one schema, normalizing keys, resolving conflicting values, tracking which file each row came from, and verifying the row math. Use when the user wants to combine spreadsheets or exports, stack monthly files, consolidate contact or lead lists, VLOOKUP-style join two sheets on an ID or email, or dedupe records across sources, even if they only say "put these together" or "clean up these lists into one".
---
# CSV/Excel Merger
- Merge multiple CSV or Excel files with automatic column matching, deduplication, and conflict resolution.
+ Combine tabular files into one clean output without silently losing, duplicating, or corrupting rows.
- ## Contents
+ ## Workflow
- - [Workflow](#workflow) — the step-by-step merge process
- - [Verification](#verification) — confirm the merge before handing it back
- - [Special cases](#special-cases) — encoding, compound keys, large files
- - [Guidelines](#guidelines) — quality and transparency standards
- - [Example triggers](#example-triggers)
- - `references/merge_strategies.md` — column matching, conflict resolution, and dedup options
- - `references/output_template.md` — the merge-report format
+ Copy this checklist and track progress:
- ## Workflow
+ ```
+ - [ ] 1. Profile inputs
+ - [ ] 2. Choose append vs join
+ - [ ] 3. Map columns and normalize keys
+ - [ ] 4. Merge and resolve conflicts
+ - [ ] 5. Verify row math
+ - [ ] 6. Write output and report
+ ```
- 1. **Inspect the inputs.** Determine file count, format (CSV / Excel / TSV), and whether the files are attached or read from disk. Read each header; identify column names, data types, and encoding (UTF-8, Latin-1). Note the candidate primary key.
+ 1. **Profile inputs.** Run the bundled profiler first; it reports encoding, delimiter, rows, headers, candidate keys, and header overlap without changing anything:
- 2. **Plan the merge.** Match columns across files to one unified schema, choose a conflict-resolution rule, and pick a deduplication strategy. See `references/merge_strategies.md` for the matching heuristics and the full set of options.
+ ```bash
+ python scripts/profile_inputs.py file1.csv file2.xlsx
+ ```
- 3. **Execute the merge** with pandas:
+ Excel files are profiled per sheet. Confirm with the user which sheets count if a workbook has more than one.
+ 2. **Choose the operation.** This is the decision that most often goes wrong:
+ - **Append (stack)** - same kind of records from different sources or periods (Jan + Feb exports, three lead lists). Use `pd.concat`, then dedupe.
+ - **Join (enrich)** - different facts about the same entities (contacts + their deal values). Use `pd.merge` on a key.
+ - Unsure? If the files share most columns, append. If they share only an ID column, join.
+
+ 3. **Map columns and normalize keys.** Build an explicit `{original: unified}` rename map per file (see [references/merge_strategies.md](references/merge_strategies.md) for common variants) and show it to the user when any match is fuzzy. Normalize key columns before dedupe or join: strip whitespace, lowercase emails, strip non-digits from phones, unify date formats. Without this, `A@x.com ` and `a@x.com` survive as two people.
+
+ 4. **Merge.** Read every file with `dtype=str` so IDs, ZIP codes, and phone numbers keep leading zeros, then convert specific columns afterward.
+
```python
import pandas as pd
- df1 = pd.read_csv("file1.csv")
- df2 = pd.read_csv("file2.csv")
-
- # Normalize, then map column names onto the unified schema
- for df in (df1, df2):
- df.columns = df.columns.str.lower().str.strip()
- df2 = df2.rename(columns={"firstname": "first_name", "e_mail": "email"})
+ frames = []
+ for path, rename in [("jan.csv", {"E-mail": "email"}), ("feb.xlsx", {"Email Address": "email"})]:
+ df = (pd.read_excel(path, dtype=str) if path.endswith((".xlsx", ".xls"))
+ else pd.read_csv(path, dtype=str, encoding="utf-8-sig", # use the profiler's encoding
+ keep_default_na=False))
+ df = df.rename(columns=rename)
+ df["email"] = df["email"].str.strip().str.lower()
+ df["source_file"] = path # lineage for every row
+ frames.append(df)
- merged = pd.concat([df1, df2], ignore_index=True)
- merged = merged.drop_duplicates(subset=["email"], keep="last")
- merged.to_csv("merged_output.csv", index=False)
+ combined = pd.concat(frames, ignore_index=True, sort=False)
+ # Blank keys are not duplicates of each other: set them aside before deduping.
+ has_key = combined["email"].fillna("") != ""
+ # Later files win: list the most recent source last, then keep="last".
+ deduped = combined[has_key].drop_duplicates(subset=["email"], keep="last")
+ no_key = combined[~has_key]
+ merged = pd.concat([deduped, no_key], ignore_index=True)
```
- 4. **Verify the result** before reporting — see [Verification](#verification).
-
- 5. **Report** using the layout in `references/output_template.md`, then offer export options: CSV (UTF-8), Excel (.xlsx), JSON, SQL INSERT statements, or Parquet for large datasets.
+ For a join, make pandas enforce the relationship you expect so a duplicate key raises instead of multiplying rows:
- ## Verification
+ ```python
+ out = pd.merge(contacts, deals, on="email", how="left",
+ validate="one_to_one", indicator=True)
+ unmatched = out[out["_merge"] == "left_only"]
+ ```
- Never hand back a merge without checking it. After merging, assert the row math holds and the key is actually unique:
+ Conflict strategies (keep first/last/most complete, combine fields, flag for review) are in [references/merge_strategies.md](references/merge_strategies.md).
- ```python
- total_in = len(df1) + len(df2)
- assert len(merged) > 0, "merge produced an empty frame"
- assert len(merged) <= total_in, "more rows than inputs — check the concat/join"
- assert merged["email"].is_unique, "duplicate keys remain after dedup"
+ 5. **Verify before reporting.** Never hand back a merge without checking it:
- print(f"in: {total_in} rows | out: {len(merged)} rows | removed: {total_in - len(merged)}")
- print(f"null keys: {merged['email'].isna().sum()} | columns: {list(merged.columns)}")
- ```
+ ```python
+ rows_in = sum(len(f) for f in frames)
+ assert len(merged) > 0, "merge produced an empty frame"
+ assert len(merged) <= rows_in, "more rows out than in: check the join keys"
+ assert deduped["email"].is_unique, "duplicate keys remain after dedupe"
+ print(f"in={rows_in} out={len(merged)} removed={rows_in - len(merged)} blank_keys={len(no_key)}")
+ print(merged["source_file"].value_counts())
+ ```
- Report rows in vs. out, duplicates removed, and per-column completeness so the user can sanity-check the numbers against their own expectations.
+ Spot-check three removed duplicates by hand against the source files; the asserts prove the math, not that the right row won.
- ## Special cases
+ 6. **Write output and report.** Use the layout in [references/output_template.md](references/output_template.md).
+ - CSV for Excel users: `to_csv(path, index=False, encoding="utf-8-sig")` (the BOM makes Excel read accents correctly).
+ - Excel: `to_excel(path, index=False)` with openpyxl installed. A sheet holds at most 1,048,576 rows; split or use CSV/Parquet beyond that.
+ - Also write `conflicts_review.csv` or `unmatched.csv` when those sets are non-empty.
- - **Compound keys** — when no single column is unique, key on a tuple: `subset=["email", "company"]`.
- - **Mixed data types** — standardize dates, phone numbers, and country codes; strip whitespace and normalize casing *before* deduping, or near-duplicates slip through.
- - **Missing columns** — fill absent columns with empty values and flag them in the report; never silently drop data.
- - **Large files (>100MB)** — read in chunks (`pd.read_csv(path, chunksize=...)`), report progress, and estimate memory before loading everything at once.
+ ## pandas version notes
- ## Guidelines
+ Current pandas is 3.x (Python 3.11+). Differences that affect merges:
+ - Text columns default to the `str` dtype, not `object`. Check `pd.api.types.is_string_dtype(col)` instead of `dtype == object`.
+ - Copy-on-Write is always on. Chained assignment such as `df[col][mask] = x` never updates `df` (pandas only warns); use `df.loc[mask, col] = x`.
+ - Parsed datetimes default to microsecond resolution. Call `.dt.as_unit("ns")` before casting to integers if something downstream expects nanoseconds.
+ - `pd.read_excel(..., engine="calamine")` (needs `python-calamine`) reads large workbooks much faster than openpyxl.
- - **Column matching** — prefer exact, then case-insensitive, then fuzzy. Always emit the original → unified mapping so every match is auditable, and allow manual override.
- - **Data quality** — trim whitespace, standardize formats, flag invalid values, preserve types.
- - **Transparency** — track the source file for every surviving row, log each merge decision, and report all conflicts with their resolutions.
- - **Performance** — chunk large files, process in batches, and show progress on long-running merges.
+ The code in this skill also runs on pandas 2.2.
- ## Example triggers
+ ## Failure modes to check for
- - "Merge these three CSV files"
- - "Combine multiple Excel sheets into one file"
- - "Deduplicate and merge customer data"
- - "Join spreadsheets with different column names"
- - "Consolidate contact lists from different sources"
+ - **Row explosion on join** - duplicate keys on both sides multiply rows. `validate=` catches it.
+ - **Leading zeros lost** - reading without `dtype=str` turns `01234` into `1234`.
+ - **Excel-mangled values** - long IDs already shown as `1.23E+15` or dates already reformatted in the source file cannot be recovered by pandas; flag them.
+ - **Header rows not on line 1** - exports with a title block need `skiprows=` or `header=`.
+ - **Mixed encodings** - one file in cp1252 among UTF-8 files shows up as `é` artifacts. The profiler reports the encoding per file.
+ - **Silent column drops** - a column present in only one file becomes mostly empty after append. Keep it and report its completeness; never drop data without saying so.
+ - **Large files** (over a few hundred MB) - read with `chunksize=` or use Polars/DuckDB, and dedupe with a key set instead of loading everything into memory.