A shell script for joining two files

Question

I want to write a shell script that get two files A and B, and get a result like this:

File A:

user_a tel_a addr_a
user_b tel_b addr_b

File B:

process_1 user_a
process_2 user_a
process_3 user_b

And the result:

user_a process_1 tel_a addr_a
user_a process_2 tel_a addr_a
user_b process_3 tel_b addr_b

How can i do this? awk or something else?

I think it can be done using `cut` and `paste` only, but I do not get the synthax right. — Bernhard, Feb 26 '12 at 18:55
My files have many records and fields, i can't cut and paste!! This is just a sample. — Navid Farhadi, Feb 26 '12 at 18:58
@NavidFarhadi not cut&paste: there are two actual command `cut` and `paste` look at their manual page. — Matteo, Feb 26 '12 at 19:07
I can load both files in memory completely and i can also use awk. — Navid Farhadi, Feb 26 '12 at 19:21
I also can use perl or others, if they were executable in linux command line. — Navid Farhadi, Feb 26 '12 at 19:28

Peter.O · Accepted Answer · 2012-02-26T19:36:36.900

15

join ...

join -1 2 -2 1 FileB FileA

Output

user_a process_1 tel_a addr_a
user_a process_2 tel_a addr_a
user_b process_3 tel_b addr_b

The input files need to be sorted by the key field ... Your example files are already sorted, so there was no need, but otherwise you could incorporate the sort as follows.

join -1 2 -2 1 <(sort -k2 FileB) <(sort FileA)

edited Feb 26 '12 at 19:36

answered Feb 26 '12 at 19:24

Peter.O

32,426
28
115
163

What means the number parameters? – Navid Farhadi Feb 26 '12 at 19:31
5

@Navid: You can always get the best and most accurate description of a command's paramaters by refering to the *manual*, by typing `man join` into the terminal's command-line... `-1 2 -2 1` means: join on the **'1st-file 2nd-field'** and the **'2nd-file 1st-field'** – Peter.O Feb 26 '12 at 19:44

dubiousjim · Answer 2 · 2012-04-19T23:55:00.160

3

Since join and paste aren't available everywhere (they're not on my BusyBox-based system, for example), here is how to do it with awk, as requested:

awk 'BEGIN {
    while( (getline < "fileA") > 0) A[$1]=$2 OFS $3 # read fileA into the array A
    close("fileA")
  } {
    print $2, $1, A[$2]
  }' fileB

edited Apr 19 '12 at 23:55

answered Apr 19 '12 at 19:22

dubiousjim

2,648
19
27

BTW you missed the seperator `A[$1]=$2 OFS $3` ... Here is another variant which avoids the manual loop in BEGIN, but it's pretty much the same (to *awk*), although it does introduce an otherwise unnecessary condition test for the second file: `awk 'NR==FNR {A[$1]=$2 OFS $3;next} {print $2, $1, A[$2]}' fileA fileB` ...(+1) – Peter.O Apr 19 '12 at 23:13

A shell script for joining two files

2 Answers2

Linked