Find and Replace in CSV Files (Bulk Edit Guide)

You have a CSV with 50,000 rows. You realize "California" is spelled "Calfornia" in 2,000 of them. Or you need to change a product ID prefix from OLD- to NEW- everywhere.

Doing this manually is impossible. You need Find and Replace.

Here is how to do it safely and efficiently, whether you have 50 rows or 5 million.

Method 1: Excel / Spreadsheet (Small to Medium Files)

If your file opens in Excel (under 1 million rows), this is the easiest way.

1. Open the CSV in Excel.
2. Select the column you want to edit. (Click the letter at the top, e.g., Column C).
   • Tip: Selecting specific columns prevents accidental replacements in other data.
3. Press Ctrl+H (Windows) or Cmd+H (Mac).
4. Find what: Calfornia
5. Replace with: California
6. Click Replace All.
7. Save the file.

Warning: Excel might auto-format dates or remove leading zeros when you open the file. If data integrity is critical, use a text editor or specialized tool.

Method 2: Text Editor (Notepad++, Sublime, VS Code)

Text editors are safer because they don't mess with your data formatting. They just treat everything as text.

1. Open the CSV in your editor.
2. Press Ctrl+H / Cmd+Option+F.
3. Enter your Find and Replace terms.
4. Click Replace All.
5. Save.

Advanced Tip: Regex (Regular Expressions) Code editors like VS Code allow "Regex" search. This lets you do complex replacements.

Example: Remove all quotes.

• Find: "
• Replace: (leave empty)

Example: Change date format from 2024/01/01 to 2024-01-01.

• Find: /
• Replace: -

Method 3: Command Line (sed) - For Huge Files

If your file is 5GB and crashes Excel, use the command line. sed is a stream editor available on Linux and Mac.

Command:

sed -i 's/old-text/new-text/g' filename.csv

• -i: Edit file in-place (save changes).
• s: Substitute.
• g: Global (replace all occurrences per line).

Example: Change "USA" to "United States" in data.csv:

sed -i 's/USA/United States/g' data.csv

Warning: This is case-sensitive and literal. It will replace "USA" inside "JERUSALEM" -> "JERUnited StatesLEM". Be careful!

To be safer, you can target specific columns using awk, but that gets complicated.

Method 4: Specialized CSV Tools (Best Balance)

If you want the safety of a text editor but the ease of a GUI, use a dedicated CSV tool.

Features to look for:

• Column targeting: Only replace in "City" column.
• Case sensitivity: Match "apple" but not "Apple".
• Whole word match: Replace "cat" but not "catastrophe".
• Streaming: Handles huge files without crashing.

-> HappyCSV Find & Replace Tool

Common Pitfalls

1. Unintended Replacements

You want to change "Hi" to "Hello". But you have a column with "High". Result: "Hellogh".

Fix: Use "Match whole word only" options, or include delimiters in your search (e.g., find ,Hi, replace with ,Hello,).

2. Breaking the CSV Structure

If you replace a comma , with a pipe |, you might break the column separation. If you replace text with something containing a comma, you break the row structure.

Fix: If your new text has a comma, ensure it is wrapped in quotes. Replace: Main St With: "Main St, Apt 1" (Quotes added)

3. Encoding Issues

Saving in a text editor might change the encoding from UTF-8 to ANSI.

Fix: Always check your save settings.

Summary

• Excel: Good for visual checks, bad for large files/formatting safety.
• Text Editor: Safe, powerful, good for medium files.
• Command Line: Best for massive files, requires caution.
• CSV Tool: Best for targeted, safe replacements.

---

Need to bulk edit safely? HappyCSV lets you Find & Replace in specific columns without breaking your file structure.