How can I write a file3 with different columns from a matched result queried from file1 to file2?-CodePudding

I have file1.txt and file2.txt with the following structure

File1.txt

5511913332222

5511910000023

5511910000029

5511910000034

File2.txt

5511910000029|BLOCKED|7|30/07/2021 02:19:43

5511910000034|AVAIL|7|30/07/2021 03:11:53

5511910000048|AVAIL|7|30/07/2021 04:10:25

5511910000073|BLOCKED|7|30/07/2021 07:20:33

I want to write a file3.txt with the 1st and 2nd columns of the file2 where the 1st column matched the 1st column of file1.txt.

File3.txt

5511910000029|BLOCKED

5511910000034|AVAIL

I have tried some tricks with awk but I couldn't get the expected result. Could anyone please help me?

awk 'NR==FNR{a[$0]}NR>FNR && $0 in a{print}' file1 file2 > file3

CodePudding user response：

$ awk -F'|' -v OFS='|' 'NR==FNR {a[$1];next} ($1 && $1 in a){print $1,$2}' File1.txt File2.txt 
5511910000034|AVAIL

To save output to File3.txt ... {print $1,$2 > "File3.txt"} ...

CodePudding user response：

$ join -o 2.1,2.2 -t '|' -j 1 <(sort -k1 -t '|' file1.txt) <(sort -k1 -t '|' file2.txt)
5511910000029|BLOCKED
5511910000034|AVAIL

This means join on the first field in both files (-j 1), outputting (-o) the first and second fields (.1, .2) of the second file (2.), using bar as a field delimiter (-t '|'), and sorting the inputs (<(sort)) on the first field (-k1) with bar as a delimiter (`-t '|' again).

This assumes that file1.txt and file2.txt 1) aren't already sorted; 2) can be sorted; 3) don't have every other line blank, like you show in the question.