Untitled

#!/bin/bash

if [ -z "$1" ]; then
    echo "Please provide the path to the accounts.csv file."
    exit 1
fi

input="$1"
output="accounts_new.csv"

if [ ! -f "$input" ]; then
    echo "The file $input does not exist."
    exit 1
fi

# Initialize the email count array
declare -A email_count

# Step 1: Copy the header from the input to the output file without modification
head -n 1 "$input" > "$output"

# Step 2: First pass to count occurrences of email bases
while IFS=',' read -r line; do
    # Parse the line into fields, respecting quoted fields
    IFS=',' read -r id location name title email department <<< "$(echo "$line" | awk -F',' '{
        for (i=1; i<=NF; i++) {
            if ($i ~ /^\".*\"$/) $i = substr($i, 2, length($i)-2); # Remove quotes
            else gsub(/"/, "", $i); # Remove stray quotes
        }
        OFS=","; print
    }')"

    # Skip the header row
    if [[ "$id" == "id" ]]; then
        continue
    fi

    # Format name parts (first and last)
    name_parts=($name)
    first="${name_parts[0]}"
    last="${name_parts[@]:1}"

    # Capitalize first and last names
    first_formatted="${first^}"
    last_formatted="${last^}"

    # Create email base (first initial + last name)
    email_base="${first_formatted:0:1}${last_formatted}"
    email_base="${email_base,,}" # Convert to lowercase

    # Count occurrences of email base
    ((email_count["$email_base"]++))
done < <(tail -n +2 "$input") # Skip the header row

# Step 3: Second pass to write processed data
while IFS=',' read -r line; do
    # Parse the line into fields, respecting quoted fields
    IFS=',' read -r id location name title email department <<< "$(echo "$line" | awk -F',' '{
        for (i=1; i<=NF; i++) {
            if ($i ~ /^\".*\"$/) $i = substr($i, 2, length($i)-2); # Remove quotes
            else gsub(/"/, "", $i); # Remove stray quotes
        }
        OFS=","; print
    }')"

    # Skip the header row
    if [[ "$id" == "id" ]]; then
        continue
    fi

    # Format name parts (first and last)
    name_parts=($name)
    first="${name_parts[0]}"
    last="${name_parts[@]:1}"

    # Capitalize first and last names
    first_formatted="${first^}"
    last_formatted="${last^}"

    # Create email base (first initial + last name)
    email_base="${first_formatted:0:1}${last_formatted}"
    email_base="${email_base,,}" # Convert to lowercase

    # Generate a unique email
    if ((email_count["$email_base"] > 1)); then
        # Add location ID to email to make it unique
        full_email="${email_base}${location}@abc.com"
    else
        full_email="${email_base}@abc.com"
    fi

    # Re-add quotes to fields containing commas
    if [[ "$title" == *","* ]]; then
        title="\"$title\""
    fi
    if [[ "$department" == *","* ]]; then
        department="\"$department\""
    fi

    # Write the processed data to the output file
    echo "$id,$location,$first_formatted $last_formatted,$title,$full_email,$department" >> "$output"
done < <(tail -n +2 "$input") # Skip the header row

echo "New file created: $output"