How to Replace Values in CSV

Quick Answer: Replace CSV Values

Use sed 's/old_value/new_value/g' file.csv > updated.csv for simple replacements, or awk with column-specific logic for structured updates: awk -F"," '{if ($3=="old") $3="new"} 1' OFS="," file.csv. Always backup before in-place editing.

Quick Comparison: CSV Replacement Methods

Method	Syntax	Best For	Pros
sed	sed 's/old/new/g'	Global text replacement	Fast, simple patterns
awk	awk -F"," with conditions	Column-specific operations	Precise field control
sed -i	In-place editing	File modification	No temp files needed
Mapping file	Multiple replacements	Bulk updates	Reusable transformations
Conditional awk	Complex logic	Row-based decisions	Most flexible

Bottom line: Use sed for quick text replacements, awk for column-specific logic, and always test on a copy first before in-place editing.

---

Update or modify values in CSV files using sed, awk, and custom scripts. Learn replacing specific columns, values, and conditional replacements.

Method 1: Replace Values with sed

The simplest approach for text replacement. Fast and straightforward for global replacements across the file.

When to Use sed for Replacement

• Global text replacements across entire file
• Simple pattern matching (no column awareness)
• Case-sensitive replacements needed
• Speed is the priority

# Replace all occurrences
sed 's/old_value/new_value/g' file.csv > updated.csv

# Replace in specific column (simple strings)
sed 's/,old_value,/,new_value,/g' file.csv > updated.csv

Method 2: Replace in Specific Column with awk

For column-specific replacements. This is more precise than sed since it understands CSV structure.

When to Use awk for Column Replacement

• Updating specific columns only
• Conditional replacements based on other columns
• Precise field-based operations
• Maintaining CSV structure with OFS

# Replace in column 2 (CSV)
awk -F"," '{$2="new_value"; print}' OFS="," file.csv > updated.csv

# Conditional replace (replace if column 1 matches)
awk -F"," '$1=="match_value" {$2="new_value"} 1' OFS="," file.csv

Method 3: In-Place File Editing with Backup

Edit files directly without creating temporary files, while keeping a backup for safety.

When to Use In-Place Editing

• Modifying files permanently
• Batch updating multiple files
• When disk space is limited
• When you want automatic backups (.bak files)

Detailed Example

Test file (users.csv):

ID,Name,Status
1,John,Active
2,Jane,Inactive
3,Bob,Active

# Replace all "Inactive" with "Archived"
sed 's/Inactive/Archived/g' users.csv

# Replace in specific column (Status column)
awk -F"," '{if ($3=="Inactive") $3="Archived"} 1' OFS="," users.csv

Output:

ID,Name,Status
1,John,Active
2,Jane,Archived
3,Bob,Active

Practical Example: Batch CSV Update

#!/bin/bash

# File: update_csv.sh

input_file="$1"
output_file="$2"
old_value="$3"
new_value="$4"

