2

I have a requirement of comparing the first column between 2 pipe delimited files and if they match, I need to replace the 3rd Column in File 1 with 4th Column in File 2.

File 1:

111|xyz|23345
222|abc|123
333|xyz|45667
444|xyz|5432
555|xyz|8976

File 2:

111|xyz|344|rtms
222|abc|222|xyzw
666|xyz|ggg|abde
888|xyz|ff|nnnn
333|xyz|dd|abde
444|xyz|vv|nnnn
555|xyz|bbb|uuyytt

Output File:

111|xyz|rtms
222|abc|xyzw
333|xyz|abde
444|xyz|nnnn
555|xyz|uuyytt
2
  • 1
    Search join and awk tags, it's been beaten to death... Aug 15, 2015 at 0:13
  • @Sachin If the below post answered your question, you can mark it as the answer to your question ;)
    – Ron
    Aug 16, 2015 at 11:54

1 Answer 1

3

One-liner without the need for awk and a temporary file:

join -t '|' -j1 -o 1.1 1.2 2.4 <(sort -t'|' -k1,1 file1) <(sort -t '|' -k1,1 file2)

Using both join and awk:

First, sort file2 based on 1st field and save it it file2.sort

sort -k 1 file2 > file2.sort

Now, using "|" as the delimiter, join file1 and file2.sort. Then again using "|" as the delimiter, extract the necessary column using awk.

join -t '|' file1 file2.sort | awk -F  "|" ' {print $1"|"$2"|"$6}'

THe output will be:

ron@ron:~$ join -t '|' file1 file2.sort | awk -F  "|" ' {print $1"|"$2"|"$6}'
111|xyz|rtms
222|abc|xyzw
333|xyz|abde
444|xyz|nnnn
555|xyz|uuyytt
2
  • 1
    Thanks! Even better! @don_crissti But, is sort -t '|' -k1,1 necessary, works without it too
    – Ron
    Aug 15, 2015 at 11:42
  • Not needed with the given samples, indeed but with unknown input you may need it (e.g. fields with spaces etc) to avoid join complaining that input isn't sorted (you're joining using | as delimiter but sort by default uses blanks). The first sort isn't needed at all in this particular case as the first file appears to be sorted Aug 15, 2015 at 11:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .