如何高效实现：若CSV列值匹配文本文件内容则替换列值并更新指定列

Great question—using repeated grep calls for every line of your CSV is definitely inefficient, especially as your dataset grows. Let's fix this with a single pass using awk, which will load your target values into memory once and process the CSV in one go.

Step-by-Step Solution #

Here's the command that does exactly what you need, with far better performance:

awk '
BEGIN {
    FS = ", "  # Define CSV field separator to match your input format
    OFS = ", " # Ensure output uses the same separator for consistency
}
# Load all target values from the text file into a lookup array
NR == FNR {
    for (i = 1; i <= NF; i++) {
        target_values[$i] = 1
    }
    next
}
# Process each line of the CSV file
{
    # Check if the second column exists in our preloaded target array
    if ($2 in target_values) {
        $4 = $2  # Move original second column value to the fourth column
        $2 = "-" # Replace second column with "-"
    }
    # Print the modified (or unchanged) line
    print $0
}' target_values.txt your_input.csv

How This Beats Your Original Approach #

Let's break down the efficiency gains:

• Single Load of Targets: The NR == FNR block runs only for the first file (target_values.txt). It reads every value in the file and stores it in an in-memory array, so we never re-read this file again (unlike repeated grep calls which re-open and scan the file every time).
• Fast Lookups: Checking if a value exists in an awk array is an O(1) operation—way faster than spawning a new grep process for each CSV line.
• One Pass Through CSV: We process the CSV line by line in a single sweep, without loading the entire file into memory.

Example Test with Your Sample Data #

Target text file (target_values.txt): #

0 1 2

Input CSV (your_input.csv): #

carrot, 0, cat, r
orange, 2, cat, m
banana, 4, robin, d

Output after running the command: #

carrot, -, cat, 0
orange, -, cat, 2
banana, 4, robin, d

This perfectly matches your expected result!

Quick Notes #

• If your CSV uses a different separator (like just , without a space), adjust FS and OFS to match (e.g., FS = ",").
• Duplicate values in the target text file don't cause issues—awk will just overwrite the array entry, but the lookup still works correctly.
• This scales well for large CSV files since it processes lines one at a time, no heavy memory usage.

内容的提问来源于stack exchange，提问作者Johnny Johnston