if [ $# -lt 4 ]; then
  echo "Usage: $0 <input.csv> <output.csv> <old> <new>"
  exit 1
fi

if [ ! -f "$input_file" ]; then
  echo "ERROR: File not found: $input_file"
  exit 1
fi

# Use sed to replace and save to output
sed "s/$old_value/$new_value/g" "$input_file" > "$output_file"

# Count replacements
original_count=$(grep -c "$old_value" "$input_file" 2>/dev/null || echo 0)
echo "Replaced $original_count occurrences"
echo "Output: $output_file"

Usage:

$ chmod +x update_csv.sh
$ ./update_csv.sh users.csv users_updated.csv Inactive Archived
Replaced 1 occurrences
Output: users_updated.csv

Replace Specific Column Values

#!/bin/bash

# Replace values only in specific column

input="$1"
column="${2:-1}"
old="$3"
new="$4"

if [ $# -lt 4 ]; then
  echo "Usage: $0 <file.csv> <column> <old> <new>"
  exit 1
fi

# Use awk to replace in specific column
awk -F"," -v col="$column" -v old="$old" -v new="$new" \
  '{if ($col == old) $col = new} 1' OFS="," "$input"

Usage:

$ ./replace_column.sh users.csv 3 Inactive Archived > users.csv.new

Case-Insensitive Replace

# Case-insensitive sed replacement
sed 's/inactive/Archived/gi' file.csv > updated.csv

# Case-insensitive awk replacement
awk -F"," '{if (tolower($3)=="inactive") $3="Archived"} 1' OFS="," file.csv

Replace with Escaping Special Characters

#!/bin/bash

# Handle special characters in replacement

old="$1"
new="$2"
file="$3"

# Escape special sed characters
old_escaped=$(printf '%s\n' "$old" | sed -e 's/[\/&]/\\&/g')
new_escaped=$(printf '%s\n' "$new" | sed -e 's/[\/&]/\\&/g')

sed "s/$old_escaped/$new_escaped/g" "$file"

Replace Based on Another Column

#!/bin/bash

# Replace values based on condition in different column

input="$1"

# If Status is "Delete", replace Name with "REMOVED"
awk -F"," '{
  if ($3 == "Delete") {
    $2 = "REMOVED"
  }
  print $0
}' OFS="," "$input"

Test file:

1,John,Active
2,Jane,Delete
3,Bob,Active

Output:

1,John,Active
2,REMOVED,Delete
3,Bob,Active

Bulk CSV Updates

#!/bin/bash

# Apply multiple replacements from a mapping file

csv_file="$1"
mapping_file="$2"  # Format: old_value,new_value

if [ ! -f "$csv_file" ] || [ ! -f "$mapping_file" ]; then
  echo "Usage: $0 <file.csv> <mappings.csv>"
  exit 1
fi

output="$csv_file.updated"
cp "$csv_file" "$output"

# Apply each mapping
while IFS=',' read -r old new; do
  # Skip empty lines and comments
  [ -z "$old" ] && continue
  [[ "$old" =~ ^# ]] && continue

  # Escape for sed
  old_escaped=$(printf '%s\n' "$old" | sed -e 's/[\/&]/\\&/g')
  new_escaped=$(printf '%s\n' "$new" | sed -e 's/[\/&]/\\&/g')

  # Apply replacement
  sed -i "s/$old_escaped/$new_escaped/g" "$output"
done < "$mapping_file"

echo "Updated: $output"

Mapping file (replacements.csv):

Inactive,Archived
John,John Smith
USA,United States

Replace with Increment

#!/bin/bash

# Replace with incrementing values

input="$1"
pattern="$2"     # e.g., "ID_"
start="${3:-1}"

output="$input.new"
counter=$start

awk -v pat="$pattern" -v start="$start" '
BEGIN { counter = start }
{
  gsub(pat, pat counter)
  counter++
  print $0
}' "$input" > "$output"

echo "Created: $output"

Delete and Replace Rows

#!/bin/bash

# Remove rows matching pattern and replace with new data

input="$1"
pattern="$2"
new_row="$3"

# Remove matching rows and add new row
grep -v "$pattern" "$input" > "$input.tmp"
echo "$new_row" >> "$input.tmp"
mv "$input.tmp" "$input"

Conditional Replacement with awk

#!/bin/bash

# Complex conditional replacement

input="$1"

awk -F"," '{
  # Skip header
  if (NR == 1) { print; next }

  # Replace ID 2 with specific values
  if ($1 == 2) {
    $2 = "Jane Doe"
    $3 = "Premium"
  }

  # Double all numeric values in column 4
  if ($4 ~ /^[0-9]+$/) {
    $4 = $4 * 2
  }

  print $0
}' OFS="," "$input"

Backup Before Replacing

#!/bin/bash

# Safe replacement with backup

input="$1"
old="$2"
new="$3"

# Create backup
cp "$input" "$input.backup"

# Apply replacement
sed -i "s/$old/$new/g" "$input"

# Verify changes
changes=$(diff "$input.backup" "$input" | wc -l)
echo "Made $((changes/2)) changes"
echo "Backup saved: $input.backup"

Replace Across Multiple Files

#!/bin/bash

# Replace in all CSV files in directory

directory="${1:-.}"
old="$2"
new="$3"

if [ -z "$old" ] || [ -z "$new" ]; then
  echo "Usage: $0 [directory] <old> <new>"
  exit 1
fi

for file in "$directory"/*.csv; do
  if [ -f "$file" ]; then
    count=$(grep -c "$old" "$file" 2>/dev/null || echo 0)
    if [ "$count" -gt 0 ]; then
      sed -i "s/$old/$new/g" "$file"
      echo "Updated: $file ($count replacements)"
    fi
  fi
done

Verify Replacements

#!/bin/bash

# Verify replacement was successful

original="$1"
updated="$2"
old_value="$3"
new_value="$4"

old_count=$(grep -c "$old_value" "$original" 2>/dev/null || echo 0)
new_count=$(grep -c "$new_value" "$updated" 2>/dev/null || echo 0)

echo "Original file: $old_count occurrences of '$old_value'"
echo "Updated file: $new_count occurrences of '$new_value'"

if [ "$new_count" -ge "$old_count" ]; then
  echo "✓ Replacement verified"
else
  echo "✗ Verification failed"
fi

Common Mistakes

1. Not escaping special regex characters - use \ for ., *, etc.
2. CSV field separators - specify correct delimiter with -F
3. Forgetting OFS - output field separator needed to maintain CSV format
4. In-place editing without backup - always use -i.bak or create backup
5. Not quoting variables - fails with spaces in values

Performance Tips

• Use sed for simple text replacements (fastest)
• Use awk for column-specific operations
• Test on a copy first
• For very large files, use sed -i for in-place editing
• Use -i.bak to keep backup automatically

Quick Reference

Common CSV replacement patterns:

# Simple global replacement
sed 's/old/new/g' file.csv > updated.csv

# Column-specific replacement
awk -F"," '{if ($3=="old") $3="new"} 1' OFS="," file.csv

# In-place with backup
sed -i.bak 's/old/new/g' file.csv

# Case-insensitive replacement
sed 's/old/new/gi' file.csv

# Conditional replacement based on another column
awk -F"," '{if ($1=="match") $2="new"} 1' OFS="," file.csv

# Multiple replacements
sed -e 's/old1/new1/g' -e 's/old2/new2/g' file.csv

Summary

Replacing values in CSV files requires careful handling of delimiters and field positions. Use sed for simple text replacements (fastest), awk for column-specific operations (most precise), and always verify results before deleting backups. Test on sample data first. Remember: always escape special characters like /, &, and \ when they appear in replacement values. The -i.bak flag creates automatic backups, protecting you from mistakes.