Merge the three csv files as one according to first coloumn.

UNIX for Beginners Q & A
1

I have three files with similar pattern i need to merge all the coloumns side by side from all three files according to the first coloumn example as shown below
I mentioned 5 coloumns only in example but i have around 15 coloumns in each file.

file1:

Name,Samples,Error,95RT,90RT
GETcategoryCurrentData,15241,152,0.00%,42
GETproduction-Recommendations,23690,179,0.00%,456
GETstockStatusSkuID,26426,752,0.00%,442
GEThomeCSS,28683,602,0.00%,426

file2:

Name,Samples,Error,95RT,90RT
GETcategoryCurrentData,16241,175,0.00%,47
GETproduction-Recommendations,27090,181,0.00%,490
GETstockStatusSkuID,20006,750,0.00%,440
GEThomeCSS,20613,610,0.00%,430

file3:

Name,Samples,Error,95RT,90RT
GETcategoryCurrentData,75241,167,0.00%,49
GETproduction-Recommendations,78390,190,0.00%,789
GETstockStatusSkuID,66826,790,0.00%,700
GEThomeCSS,21809,612,0.00%,789

sample output:

Name,Samples,Samples,Samples,Error,Error,Error,95RT,95RT,95RT,90RT,90RT,90RT
GETcategoryCurrentData,15241,16241,75241,152,175,167,0.00%,0.00%,0.00%,42,47,49
GETproduction-Recommendations,23690,27090,78390,179,181,190,0.00%,0.00%,0.00%,456,490,789
GETstockStatusSkuID,26426,27090,78390,752,750,790,0.00%,0.00%,0.00%,442,440,700
GEThomeCSS,28683,20613,21809,602,610,612,0.00%,0.00%,0.00%,426,430,789

could any one help me on this.

2

Try:

awk '
  (getline f<fn)>0 && (getline g<gn)>0 {
    split(f,F)
    split(g,G)
    for(i=2; i<=NF; i++)
      $i=$i OFS F OFS G
    print
  }
' FS=, OFS=, fn=file2 gn=file3 file1
3

Above script worked but it is adding first line from the first file,second file, third file as one..
i need it as it should go check go look for ex:"GETcategoryCurrentData" from three files and write it as one row.
i can use sort but if rows are different from one file to other file then they may merge wrongly.

could you please help me in this scenario.

file1:

4

Try this for merging data according to the field 1 values, trying to keep the order of lines to the first file's. It partly takes care of $1 values disappearing or newly occurring, but does not care for the order those appear in the output lines. For more convoluted input cases, additional measures need to be taken.

awk '
!F[$1]  {F[$1]    = $1
         C[++CNT] = $1
        }
        {for (i=2; i<=NF;i++) T[$1,i] = T[$1,i] OFS $i
        }
END     {for (j=1; j<=CNT; j++) {printf "%s", C[j]
                                 for (i=2; i<=NF; i++) printf "%s", T[C[j],i]
                                 printf ORS
                                }
        } 
 ' FS=, OFS=, file[1-3]

Be aware that your sample output file does NOT correctly reflect the input data!

1 Like
5

Thank You..It worked...!