awk - Matching columns between 2 files and reordering results

Shell Programming and Scripting
1

I am trying to match 4 colums (first_name,last_name,dob,ssn) between 2 files and when there is an exact match I need to write out these matches to a new file with a combination of fields from file1 and file2. I've managed to come up with a way to match these 2 files based on the columns (see below) but I'm not sure how to control which columns from either file1 or file2 I can output to a final results file.

nawk -F "|" 'FNR==NR{ x[$2$3$5$6]; next} $1$2$4$3 in x' file1 file2

Here's an example of my files below.

file1: Min List

id|first_name|last_name|gender|ssn|dob
10000|Maria|Bellow|F|111-15-1111|01/21/1957
10001|Brian|Hala|M|123-45-6789|01/21/1940
10002|Amy|Jones|F|123-45-6789|01/19/1972
10003|Jimmy|Thomas|M|222-45-2222|05/25/1952
10004|John|Smith|M||02/22/1970
10005|Atkins|Lucent|F|123-45-6789|02/27/1955
10006|Shawn|Batman|F|123-45-6789|03/21/1936
10007|Karen|Rockefeller|F|123-45-6789|02/11/1957

file2: Master List

first_name|last_name|dob|ssn|value1|value2|value3|value4|value5|value6|value7

Maria|Bellow|01/21/1957|111-15-1111|value1|value2|value3|value4|value5|value6|value7
Jimmy|Thomas|05/25/1952|222-45-2222|value1|value2|value3|value4|value5|value6|value7Maria|Bellow|01/21/1957|111-15-1111|value1|value2|value3|value4|value5|value6|value7
Jimmy|Thomas|05/25/1952|222-45-3333|value1|value2|value3|value4|value5|value6|value7
James|Thomas|01/21/1957|123-45-6789|value1|value2|value3|value4|value5|value6|value7

file3: Results List

id|value1|value2|last_name|first_name|value3|value4|value5|value6|value7
10000|value1|value2|Bellow|Maria|value3|value4|value5|value6|value7
10000|value1|value2|Bellow|Maria|value3|value4|value5|value6|value7
10003|value1|value2|Thomas|Jimmy|value3|value4|value5|value6|value7

Notice that the "id" column from file1 is in file3 the results file and all others are from file2 in different columns. Any help on this is greatly appreciated.

2

--- removed ---

3 
# awk 'BEGIN{FS=OFS="|"}NR==FNR{a[$2$3$5$6]=$1}a[$1$2$4$3]{print a[$1$2$4$3],$5,$6,$2,$1,$7,$8,$9,$10,$11}' minlist master
id|value1|value2|last_name|first_name|value3|value4|value5|value6|value7
10000|value1|value2|Bellow|Maria|value3|value4|value5|value6|value7
10003|value1|value2|Thomas|Jimmy|value3|value4|value5|value6|value7
10000|value1|value2|Bellow|Maria|value3|value4|value5|value6|value7
4

Thank you very very much!!!

5

Be welcome, you can use the Thanks button on the right side if the answer is useful and correct.

1 Like
6

I used that button. I more thing, Is there a way to match these fields for the first_name and last_name non case sensitive?

7 
awk 'BEGIN{FS=OFS="|"}NR==FNR{a[tolower($2$3)$5$6]=$1}a[tolower($1$2)$4$3]{print a[tolower($1$2)$4$3],$5,$6,$2,$1,$7,$8,$9,$10,$11}' minlist master
8

danmero - you definitely ROCK!