How to join two files by matching a column with an irregular number of columns?

Question

file1:

0000002|SLM DEV CORP                                               |PO 857
0000003|S TOPPING                                                   |APT 19
0000004|JD  BROS LTD                                                |PO 118
0000005|ZKZ SERVICES                                                |14699  CREDITVIEW RD

file2:

0000001|GTI CONSULTING                     |4513 GLADEBROOK CRES    
0000002|SLM DEVELOPMENT CORP | SLM         |PO BOX 857              
0000003|S TOPPING                          |APT 19                  
0000004|JD PLETT BROS LTD                  |PO BOX 118              
0000005|ZKZ SERVICES |ZKZ                  |14699  CREDITVIEW RD    

expected output:

0000002|SLM DEVELOPMENT CORP | SLM         |PO BOX 857
0000003|S TOPPING                           |APT 19
0000004|JD PLETT BROS LTD                   |PO BOX 118
0000005|ZKZ SERVICES |ZKZ                   |14699  CREDITVIEW RD

I have tried join command:

join -j1 1 -j2 1 -t'|' -o 1.1 2.2 2.3 file1 file2

Due to the pipe delimiter, I got wrong output. Also, I cannot use any other symbol as delimiter since any symbol can be available in column 2.

I need to match column 1 in file 2 and copy column 2 and 3 lengthwise.

I also tried looping column 1 data of file1 in file2 and getting the output but that takes too long since my files are very large.

Answer 1

Simple with awk, if the join field is unique:

awk -F"|" 'a[$1]++' file1 file2

• -F"|" sets pipe as delimiter
• a[$1]++ is a condition. When the condition is true, the line is printed. The condition becomes true, when the first field $1 appears more than one times.

---

If the join field is not unique:

awk -F"|" 'a[$1]++&&FNR!=NR' file1 file2

• FNR!=NR: is also a condition that must be true. This applies only on the second file file2 which is processed. That condition could be removed, when you can guarantee that the first field in both files is unique.

Answer 2

One way:

awk -F"|" 'NR==FNR{a[$1];next}($1 in a)' file1 file2

THis reads the 1st file and stores all the keys in an array. WHen processing the 2nd file, prints only those lines whose key is present in the array.

Since your file size is huge, do not prefer join since it needs the files to be in sorted order.

Answer 3

As far as I can tell, you just want all lines from file2 whose 1st field is present in file1. If so, you can use grep to search and awk or cut to get the 1st field:

$ grep -f <(cut -d '|' -f 1 file1) file2
0000002|SLM DEVELOPMENT CORP | SLM         |PO BOX 857              
0000003|S TOPPING                          |APT 19                  
0000004|JD PLETT BROS LTD                  |PO BOX 118              
0000005|ZKZ SERVICES |ZKZ                  |14699  CREDITVIEW RD    

Or:

$ grep -f <(awk -F'|' '{print $1}' file1) file2
0000002|SLM DEVELOPMENT CORP | SLM         |PO BOX 857              
0000003|S TOPPING                          |APT 19                  
0000004|JD PLETT BROS LTD                  |PO BOX 118              
0000005|ZKZ SERVICES |ZKZ                  |14699  CREDITVIEW RD