1

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.

  • I don't understand. Your output seems to be all the lines from file2 whose 1st field appears in file1. Are you using any data from file1 apart from the 1st field? – terdon Jan 27 '16 at 11:03
  • @terdon "irregular columns" came from me. I understood the question that way. If you look at file2 line2, the middle field is also splitted in two fields... That's what is meant with "irregular fields". And thus the join command prints not the whole line. – chaos Jan 27 '16 at 11:05
  • @chaos yes, I understood after posting my comment and removed that part. Thanks. – terdon Jan 27 '16 at 11:07
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.
1

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.

0

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    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.