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
  • 1
    Search join and awk tags, it's been beaten to death... – don_crissti Aug 15 '15 at 0:13
  • @Sachin If the below post answered your question, you can mark it as the answer to your question ;) – Ron Aug 16 '15 at 11:54
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
  • 1
    Thanks! Even better! @don_crissti But, is sort -t '|' -k1,1 necessary, works without it too – Ron Aug 15 '15 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 – don_crissti Aug 15 '15 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.