csv-excel-merger

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.

CSV/Excel Merger

Intelligently merge multiple CSV or Excel files with automatic column matching and data deduplication.

Instructions

When a user needs to merge CSV or Excel files:

1. Analyze Input Files:

   • How many files need to be merged?
   • What format (CSV, Excel, TSV)?
   • Are the files provided or need to be read from disk?
   • Do columns have the same names across files?
   • What is the primary key (unique identifier)?

2. Inspect File Structures:

   • Read headers from each file
   • Identify column names and data types
   • Detect encoding (UTF-8, Latin-1, etc.)
   • Check for missing columns
   • Look for duplicate column names

3. Create Merge Strategy:

   Column Matching:

   • Exact name match: "email" = "email"
   • Case-insensitive: "Email" = "email"
   • Fuzzy match: "E-mail" ≈ "email"
   • Common patterns:
     • "first_name", "firstname", "First Name" → "first_name"
     • "phone", "phone_number", "tel" → "phone"
     • "email", "e-mail", "email_address" → "email"

   Conflict Resolution (when same record appears in multiple files):

   • Keep first: Use value from first file
   • Keep last: Use value from last file (most recent)
   • Keep longest: Use most complete value
   • Manual review: Flag conflicts for user review
   • Merge: Combine non-conflicting fields

   Deduplication:

   • Identify duplicate rows based on primary key
   • Options: keep first, keep last, keep all, merge values
   • Track source file for each row

4. Perform Merge:

   # Example merge logic
   import pandas as pd
   
   # Read files
   df1 = pd.read_csv('file1.csv')
   df2 = pd.read_csv('file2.csv')
   
   # Normalize column names
   df1.columns = df1.columns.str.lower().str.strip()
   df2.columns = df2.columns.str.lower().str.strip()
   
   # Map similar columns
   column_mapping = {
       'firstname': 'first_name',
       'e_mail': 'email',
       # ...
   }
   df2 = df2.rename(columns=column_mapping)
   
   # Merge
   merged = pd.concat([df1, df2], ignore_index=True)
   
   # Deduplicate
   merged = merged.drop_duplicates(subset=['email'], keep='last')
   
   # Save
   merged.to_csv('merged_output.csv', index=False)
   

5. Format Output:

   📊 CSV/EXCEL MERGER REPORT
   
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   📁 INPUT FILES
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   
   File 1: contacts_jan.csv
     Rows: 1,245
     Columns: 8 (name, email, phone, company, ...)
   
   File 2: contacts_feb.csv
     Rows: 987
     Columns: 9 (firstname, lastname, email, mobile, ...)
   
   File 3: leads_export.xlsx
     Rows: 2,103
     Columns: 12 (full_name, email_address, phone, ...)
   
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   🔄 COLUMN MAPPING
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   
   Unified Schema:
   • first_name ← [firstname, first name, fname]
   • last_name ← [lastname, last name, lname]
   • email ← [email, e-mail, email_address]
   • phone ← [phone, mobile, phone_number, tel]
   • company ← [company, organization, org]
   • title ← [title, job_title, position]
   • source ← [file origin tracking]
   
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   🔍 MERGE ANALYSIS
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   
   Total rows before merge: 4,335
   Duplicate records found: 892
   Conflicts detected: 47
   
   Deduplication Strategy: Keep most recent (by source file date)
   Primary Key: email
   
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   ⚠️ CONFLICTS
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   
   Record: [e-mail]
     File 1 phone: [phone]
     File 2 phone: [phone]
     Resolution: Kept most recent (File 2)
   
   [List top 10 conflicts]
   
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   ✅ MERGE RESULTS
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   
   Output File: merged_contacts.csv
   Total Rows: 3,443
   Columns: 7
   Duplicates Removed: 892
   
   Breakdown by Source:
   • contacts_jan.csv: 1,245 rows (398 unique)
   • contacts_feb.csv: 987 rows (521 unique)
   • leads_export.xlsx: 2,103 rows (2,524 unique)
   
   Data Quality:
   • Email completeness: 98.2%
   • Phone completeness: 87.5%
   • Company completeness: 91.3%
   
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   💡 RECOMMENDATIONS
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   
   • Review 47 conflict records manually
   • Standardize phone number format
   • Fill missing company names (8.7% incomplete)
   • Export conflicts to: conflicts_review.csv
   

6. Handle Special Cases:

   Multiple Primary Keys:

   • Use compound keys: (email + company)
   • Offer options when ambiguous

   Different Data Types:

   • Convert dates to standard format
   • Normalize phone numbers
   • Standardize country codes
   • Clean whitespace and casing

   Missing Columns:

   • Fill with empty values
   • Flag missing data
   • Offer to create new columns

   Large Files:

   • Use chunking for files > 100MB
   • Show progress indicator
   • Estimate memory usage

7. Generate Code: Provide Python/pandas script that:

   • Reads all files
   • Performs intelligent column matching
   • Deduplicates based on strategy
   • Resolves conflicts
   • Saves merged output
   • Generates detailed report

8. Export Options:

   • CSV (UTF-8)
   • Excel (.xlsx)
   • JSON
   • SQL INSERT statements
   • Parquet (for large datasets)

Example Triggers

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

Best Practices

Column Matching:

• Use fuzzy matching for similar names
• Maintain original column name mapping report
• Allow manual override of auto-matching

Data Quality:

• Trim whitespace
• Standardize formats (phone, email, dates)
• Detect and flag invalid data
• Preserve data types

Performance:

• Use chunking for large files
• Process in batches
• Show progress for long operations
• Optimize memory usage

Transparency:

• Log all merge decisions
• Track source file for each row
• Report conflicts and resolutions
• Generate detailed merge report

Output Quality

Ensure merges:

• Intelligently match columns
• Handle different schemas
• Deduplicate properly
• Preserve data integrity
• Flag conflicts for review
• Generate comprehensive report
• Maintain data quality
• Track data lineage (source)
• Handle edge cases gracefully
• Provide validation statistics

Generate clean, deduplicated merged files with full transparency and data quality checks.