Shell Programming and Scripting

Good morning everyone and hello unix.com!

I'm trying to solve a problem and was unable to find a solution after a forum research. So hopefully you can help me get this solved.

I have these two csv files:

file1.csv

ID,REFERENCE,STATUS
1,2,0
2,4,1
3,1,0

file2.csv

REFERENCE,FIRST_NAME,LAST_NAME
1,John,Smith
2,Mary,James
3,Steve,Adams
4,Michael,Angelo

What I'm trying to achieve is to replace the 'REFERENCE' column in file1.csv with the respective line from file2.csv (matching 'REFERENCE'). So the output I want is:

1,2,Mary,James,0
2,4,Michael,Angelo,1
3,1,John,Smith,0

I have played around with the JOIN command in conjunction with SORT, but to no avail yet. Does anyone have an idea how to do that?

Thanks a lot,
RKos

You may try this:

Code:

awk -F, 'FNR==NR{
if(NR>1) a[$1]=$0
next}
{if(FNR==1) next
 $2 = ($2 in a ? a[$2] : "NOT FOUND") }
1' OFS=, file2.csv file1.csv

Thank you, Sir.
This awk does exactly what I need and it works on the actual data files. You made me very happy :-)