csv-excel-merger

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)