awk - if values match then print file1 and file 2

I googled a lot my problem and tested different solutions, but none seem to work. I even used the same command in advance with success but now I do not manage to get my desired output.

I have file1

AAA;123456789A
BBB;123456789B
CCC;123456789C

And file2

1;2;3;CCC;pippo
1;2;3;AAA;pippo
1;2;3;BBB;pippo
1;2;3;*;pippo

My desired output is this:

1;2;3;CCC;pippo;CCC;123456789C
1;2;3;AAA;pippo;AAA;123456789A
1;2;3;BBB;pippo;BBB;123456789B

I tried with this command:

awk -F";" -v OFS=";" 'FNR == NR {a[$10]=$1; b[$20]=$2; next}($10 in a){ if(match(a[$10],$4)) print $0,a[$10],b[$20]}' file1 file2

But I get this output (only one entry, even with bigger files):

1;2;3;CCC;pippo;CCC;123456789C

What am I doing wrong? If it manages for one it should for all the other. Why is this not happening? Also why if I set a[$1]=$1 it doesn't work? Thank you for helping! If possible could you explain the answer? So next time I won't have to ask for help!

EDIT: Sorry, I did not mention (since I wanted to keep the example minimal) that in file2 some fields are just "*". And I'd like to add an "else doesn't match do something".

Answers


awk to the rescue!

$ awk 'BEGIN{FS=OFS=";"} 
     NR==FNR{a[$1]=$0;next} 
            {print $0,a[$4]}' file1 file2

1;2;3;CCC;pippo;CCC;123456789C
1;2;3;AAA;pippo;AAA;123456789A
1;2;3;BBB;pippo;BBB;123456789B

UPDATE: Based on the original input file it was only looking for exact match. If you want to skip the entries where there is no match, you need to qualify the print block with $4 in a

$ awk 'BEGIN{FS=OFS=";"} 
     NR==FNR{a[$1]=$0;next} 
     $4 in a{print $0,a[$4]}' file1 file2

join is made for this sort of thing:

$ join -t';' -1 4 -o1.{1..5} -o2.{1..2} <(sort -t';' -k4 file2) <(sort -t';' file1)

1;2;3;AAA;pippo;AAA;123456789A
1;2;3;BBB;pippo;BBB;123456789B
1;2;3;CCC;pippo;CCC;123456789C

The output is what you asked for except for the ordering of lines, which I assume isn't important. The -o options to join are needed because you want the full set of fields; you can try omitting it and you'll get the join field on the left a single time instead, which might also be fine.


Need Your Help

UWP - ListView vertical scrolling view changed

c# listview winrt-xaml win-universal-app scrollviewer

How can to use ListView inside ScorllVeiwer and don't break the UI virtualization ? I need to use ScrollViewer ViewChanged

Fully managed text editor component for .NET/Winforms

c# winforms mono text-editor

I'm basically looking for a replacement for RichTextBox or TextBox components that is fully written in c# without using pinvokes in order to keep cross platform compatibility.