Merge two csv files if Id columns match

Question

I have the following:

file1.csv

"Id","clientName1","clientName2"

file2.csv

"Id","Name1","Name2"

I want to read file1 sequentially. For each record, I want to check if there is a matching Id in file2. There may be more than one match. For each match, I want to append Name1, Name2 to the end of the record of file1.csv

So, possible result, if a record has more than one match in file2:

"Id","clientName1","clientName2","Name1","Name2","Name1","Name2"

Answer 1

I'm afraid bash may not be the efficient solution but the following bash script would work:

#!/bin/bash

declare -A id_hash

while read line; do
    id=$(echo $line | cut -d ',' -f 1)
    name=$(echo $line | cut -d ',' -f 2-)
    if [ -z "${id_hash[$id]}" ]; then
        id_hash[$id]=$name
    else
        id_hash[$id]=${id_hash[$id]},$name
    fi
done < file1.csv

while read line; do
    id=$(echo $line | cut -d ',' -f 1)
    name=$(echo $line | cut -d ',' -f 2-)
    if [ -z "${id_hash[$id]}" ]; then
        id_hash[$id]=$name
    else
        id_hash[$id]=${id_hash[$id]},$name
    fi
done < file2.csv

for id in ${!id_hash[@]}; do
    echo $id,${id_hash[$id]}
done

Answer 2

In response to the OP's clarification in his/her comment, here is the revised version of the single awk command which does merge in case there was duplicated IDs either in file1 or file2 or in both and if with different number of fields. old version which it works for OP's current stated question

awk -F',' '{one=$1;$1="";a[one]=a[one]$0} END{for (i in a) print i""a[i]}' OFS=, file[12]

For the inputs:

file1

"Id1","clientN1","clientN2"
"Id2","Name3","Name4"
"Id3","client00","client01","client02"
"Id1","client1","client2","client3"

file2

"Id1","Name1","Name2"
"Id1","Name3","Name4"
"Id2","Name0","Name1"
"Id2","Name00","Name11","Name22"

The output is merged file1 and file2 on same IDs:

"Id1","clientN1","clientN2","client1","client2","client3","Name1","Name2","Name3","Name4"
"Id2","Name3","Name4","Name0","Name1","Name00","Name11","Name22"
"Id3","client00","client01","client02"

Answer 3

A regex solution by using join and GNU sed

join -t , -a 1 file[12].csv | sed -r '$!N;/^(.*,)(.*)\n\1/!P;s//\n\1\2,/;D'

assume that both file1.csv and file2.csv are sorted by id and without header

file1.csv

1,c11,c12
2,c21,c22
3,c31,c32

file2.csv

1,n11,n12
1,n21,n22
1,n31,n32
2,n41,n42

gives a result of

1,c11,c12,n11,n12,n21,n22,n31,n32
2,c21,c22,n41,n42
3,c31,c32

---

UPDATE

In case where file1.csv might contain duplicate ids and various field lengths, I would suggest to perform a pre-process to make sure file1.csv is clean before joining with file2.csv

awk -F, '{for(i=2;i<=NF;i++) print $1 FS $i}' file1.csv |\
    sort -u |\
    sed -r '$!N;/^(.*,)(.*)\n\1/!P;s//\n\1\2,/;D'

• the first awk process splits all data into (id, name) pairs
• sort -u sorts and uniques each pairs
• the last sed process merge all pairs with same ids into a single row

input

1,c11,c12
1,c12,c14,c13
1,c15,c12
2,c21,c22

output

1,c11,c12,c13,c14,c15
2,c21,c22

Answer 4

Thanks to all but this has been completed. The code I wrote is below:

#!/bin/bash

echo
echo 'Merging files into one'

IFS=","
while read id lname fname dnaid status type program startdt enddt ref email dob age add1 add2 city postal phone1 phone2

do
var="$dnaid,$lname,$fname,$status,$type,$program,$startdt,$enddt,$ref,$email,$dob,$age,$add1,$add2,$city,$postal,$phone1,$phone2"

  while read id2 cwlname cwfname
  do
       if [ $id == $id2 ]
       then
           var="$var,$cwlname,$cwfname"
       fi

  done < file2.csv

  echo "$var" >> /root/scijoinedfile.csv

done < file1.csv

echo
echo "Merging completed"