1

File1: Excel file (.xls)

UN          ID    St      M1    M2       SE    DOF  PV        PA            FC
17127159    0   -5.9    297.3   765.7   0.22    4   0.003   0.00389231  2.57536
17127163    2   -3.87   189.914 492.307 0.3548  4   0.0179  0.01795     2.59226
17127167    4   -3.8908 339.136 855.276 0.3429  4   0.0176  0.017       2.52192
17127171    6   -3.922  390.44  986.365 0.340   4   0.0172179   0.01721 2.52627
17127175    8   -4.715  536.072 1210.65 0.2492  4   0.00920158  0.00920 2.258

File2: Text file (.txt)

UNIT_ID   UN      TID        X       E       GG7     J     O
0      17127159 16657436 353.568 335.295 221.717 815.654 684.85
1      17127161 16657436 11.0842 7.01459 7.33511 11.2121 12.6268
2      17127163 16657450 221.647 226.774 136.274 431.32  392.533
3      17127165 16657452 5.02182 3.41172 4.12834 6.90306 4.91183

If 1st column of 1st file matches with 2nd column of 2nd file the output should look like the following

UN         FC        TID       X       E       GG7     J     O
17127159  2.57536  16657436 353.568 335.295 221.717 815.654 684.85
17127163  2.59226  16657450 221.647 226.774 136.274 431.32  392.533

Thank you in advance.

Improve this question
3
  • If that's an actual excel file, you'll have to save it in tab-separated text format.
    – glenn jackman
    Commented Apr 1, 2016 at 9:57
  • I did that but still the UN FC and TID are saved in only one column
    – stack_learner
    Commented Apr 1, 2016 at 10:06
  • 4
    how is this any different from your question yesterday? unix.stackexchange.com/questions/273403/…
    – cas
    Commented Apr 1, 2016 at 23:26

2 Answers 2

Reset to default
1

With awk:

$ awk 'NR==FNR{a[$2]=$3"\t"$4"\t"$5"\t"$6"\t"$7"\t"$8; next} 
              {
                if($1 in a){
                    print $1,$10,a[$1]
                }
               }' file2 file1
UN        FC        TID       X         E         GG7       J         O
17127159  2.57536   16657436  353.568   335.295   221.717   815.654   684.85
17127163  2.59226   16657450  221.647   226.774   136.274   431.32    392.533

If the spacing is important (it isn't to most programs but can be for humans), you can do:

$ awk 'NR==FNR{
               a[$2]=sprintf("%-10s%-10s%-10s%-10s%-10s%-10s",$3,$4,$5,$6,$7,$8);
               next;
              } 
              {
                if($1 in a){
                    printf("%-10s%-10s%10s\n", $1,$10,a[$1])
                }
               }' file2 file1
UN        FC        TID       X         E         GG7       J         O         
17127159  2.57536   16657436  353.568   335.295   221.717   815.654   684.85    
17127163  2.59226   16657450  221.647   226.774   136.274   431.32    392.533
Improve this answer
7
  • Could you please check the output which I need. The first column i need is UN not ID
    – stack_learner
    Commented Apr 1, 2016 at 9:38
  • @user3351523 whops, sorry, my bad! Fixed.
    – terdon ♦
    Commented Apr 1, 2016 at 9:40
  • it worked but UN, FC and TID are combined in one column
    – stack_learner
    Commented Apr 1, 2016 at 9:46
  • @user3351523 then, again, the data you show doesn't accurately represent the data you have. You have still not explained what you mean by "Excel file" (although I asked you three times). That might be the source of the problem.
    – terdon ♦
    Commented Apr 1, 2016 at 9:50
  • there is no problem with the data and even Excel file is saved in tab separated text format. But still I see in the output that UN, FC and TID in the same column. I saved the output file in .txt format
    – stack_learner
    Commented Apr 1, 2016 at 10:09
0

here is something it might help you try looking for more on web...

awk 'FNR==NR{a[$2]=$3;next}{print $0,a[$2]?a[$2]:"NA"}' file2 file1
Improve this answer
1
  • It gives all the columns from file1. that is not what I need.
    – stack_learner
    Commented Apr 1, 2016 at 9:41

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