Shell script needed for comparing two files

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

Hi,

I need shell script to compare the two files based on certain fields and output should contains the required fields based on result.pls find sample input files and required output

file 1

COUNT, BNG_IP,PORT,OVLAN
    22 ,  10.238.60.129,1/1,2009
    144 ,  10.238.60.129,1/1,2251
      3 ,  10.238.60.129,1/1,2255
     48 ,  10.238.60.129,1/1,2294
     32 ,  10.238.60.129,1/1,2677
    137 ,  10.238.60.129,1/1,2682
     49 ,  10.238.60.129,1/1,2683
     17 ,  10.238.60.129,1/1,2684
    123 ,  10.238.60.129,1/1,2686
     74 ,  10.238.60.129,1/1,2731
      8 ,  10.238.60.129,1/1,2896
     39 ,  10.238.60.129,2/1,2002
      8 ,  10.238.60.129,2/1,2310
      9 ,  10.238.60.129,2/1,2312
      4 ,  10.238.60.129,2/1,2313
     13 ,  10.238.60.129,2/1,2314
      1 ,  10.238.60.129,2/1,2316

File 2

  BNG_IP DSLAM_IP PORT 
10.238.60.129 , 10.232.0.9 , 1/1 , 2009:124
10.238.60.129 , 10.232.2.2 , 2/1 , 2302:122
10.238.60.129 , 10.232.2.3 , 2/1 , 2303:122
10.238.60.129 , 10.232.2.4 , 2/1 , 2304:122
10.238.60.129 , 10.232.2.6 , 2/1 , 2306:122
10.238.60.129 , 10.232.2.7 , 2/1 , 2307:122
10.238.60.129 , 10.232.2.8 , 2/1 , 2308:122
10.238.60.129 , 10.232.2.9 , 2/1 , 2309:122
10.238.60.129 , 10.232.2.10 , 2/1 , 2310:122
10.238.60.129 , 10.232.2.11 , 2/1 , 2311:122
10.238.60.129 , 10.232.2.12 , 2/1 , 2312:122
10.238.60.129 , 10.232.2.13 , 2/1 , 2313:122
10.238.60.129 , 10.232.2.16 , 2/1 , 2316:122
10.238.60.129 , 10.232.2.17 , 2/1 , 2317:122
10.238.60.129 , 10.232.2.18 , 2/1 , 2318:122

Script should compare the first file with second file for BNG_IP,PORT,OVLAN fields if its available in file 2 .it should give the corresponding DSLAM_IP from file2
Required out put will be as below

Output

COUNT, BNG_IP,PORT,OVLAN,DSLAM_IP
 22 ,  10.238.60.129,1/1,2009 ,10.232.0.9
 8 ,  10.238.60.129,2/1,2310,10.232.2.10
  9 ,  10.238.60.129,2/1,2312,10.232.2.12
  4 ,  10.238.60.129,2/1,2313,10.232.2.13
 1 ,  10.238.60.129,2/1,2316,10.232.2.16
2

Any attempts from your side?

---------- Post updated at 23:46 ---------- Previous update was at 23:43 ----------

Am I correctly assuming OVLAN in file2 is the first part of the last field?

3

yes. OVLAN in file2 is the first part of the last field

4

Howsoever, try

awk -F, '
        {gsub (/ /,_)}
NR==FNR {split ($NF, T, ":")
         DSLAM[$1,$3,T[1]]=$2
         next
        }
FNR==1  {print $0 FS "DSLAM_IP"
         next
        }
($2,$3,$4) in DSLAM     {print $0 FS DSLAM[$2,$3,$4]}
' file2 file1
COUNT,BNG_IP,PORT,OVLAN,DSLAM_IP
22,10.238.60.129,1/1,2009,10.232.0.9
8,10.238.60.129,2/1,2310,10.232.2.10
9,10.238.60.129,2/1,2312,10.232.2.12
4,10.238.60.129,2/1,2313,10.232.2.13
1,10.238.60.129,2/1,2316,10.232.2.16
5

Thanks .Its working .Can you pls explain the command in detail.

6

You could try,

awk -F'[,:]' '                                       # separate by a coma or a colon
BEGIN{
    print "COUNT,BNG_IP,PORT,OVLAN,DSLAM_IP"         # start by displaying the header
}
{
    gsub(" ", "")                                    # remove any spaces to sanitize the input
}
FNR==NR {                                            # apply the following block rule to the first file
    I[$2,$3,$4]=$0                                   # make the fields 2,3,4 as an id for the array I and save the whole line in it
    next                                             # stop processing any more blocks and go to read the next record
}
I[$1,$3,$4]{                                         # if it gets here, it is processing the second file. Check there's something in for the key of fields
    print I[$1,$3,$4], $2                            # if an ID matches, display the whole record from file 1 + the added DSLAM_IP from file 2
}' OFS=',' surender_reddy.file1 surender_reddy.file2 # OFS (Output Field Separator) is set to be a coma

COUNT,BNG_IP,PORT,OVLAN,DSLAM_IP
22,10.238.60.129,1/1,2009,10.232.0.9
8,10.238.60.129,2/1,2310,10.232.2.10
9,10.238.60.129,2/1,2312,10.232.2.12
4,10.238.60.129,2/1,2313,10.232.2.13
1,10.238.60.129,2/1,2316,10.232.2.16

---------- Post updated at 05:30 PM ---------- Previous update was at 05:03 PM ----------

awk -F, '                                             # use a coma as input separator
        {gsub (/ /,_)}				      # substitute any white space for an underscore to balance the input										
NR==FNR {split ($NF, T, ":")                          # for the first file, split the last field by colon and save it into an array T
         DSLAM[$1,$3,T[1]]=$2                         # create an array named DSLAM with the id made up from $1,$3,T[1] to save $2
         next                                         # move to the next record and skip the rest if this is file2
        }
FNR==1  {print $0 FS "DSLAM_IP"                       # only for the first line of the second file,since the first file has been processed already. Print header.
         next			                      # skip the rest if it is the record is the first line of the second file at stdin
        }
($2,$3,$4) in DSLAM     {print $0 FS DSLAM[$2,$3,$4]} # if there a key named $2,$3,$5 in the previous made array DSLAM, display current line + coma + content stored in DSLAM[key].
' file2 file1