SED or AWK script to replace multiple text

Question

I am trying to do the following with a sed script but it's taking too much time. Looks like something I'm doing wrongly.

Scenario: I've student records (> 1 million) in students.txt. In This file (each line) 1st 10 characters are student ID and next 10 characters are contact number and so on

students.txt

10000000019234567890XXX...
10000000029325788532YYY...
.
.
.
10010000008766443367ZZZZ...

I have another file (encrypted_contact_numbers.txt) which has all the phone but numbers and corresponding encrypted phone numbers as below

encrypted_contact_numbers.txt

Phone_Number, Encrypted_Phone_Number

9234567890, 1122334455
9325788532, 4466742178
.
.
.
8766443367, 2964267747

I wanted to replace all the contact numbers (11th–20th position) in students.txt with the corresponding encrypted phone number from encrypted_contact_numbers.txt.

Expected Output:

10000000011122334455XXX...
10000000024466742178YYY...
.
.
.
10010000002964267747ZZZZ...

I am using the below sed script to do this operation. It is working fine but too slowly.

Approach 1:

while read -r pattern replacement; do   
    sed -i "s/$pattern/$replacement/" students.txt
done < encrypted_contact_numbers.txt

Approach 2:

sed 's| *\([^ ]*\) *\([^ ]*\).*|s/\1/\2/g|' <encrypted_contact_numbers.txt |
sed -f- students.txt > outfile.txt

Is there any way to process this huge file quickly?

Update: 9-Feb-2018

Solutions given in AWK and Perl is working fine if the phone number is in specified position (column 10-20), If I try to do global replacement it took too much time to process. Is there any best way to achieve this?

students.txt : Updated version

10000000019234567890XXX...9234567890
10000000029325788532YYY...
.
.
.
10010000008766443367ZZZZ9234567890...

Answer 1

awk to the rescue!

if you have enough memory to keep the phone_map file in memory

awk -F', *' 'NR==FNR{a[$1]=$2; next}
                    {key=substr($0,11,20)}
           key in a {$0=substr($0,1,10) a[key] substr($0,21)}1' phone_map data_file

not tested since you're missing the data file. It should speed up since both files will be scanned only once.

Answer 2

Following awk may help you on same.

awk '
FNR==NR{
  sub(/ +$/,"");
  a[$1]=$2;
  next
}
(substr($0,11,10) in a){
  print substr($0,1,10) a[substr($0,11,10)] substr($0,21)
}
' FS=", " encrypted_contact_number.txt students.txt

Output will be as follows. Will add explanation too shortly.

10000000011122334455XXX...
10000000024466742178YYY...

Answer 3

What question would be complete without a Perl answer? :) Adapted from various answers in the Perl Monks' discussion of this topic.

Edited source

Edited per @Borodin's comment. With some inline comments for explanation, in hopes that they are helpful.

#!/usr/bin/env perl

use strict;     # keep out of trouble
use warnings;   # ditto

my %numbers;    # map from real phone number to encrypted phone number

open(my $enc, '<', 'encrypted_contact_numbers.txt') or die("Can't open map file");
while(<$enc>) {
    s{\s+}{}g;                               #remove all whitespace
    my ($regular, $encrypted) = split ',';
    $numbers{$regular} = $encrypted;
}

# Make a regex that will match any of the numbers of interest
my $number_pattern = join '|', map quotemeta, keys %numbers;
$number_pattern = qr{$number_pattern}o;
    # Compile the regex - we no longer need the string representation

while(<>) {     # process each line of the input
    next unless length > 1;     # Skip empty lines (don't need this line if there aren't any in your input file)
    substr($_, 10, 10) =~ s{($number_pattern)}{$numbers{$1}}e;
    # substr: replace only in columns 11--20
    # Replacement (s{}{}e): the 'e' means the replacement text is perl code.
    print;  # output the modified line
}

Test

Tested on Perl v5.22.4.

encrypted_contact_numbers.txt:

9234567890, 1122334455
9325788532, 4466742178

students.txt:

aaaaaaaaaa9234567890XXX...
bbbbbbbbbb9325788532YYY...
cccccccccc8766443367ZZZZ...
dddddddddd5432112345Nonexistent phone number

(modified for ease of reading)

Output of ./process.pl students.txt:

aaaaaaaaaa1122334455XXX...
bbbbbbbbbb4466742178YYY...
cccccccccc8766443367ZZZZ...
dddddddddd5432112345Nonexistent phone number

The change has been made on the first two lines, but not the second two, which is correct for this input.