Reading 2 CSV files and filtering data based on group

Shell Programming and Scripting
1

I have two CSV files in the following format:
First file:

GroupID, PID:TID, IP, Port

Sample data:

0,1000:11,127.0.0.1,445
0,-1:-1,127.0.0.1,800
1,1000:11,127.0.0.1,445
1,-1:-1,127.0.0.1,900
2,1000:11,127.0.0.1,445
2,-1:-1,180.0.0.3,900

Second file:

IP,Port,PID

Sample data

127.0.0.1,445,1000
127.0.0.1,800,1000
127.0.0.1,900,2000

What I need to do is as follows:

  1. in the first file, whereever I have PID as -1, I need to pick its IP and port and llok it up in 2nd file and replace the -1 PID with the PID from 2nd file
  2. Now based on the groupid (we are sure that there would be exacty 2 records with same group id), ignore all those groups where we have the same PID or if any PID is still -1 (ip port not found in 2nd file)

So in the above sample data:
first step will lead us to

0,1000:11,127.0.0.1,445
0,1000:-1,127.0.0.1,800
1,1000:11,127.0.0.1,445
1,2000:-1,127.0.0.1,900
2,1000:11,127.0.0.1,445
2,-1:-1,180.0.0.3,900

and the second step should get us the desired output

1,1000:11,127.0.0.1,445
1,2000:-1,127.0.0.1,900

We do not need the file after 1st step but that is just for explanation.

2

There might be better ways. But, try:

awk -F, 'FNR==NR{a[$1,$2]=$3;next}
{split($2,b,":")
if(b[1]==-1 && ($3,$4) in a) b[1]=a[$3,$4]
$2=b[1] ":" b[2]
temp = (length(temp))?(temp RS $0):($0)
pid[FNR]=b[1]
}!(FNR%2){
if(pid[FNR]!=-1 && pid[FNR-1]!=-1 && pid[FNR]!=pid[FNR-1]) print temp;temp=""}' file2 OFS=, file1
3 
awk -F, 'FNR==NR{a[$2]=$3;next}
 {print a[$4]}' OFS=, file2.csv file1.csv

Thanks for your answer. I was trying this but even this is not working and it prints 6 empty rows. Can you please help?

4

I did not provide this solution!!!

5

Yes I know. I tried the solution you gave but it didn't work. So I was trying myself in small steps and that too didn't work. Can you please check? Thanks in advance.

6

The solution that I've provided works perfectly fine with your sample inputs.
Tested with the awk on AIX 6.1 and gawk on cygwin.
Which awk version are you using?
Is there any white-space in any of the fields of file1 and file2?
Would you post the output of head -2 <file>|od -bc of your 2 files?

7

I found the issue. the row separator was somehow \r\n which was causing the issue. The moment we used

 RS="\r\n"

, it worked for us.

Thanks a lot for your help.