awk matching fields in 2 separate files using command containing {BEGIN FS = "|"} returns output of blank lines

Question

Two files: data1

 Name |formula |no. |dose|days|cost |msg|em|notes Fname-Lname|BXXXT+GG |8262|4 |14 |57.78 | | |sq Fname-Lname|SJXXT+GG |8263|4¾ |14 |105.15| | |IB Fname-Lname|FJDHT+BH,LG,CQC,ZX|8264|5¾ |14 |46.20 | | |IB 

data2

10/12/2020|more-data-3456|105.15 10/12/2020|more-data-3456|95.10 11/12/2020|more.data-3456|30.30 14/12/2020|more-data-3456|45.55 

I am using the code snippet

awk 'BEGIN {FS = "|" } NR==FNR{a[$6];next} $3 in a {print $0}' data1 data2 

To match where a value in $6 of file data1 also occurs in $3 of file data2. Where there is a match print out the whole record ($0) containing the match from file data2. I am expecting:

10/12/2020|more-data-3456|105.15 

But I am only getting an output of a blank line. I removed the file separators "|" using a " " as replacement the command code worked exactly as expected however really want to preserve the field separator as | if at all possible . I would like to understand why the addition of a BEGIN block has caused this . Has it caused awk to load an empty array in place of taking data from S6 ? My awk level is just above beginner. Edit: I have also used the -F parameter with the same result, an out put of a blank line . I am using gawk .

Answer 1

1. You probably have DOS line endings, see why-does-my-tool-output-overwrite-itself-and-how-do-i-fix-it, so remove that if present (I'm removing that and any other trailing spaces with the sub() in my script below).
2. If you're getting a blank line output then you have blank lines in each of your input files but I'd bet you aren't REALLY getting a blank line output and instead you're getting the 1 line of output you should get for 105.15 but the CR at the end is overwriting back to the start of the line - pipe the output to | cat -v to see if that's true.
3. Your input has blanks before and after the |s in some places so you should set FS to match - FS=" *[|] *"
4. You don't need to write {print $0} as that's the default behavior

Try this:

awk 'BEGIN{FS=" *[|] *"} {sub(/[[:space:]]+$/,"")} NR==FNR{a[$6];next} $3 in a' data1 data2 

Answer 2

Your code works as-is for me, both with GNU awk 5.1.0 and with macOS awk 20200816.

Which version of awk are you using?

Note that you can also set the field separator with the -F command-line parameter; if you do that, then the BEGIN block is unnecessary.