csv-excel-merger

CSV/Excel Merger

Combine tabular files into one clean output without silently losing, duplicating, or corrupting rows.

Workflow

Copy this checklist and track progress:

- [ ] 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. Profile inputs. Run the bundled profiler first; it reports encoding, delimiter, rows, headers, candidate keys, and header overlap without changing anything:

   python scripts/profile_inputs.py file1.csv file2.xlsx
   

   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 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, [email protected] and [email protected] 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.

   import pandas as pd
   
   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)
   
   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)