merge two csv files according to matching rows and add new columns in linux

linux, unix

Solution

awk may work for you:

kent$  awk -F, -v OFS="," 
       'BEGIN{print "name,Direction,Date,currentDirection,receivedDate"}
        NR==FNR&&NR>1{a[$1]=$0;next}
        FNR>1{printf "%s%s\n",$0,($1 in a?FS a[$1]:"")}' 2.csv 1.csv
name,Direction,Date,currentDirection,receivedDate
abc,sent,Jan 21 2014 02:06,abc,received,Jan 22 2014 02:06
xyz,sent,Nov 21 2014 01:09,xyz,received,Nov 22 2014 02:06
pqr,sent,Oct 21 2014 03:06

update

kent$  awk -F, -v OFS="," 'BEGIN{print "name,Direction,Date,currentDirection,receivedDate"}
        NR==FNR&&NR>1{a[$1]=$2 FS $3;next}
        FNR>1{printf "%s%s\n",$0,($1 in a?FS a[$1]:"")}' 2.csv 1.csv 
name,Direction,Date,currentDirection,receivedDate
abc,sent,Jan 21 2014 02:06,received,Jan 22 2014 02:06
xyz,sent,Nov 21 2014 01:09,received,Nov 22 2014 02:06
pqr,sent,Oct 21 2014 03:06

Problem

I am developing an application using java, but for that I need a csv file in some order. I dont know linux much, but wondering if some way is there to merge the csv files in the required format. I have two csv files containing hundreds of thousands of records. a sample is below: ``` name,Direction,Date abc,sent,Jan 21 2014 02:06 xyz,sent,Nov 21 2014 01:09 pqr,sent,Oct 21 2014 03:06 ``` and ``` name,Direction,Date abc,received,Jan 22 2014 02:06 xyz,received,Nov 22 2014 02:06 ``` so, this second csv file would contain some records of file 1. What I need is a new csv like this: ``` name,Direction,Date,currentDirection,receivedDate abc,sent,Jan 21 2014 02:06,received,Jan 22 2014 02:06 xyz,sent,Nov 21 2014 01:09,received,Nov 22 2014 02:06 pqr,sent,Oct 21 2014 03:06 ``` Need to add the columns(4th and 5th column) according to the matching data in column1. if no matching data is there in the second file, the columns should be empty like above. so is there a bash command in linux to achieve this?

Original source