Sed replace string, based on input file

Question

Wanting to see if there is a better/quicker way to do this.

Basically, I have a file and I need to add some more information to it, based on one of its fields. e.g.

File to edit:

USER|ROLE
user1|role1
user1|role2
user2|role1
user2|role11

Input File:

Role|Application
role1|applicationabc
role2|application_qwerty
role3|application_new_app_new
role4|qwerty_abc_123
role11|applicationabc123

By the end, I want to be left with something like this:

USER|ROLE|Application
user1|role1|applicationabc
user1|role2|application_qwerty
user2|role11|applicationabc123
user2|role3|application_new_app_new

My idea:

cat inputfile | while IFS='|' read src rep
do   
sed -i "s#\<$src\>#$src\|$rep#" /path/to/file/filename.csv
done

What I've written works to an extent, but it is very slow. Also, if it finds a match anywhere in the line, it will replace it. For example, for user2, and role11, the script would match role1 before it matches role11.

So my questions are:

1. Is there a quicker way to do this?
2. Is there a way to match against the exact expression/string? Putting quotes in my input file doesn't seem to work.

Answer 1

With join:

join -i -t "|" -1 2 -2 1 <(sort -t '|' -k2b,2 file) <(sort -t '|' -k 1b,1 input)

From the join manpage:

Important: FILE1 and FILE2 must be sorted on the join fields.

That's why we need to sort the two files first: file on the first field and input on the second.

Then join joins the two file on those fields -1 2 -2 1. Output would then be:

ROLE|USER|Application
role1|user1|applicationabc
role1|user2|applicationabc
role11|user2|applicationabc123
role2|user1|application_qwerty

Answer 2

Piece of cake with awk:

$ cat file1
USER|ROLE
user1|role1
user1|role2
user2|role1
user2|role11

$ cat file2
ROLE|Application
role1|applicationabc
role2|application_qwerty
role3|application_new_app_new
role4|qwerty_abc_123
role11|applicationabc123

$ awk -F'\\|' 'NR==FNR{a[$1]=$2; next}; {print $0 "|" a[$2]}' file2 file1
USER|ROLE|Application
user1|role1|applicationabc
user1|role2|application_qwerty
user2|role1|applicationabc
user2|role11|applicationabc123

Answer 3

Please try the following:

awk 'FNR==NR{A[$1]=$2;next}s=$2 in A{ $3=A[$2] }s' FS='|' OFS='|' file2 file1

or:

awk 'FNR==NR{A[$1]=$2;next} $3 = $2 in A ? A[$2] : 0' FS='|' OFS='|' file2 file1

Explanation

 awk '
     # FNR==NR this is true only when awk reading first file

     FNR==NR{
                # Create array A where index = field1($1) and value = field2($2) 
                A[$1]=$2

                # stop processing and go to next line
                next
            }

   # Here we read 2nd file that is file1 in your case
   # var in Array returns either 1=true or 0=false
   # if array A has index field2 ($2) then s will be 1 otherwise 0 
   # whenever s is 1 that is nothing but true state, we create new field
   # $3 and its value will be array element corresponds to array index field2

   s=$2 in A{
               $3=A[$2] 
            }s

  # An awk program is a series of condition-action pairs, 
  # conditions  being outside of curly braces and actions being enclosed in them. 
  # A condition is considered false if it evaluates to zero or the empty string,
  # anything else is true (uninitialized variables are zero or empty string, 
  # depending on context, so they are false). 
  # Either a condition or an action can be implied; 
  # braces without a condition are considered to have a true condition and 
  # are always executed if they are hit,
  # and any condition without an action will print the line 
  # if and only if the condition is met.

  # So finally }s at the end of script
  # it executes the default action for every line, 
  # printing the line whenever s is 1 that is true 
  # which may have been modified by the previous action in braces

  # FS  = Input Field Separator
  # OFS = Output Field Separator

    ' FS='|' OFS='|' file2 file1