Compare 2 files and match column data and align data from 3 column

Shell Programming and Scripting
1

Hello experts,

Please help me in achieving this in an easier way possible. I have 2 csv files with following data:
File1

08/23/2012 12:35:47,JOB_5330
08/23/2012 12:35:47,JOB_5330
08/23/2012 12:36:09,JOB_5340
08/23/2012 12:36:14,JOB_5340
08/23/2012 12:36:22,JOB_5350
08/23/2012 12:36:26,JOB_5350

File2

JOB_5340,121648
JOB_5350,121648
JOB_5360,243215

Output Required

08/23/2012 12:35:47,JOB_5330	
08/23/2012 12:35:47,JOB_5330	
08/23/2012 12:36:09,JOB_5340,JOB_5340, 121648
08/23/2012 12:36:14,JOB_5340	
08/23/2012 12:36:22,JOB_5350,JOB_5350,121648
08/23/2012 12:36:26,JOB_5350

I have tried to construct this code, and it doesn't produces the exact match on the 4th column. Here is the code and the output...

 awk -F, 'NR==FNR { a[$2]=$0; next  }
>            a[$3] { print $0, a[$3]}
>         ' Aug_23.log.Data_load.csv Aug_23.log.begin_end_times.csv
08/23/2012 12:35:47,JOB_5330 JOB_5490 24
08/23/2012 12:35:47,JOB_5330 JOB_5490 24
08/23/2012 12:36:09,JOB_5340 JOB_5490 24
08/23/2012 12:36:14,JOB_5340 JOB_5490 24
08/23/2012 12:36:22,JOB_5350 JOB_5490 24
08/23/2012 12:36:26,JOB_5350 JOB_5490 24
08/23/2012 13:08:51,JOB_5360 JOB_5490 24
08/23/2012 13:08:58,JOB_5360 JOB_5490 24
2

Hi

awk -F, 'NR==FNR{a[$1]=$2;next}{if ($2 in a){$3=$2;$4=a[$2];delete a[$2];}}1' OFS=, file2 file1

Guru.

3

OR..

awk -F, 'FNR==NR{a[$1]=$0;next}{print $0,a[$NF];delete a[$NF]}' OFS="," file2 file1
4

Hello asnandhakumar,

One more option for you.

awk -F, 'FNR==NR {x[$1]=$0;next} z=($2 in x)?$0","x[$2]:$0 {print z; x[$2]=""}' file2 file1

Regards

1 Like
5

hi Guru,

Thanks for your quick response. Appreciate it.

The given command doesn't produce the required output. Actually, I'm looking at the Job_id in the 2nd column (File1) should match with the Job_id in File(2) and show in the output, as referred in my output section.

Please help me its very urgent

thanks,
NK

6

try

awk  'NR==FNR {Ar[$1] = $0; next}
      $2 in Ar {$(NF+1)=Ar[$2]
                delete Ar[$2]
               }
      1
     ' OFS="," FS="," file2 file1
1 Like