Compare 2 csv files

Hello,
I want to compare two csv files expected.csv and actual.csv by fields "f3", "f4", "f5". Field "z" can be differnt.
Each record identified by two fields: "number" +"key".
Records are not sorted.
Can you please help me to get such report:

Error: Mismatch in the record "1,a,33,44,56,333", field "f5". Expected - 56, actual - 55
Error: There is no record "4,d,90,12,56,888" in Expected
Error: There is no record "4,x,90,12,56,333" in Actual

expected.csv:

number,key,f3,f4,f5,z
1,a,33,44,56,333
1,b,45,56,67,222
2,c,55,56,89,222
3,d,87,88,90,444
4,x,90,12,56,333

actual.csv :

number,key,f3,f4,f5,z
1,a,33,44,55,999
4,d,90,12,56,888
1,b,45,56,67,777
3,d,87,88,90,777
2,c,55,56,89,777

Thanks in advance

It's not remotely clear to me how you get the "expected" output. Did you perhaps forget to include the "input" for that, and a description of how that's to be handled, that would add some context and meaning to the question? Also showing what you've done (code) to get that expected output would be good.

I mean that I want to compare expected.csv and actual.csv, and get report in console or file. So far I can only compare by whole string:

awk -F',' 'NR==FNR {a[$1]=$0;next;}; !($1 in a) || ($0 != a[$1]);' expected.csv actual.csv

Now, this (or very similar) problem has been solved umpteen times in here. Try

awk -F, '
function PRERR(VAR)     {print "Error: Mismatch in the record ", $0, ", field f" VAR ". Expected - ", FN[VAR, KEY], " actual - ", $VAR
                        }

                        {KEY = $1 SUBSEP $2
                        }

NR == FNR               {LN[KEY] = $0
                         for (i=3; i<=5; i++) FN[i, KEY] = $i
                         next
                        }

(KEY in LN)             {for (i=3; i<=5; i++) if (FN[i, KEY] != $i) PRERR(i)
                         delete LN[KEY]
                         next
                        }

                        {print "Error: There is no record ", $0, " in Expected."
                         delete LN[KEY]
                        }

END                     {for (l in LN) print "Error: There is no record ", LN[l], " in Actual."
                        }
 
' file[12]
Error: Mismatch in the record  1,a,33,44,55,999 , field f5. Expected -  56  actual -  55
Error: There is no record  4,d,90,12,56,888  in Expected.
Error: There is no record  4,x,90,12,56,333  in Actual.
1 Like

Thank you very much!