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"))