How to join two files using two columns in unix
Clash Royale CLAN TAG#URR8PPP
up vote
0
down vote
favorite
I have file a and file lookup and the structure is below.
vi a.txt
empid|ename|sal_grade|MANAGER_ID
1|raj|A|202
2|MAN|B|203
3|JOS|C|204
vi lookup.txt
Grade|sal|manager_id
A|$100000|202
A|$1000|099
B|$1000000|203
B|$100|011
Output:
1|raj|A|202
2|MAN|B|203
command:
awk 'BEGIN " NR==FNRa[$1];next $3 in aprint $0' lookup.txt a.txt >matched.txt
Here in the both files grade and manager_id is common and i want to join a.txt with lookup.txt on grade and manager_id and get the data out from a.txt where it got a match from lookup.txt .I tried with below command but it will join only on column i.e on grade column but i need to join on both grade and manager id column .
Thank's in advance.
awk gawk
asked Sep 27 at 18:28
Rak kundra
149111
add a comment |Â
up vote
0
down vote
favorite
I have file a and file lookup and the structure is below.
vi a.txt
empid|ename|sal_grade|MANAGER_ID
1|raj|A|202
2|MAN|B|203
3|JOS|C|204
vi lookup.txt
Grade|sal|manager_id
A|$100000|202
A|$1000|099
B|$1000000|203
B|$100|011
Output:
1|raj|A|202
2|MAN|B|203
command:
awk 'BEGIN " NR==FNRa[$1];next $3 in aprint $0' lookup.txt a.txt >matched.txt
Here in the both files grade and manager_id is common and i want to join a.txt with lookup.txt on grade and manager_id and get the data out from a.txt where it got a match from lookup.txt .I tried with below command but it will join only on column i.e on grade column but i need to join on both grade and manager id column .
Thank's in advance.
awk gawk
asked Sep 27 at 18:28
Rak kundra
149111
add a comment |Â
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have file a and file lookup and the structure is below.
vi a.txt
empid|ename|sal_grade|MANAGER_ID
1|raj|A|202
2|MAN|B|203
3|JOS|C|204
vi lookup.txt
Grade|sal|manager_id
A|$100000|202
A|$1000|099
B|$1000000|203
B|$100|011
Output:
1|raj|A|202
2|MAN|B|203
command:
awk 'BEGIN " NR==FNRa[$1];next $3 in aprint $0' lookup.txt a.txt >matched.txt
Here in the both files grade and manager_id is common and i want to join a.txt with lookup.txt on grade and manager_id and get the data out from a.txt where it got a match from lookup.txt .I tried with below command but it will join only on column i.e on grade column but i need to join on both grade and manager id column .
Thank's in advance.
awk gawk
asked Sep 27 at 18:28
Rak kundra
149111
I have file a and file lookup and the structure is below.
vi a.txt
empid|ename|sal_grade|MANAGER_ID
1|raj|A|202
2|MAN|B|203
3|JOS|C|204
vi lookup.txt
Grade|sal|manager_id
A|$100000|202
A|$1000|099
B|$1000000|203
B|$100|011
Output:
1|raj|A|202
2|MAN|B|203
command:
awk 'BEGIN " NR==FNRa[$1];next $3 in aprint $0' lookup.txt a.txt >matched.txt
Here in the both files grade and manager_id is common and i want to join a.txt with lookup.txt on grade and manager_id and get the data out from a.txt where it got a match from lookup.txt .I tried with below command but it will join only on column i.e on grade column but i need to join on both grade and manager id column .
Thank's in advance.
awk gawk
awk gawk
asked Sep 27 at 18:28
Rak kundra
149111
asked Sep 27 at 18:28
Rak kundra
149111
asked Sep 27 at 18:28
Rak kundra
149111
asked Sep 27 at 18:28
Rak kundra
149111
asked Sep 27 at 18:28
Rak kundra
149111
149111
add a comment |Â
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
If you need a combined key, build a combined key.
awk '
BEGIN "
NR==FNR a[$1 "-" $3]; next
($3 "-" $4) in a print $0
' lookup.txt a.txt > matched.txt
You can use "-" as a separator, or another string, or nothing at all. With the data from your example it would work without a separator.
answered Sep 27 at 18:35
RalfFriedl
4,2481725
Thank you for the reply . It works for me .
– Rak kundra
Sep 27 at 19:18
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
If you need a combined key, build a combined key.
awk '
BEGIN "
NR==FNR a[$1 "-" $3]; next
($3 "-" $4) in a print $0
' lookup.txt a.txt > matched.txt
You can use "-" as a separator, or another string, or nothing at all. With the data from your example it would work without a separator.
answered Sep 27 at 18:35
RalfFriedl
4,2481725
Thank you for the reply . It works for me .
– Rak kundra
Sep 27 at 19:18
add a comment |Â
up vote
0
down vote
accepted
If you need a combined key, build a combined key.
awk '
BEGIN "
NR==FNR a[$1 "-" $3]; next
($3 "-" $4) in a print $0
' lookup.txt a.txt > matched.txt
You can use "-" as a separator, or another string, or nothing at all. With the data from your example it would work without a separator.
answered Sep 27 at 18:35
RalfFriedl
4,2481725
Thank you for the reply . It works for me .
– Rak kundra
Sep 27 at 19:18
add a comment |Â
up vote
0
down vote
accepted
up vote
0
down vote
accepted
If you need a combined key, build a combined key.
awk '
BEGIN "
NR==FNR a[$1 "-" $3]; next
($3 "-" $4) in a print $0
' lookup.txt a.txt > matched.txt
You can use "-" as a separator, or another string, or nothing at all. With the data from your example it would work without a separator.
answered Sep 27 at 18:35
RalfFriedl
4,2481725
If you need a combined key, build a combined key.
awk '
BEGIN "
NR==FNR a[$1 "-" $3]; next
($3 "-" $4) in a print $0
' lookup.txt a.txt > matched.txt
You can use "-" as a separator, or another string, or nothing at all. With the data from your example it would work without a separator.
answered Sep 27 at 18:35
RalfFriedl
4,2481725
answered Sep 27 at 18:35
RalfFriedl
4,2481725
answered Sep 27 at 18:35
RalfFriedl
4,2481725
answered Sep 27 at 18:35
RalfFriedl
4,2481725
4,2481725
Thank you for the reply . It works for me .
– Rak kundra
Sep 27 at 19:18
add a comment |Â
Thank you for the reply . It works for me .
– Rak kundra
Sep 27 at 19:18
Thank you for the reply . It works for me .
– Rak kundra
Sep 27 at 19:18
Thank you for the reply . It works for me .
– Rak kundra
Sep 27 at 19:18
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f471900%2fhow-to-join-two-files-using-two-columns-in-unix%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
