How to merge two csv files based on one matching column in unix. The position of column is different in both file

Question

I want to merge file2.csv with file1.csv based on matching column values. col1 of files 2 needs to be matched with col4 of file one. if it matches then merge the file2.csv with file1.csv.

file2.csv

1111_2222_2222,3333
1112_2223_2224,3333

File1.csv

d20200317,v0000,7777,2001_0218_AAAA,111
d20200318,v0000,7777,0102_5913_AAA,111

I tried below awk command but it is not working:

awk 'NR==FNR{a[$2]=$1;next}{print $0"," a[$1];}' File1.csv file2.csv

Answer 1

Why OP's attempt didn't work: By default awk uses field separator as space so we need to set FS value to = since field separator in csv files is ,.

Considering that you want to merge files and add column from Input_file1.csv to Input_file2.csv in its last position.

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

Output will be as follows.

d20200317,v22221,7395,2001_0218_AAAA,111 d20200318,v22221,7395,0102_5913_AAA,111