Could you please help me with the below requirement?

I have two files FILE1 and FILE2 with the below content (example):


Column1       Column2
M             1.2
A             11
S             hostname
T             TCP
E             120
R             Y


Column1        Column2
A             12
M             1.3
T             SSL
S             hostname1

The order of fields in FILE1 and FILE2 are different, I want to compare FILE1 and FILE2, Take 1st column from FILE1 and search the entire column1 of FILE2 and once it finds the match, it has to pick column2 value (from FILE2) and replace the value in the corresponding field in FILE1.

Appreciate if someone can please help my requirement.

  • Is it important that the order of the lines in file1 be kept? Or can the lines be shuffled in the resulting output? – xhienne Jan 11 '17 at 18:13
  • 2
    Please show the expected output. – Stéphane Chazelas Jan 11 '17 at 18:21
  • Hello, and welcome to SE. What have you tried? How did it not work as you expected or intended? What is your desired output? – DopeGhoti Jan 11 '17 at 19:55

You could do something like:

$ join -o 0,1.2,2.2 -a 1 <(sort -k 1,1 FILE1) <(sort -k 1,1 FILE2) |
     awk '{print $1, $NF}'
A 12
E 120
M 1.3
S hostname1

Or to preserve the order of the lines in FILE1:

$ awk '!n {a[$1] = $2; next}
   {print $1, $1 in a ? a[$1] : $2}' FILE2 n=1 FILE1
M 1.3
A 12
S hostname1
E 120
  • Hi Stephane, yes I want to preserve the Order of lines in FILE1. All I want to do is, Compare column1 field from FILE1 with the respective Field in FILE2-->take the respective value from FILE2 and replace it in the corresponding filed of FILE1. From your answer I can see you used awk command, could you please explain the syntax for my benefit. – Ram Jan 12 '17 at 6:53
  • Hi Experts, In Continuation to the above requirement, what change should I do in the command, in case if I need to leave few fields as it is with out comparing with FILE 2. Which means it will retain the same entries as original. How can I hardcode those respective fields. – Ram Jan 23 '17 at 9:19

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.