2

I would like to compare fields in 2 csv file and create new csv file for output.

I have 2 csv files; this first file is PCs in my network:

192.68.205.76,189,FC3F.DB02.ED78,2ndfloor-c001a-asw1,GigabitEthernet3/1,2ndfloor-access,K022E-NB1-C1
192.68.189.231,189,EC9A.7432.D246,2ndfloor-c001a-asw1,GigabitEthernet3/2,2ndfloor-access,K020-CF8-C2
10.68.194.185,1189,9C93.4E2D.EE1A,2ndfloor-c001a-asw1,GigabitEthernet3/3,2ndfloor-access,K022D-NB2-C2
192.68.189.26,189,9C8E.99DD.A49F,2ndfloor-c001a-asw1,GigabitEthernet3/4,2ndfloor-access,K013-EB3-C1
192.68.189.32,189,9C8E.99DE.0BEF,2ndfloor-c001a-asw1,GigabitEthernet3/5,2ndfloor-access,K011-WB1-C1
192.68.189.230,189,EC9A.7435.2177,2ndfloor-c001a-asw1,GigabitEthernet3/6,2ndfloor-access,K024-SB1-C1
192.68.189.21,189,9C8E.99E3.7C33,2ndfloor-c001a-asw1,GigabitEthernet3/7,2ndfloor-access,K024-SB2-C1
192.68.189.34,189,70F3.95C1.11F8,2ndfloor-c001a-asw1,GigabitEthernet3/8,2ndfloor-access,K020-CF7-C1
192.68.194.25,189,F4CE.4613.F9BB,2ndfloor-c001a-asw1,GigabitEthernet3/10,2ndfloor-access,K022D-NB1-C11
192.68.189.36,189,F4CE.4613.F9A5,2ndfloor-c001a-asw1,GigabitEthernet3/14,2ndfloor-access,K004-WB3-C1
10.68.189.182,1189,001C.9B09.0504,2ndfloor-c001a-asw1,GigabitEthernet3/15,2ndfloor-access,K006-NW1-C1
10.68.189.181,1189,9C93.4E16.D940,2ndfloor-c001a-asw1,GigabitEthernet3/17,2ndfloor-access,K004-WB1-C2
192.68.205.77,189,FC3F.DB02.EDBC,2ndfloor-c001a-asw1,GigabitEthernet3/19,2ndfloor-access,K022E-SB1-C1
10.68.189.51,1189,0040.580D.15BC,2ndfloor-c001a-asw1,GigabitEthernet3/20,2ndfloor-access,K024HALL-KRONOS
10.68.189.53,1189,0040.580D.1FC5,2ndfloor-c001a-asw1,GigabitEthernet3/22,2ndfloor-access,K009HALL-EW2-C1(KRONOS)
10.68.189.30,1189,0004.7D0B.3216,2ndfloor-c001a-asw1,GigabitEthernet3/25,2ndfloor-access,PAYST-CC1-C1
192.68.189.181,189,0004.7D24.AE81,2ndfloor-c001a-asw1,GigabitEthernet3/26,2ndfloor-access,K018-CC2-C1
192.165.45.183,1045,0040.1135.7DC6,zhd-04721-asw1,GigabitEthernet1/0/15,zhd-access,04740-WB1-C1(SECURITY)
192.165.45.241,1045,00C0.B792.8DD1,zhd-04721-asw1,GigabitEthernet1/0/25,zhd-access,04721-NETBOT

The second file contains other voip phones on the same switch, same port.

10.29.40.46,1640,F025.7279.6DAA,2ndfloor-c001a-asw1,GigabitEthernet3/1,2ndfloor-access,K022E-NB1-C1
10.29.40.32,1640,1CDE.A783.AAB3,2ndfloor-c001a-asw1,GigabitEthernet3/2,2ndfloor-access,K020-CF8-C2
10.29.40.29,1640,1CDE.A783.EA7B,2ndfloor-c001a-asw1,GigabitEthernet3/4,2ndfloor-access,K013-EB3-C1
10.29.40.43,1640,1CDE.A783.AE1A,2ndfloor-c001a-asw1,GigabitEthernet3/5,2ndfloor-access,K011-WB1-C1
10.29.40.55,1640,0008.2FB7.74D0,2ndfloor-c001a-asw1,GigabitEthernet3/7,2ndfloor-access,K024-SB2-C1
10.29.43.43,1640,1CDE.A782.8539,2ndfloor-c001a-asw1,GigabitEthernet3/10,2ndfloor-access,K022D-NB1-C1
10.29.40.45,1640,0008.2FB7.6F84,2ndfloor-c001a-asw1,GigabitEthernet3/11,2ndfloor-access,K002A-NB1-C1
10.29.40.28,1640,3CCE.73AC.ED44,2ndfloor-c001a-asw1,GigabitEthernet3/12,2ndfloor-access,K022D-NB1-C2
10.29.40.35,1640,0008.2FB7.6F7F,2ndfloor-c001a-asw1,GigabitEthernet3/14,2ndfloor-access,K004-WB3-C1
10.29.40.42,1640,1CDE.A783.B19B,2ndfloor-c001a-asw1,GigabitEthernet3/16,2ndfloor-access,K005-NB1-C1
10.29.40.49,1640,2C3F.38C8.C582,2ndfloor-c001a-asw1,GigabitEthernet3/19,2ndfloor-access,K022E-SB1-C1
10.29.40.51,1640,0CD9.9691.B858,2ndfloor-c001a-asw1,GigabitEthernet3/21,2ndfloor-access,K009HALL-EW1-C1
10.29.40.48,1640,0CD9.9691.B7B6,2ndfloor-c001a-asw1,GigabitEthernet3/23,2ndfloor-access,K009HALL-EW3-C1
10.29.40.192,1640,0040.1135.7FC6,zhd-04721-asw1,GigabitEthernet1/0/15,zhd-access,04740-WB1-C1(SECURITY)
10.29.40.105,1640,00C0.B792.8CD1,zhd-04721-asw1,GigabitEthernet1/0/25,zhd-access,04721-NETBOT

If this second file has the exact same value in fields $4, $5, and $7, I would like to add the voip ip address from file two to the end of the matching line from file 1.

example:

192.68.205.76,189,FC3F.DB02.ED78,2ndfloor-c001a-asw1,GigabitEthernet3/1,2ndfloor-access,K022E-NB1-C1
10.29.40.46,1640,F025.7279.6DAA,2ndfloor-c001a-asw1,GigabitEthernet3/1,2ndfloor-access,K022E-NB1-C1

field 4 = 2ndfloor-c001a-asw1

field 5 = GigabitEthernet3/1

fiedl 7 = K022E-NB1-C1

output for this line should be

192.68.205.76,189,FC3F.DB02.ED78,2ndfloor-c001a-asw1,GigabitEthernet3/1,2ndfloor-access,K022E-NB1-C1,10.29.40.46

Expected output from sample files output should be like this

192.68.205.76,189,FC3F.DB02.ED78,2ndfloor-c001a-asw1,GigabitEthernet3/1,2ndfloor-access,K022E-NB1-C1,10.29.40.46
192.68.189.231,189,EC9A.7432.D246,2ndfloor-c001a-asw1,GigabitEthernet3/2,2ndfloor-access,K020-CF8-C2,10.29.40.32
10.68.194.185,1189,9C93.4E2D.EE1A,2ndfloor-c001a-asw1,GigabitEthernet3/3,2ndfloor-access,K022D-NB2-C2,N/A
192.68.189.26,189,9C8E.99DD.A49F,2ndfloor-c001a-asw1,GigabitEthernet3/4,2ndfloor-access,K013-EB3-C1,10.29.40.29
192.68.189.32,189,9C8E.99DE.0BEF,2ndfloor-c001a-asw1,GigabitEthernet3/5,2ndfloor-access,K011-WB1-C1,10.29.40.43
192.68.189.230,189,EC9A.7435.2177,2ndfloor-c001a-asw1,GigabitEthernet3/6,2ndfloor-access,K024-SB1-C1,N/A
192.68.189.21,189,9C8E.99E3.7C33,2ndfloor-c001a-asw1,GigabitEthernet3/7,2ndfloor-access,K024-SB2-C1,10.29.40.55
192.68.189.34,189,70F3.95C1.11F8,2ndfloor-c001a-asw1,GigabitEthernet3/8,2ndfloor-access,K020-CF7-C1,N/A
192.68.194.25,189,F4CE.4613.F9BB,2ndfloor-c001a-asw1,GigabitEthernet3/10,2ndfloor-access,K022D-NB1-C11,N/A
192.68.189.36,189,F4CE.4613.F9A5,2ndfloor-c001a-asw1,GigabitEthernet3/14,2ndfloor-access,K004-WB3-C1,10.29.40.35
10.68.189.182,1189,001C.9B09.0504,2ndfloor-c001a-asw1,GigabitEthernet3/15,2ndfloor-access,K006-NW1-C1,N/A
10.68.189.181,1189,9C93.4E16.D940,2ndfloor-c001a-asw1,GigabitEthernet3/17,2ndfloor-access,K004-WB1-C2,N/A
192.68.205.77,189,FC3F.DB02.EDBC,2ndfloor-c001a-asw1,GigabitEthernet3/19,2ndfloor-access,K022E-SB1-C1,10.29.40.49
10.68.189.51,1189,0040.580D.15BC,2ndfloor-c001a-asw1,GigabitEthernet3/20,2ndfloor-access,K024HALL-KRONOS,N/A
10.68.189.53,1189,0040.580D.1FC5,2ndfloor-c001a-asw1,GigabitEthernet3/22,2ndfloor-access,K009HALL-EW2-C1(KRONOS),N/A
10.68.189.30,1189,0004.7D0B.3216,2ndfloor-c001a-asw1,GigabitEthernet3/25,2ndfloor-access,PAYST-CC1-C1,N/A
192.68.189.181,189,0004.7D24.AE81,2ndfloor-c001a-asw1,GigabitEthernet3/26,2ndfloor-access,K018-CC2-C1,N/A
192.165.45.183,1045,0040.1135.7DC6,zhd-04721-asw1,GigabitEthernet1/0/15,zhd-access,04740-WB1-C1(SECURITY),10.29.40.192
192.165.45.241,1045,00C0.B792.8DD1,zhd-04721-asw1,GigabitEthernet1/0/25,zhd-access,04721-NETBOT,10.29.40.105

I have completed this with loop in bash but I think it will take long time when I give thousands of line.

bash code I use:

#!/bin/bash
for systems in $(cat list.csv)
do
        phone_ip_final="N/A"
        host_ip=$(echo "$systems" | cut -d, -f1)
        location=$(echo "$systems" | cut -d, -f4,5,7)
        vlan=$(echo "$systems" | cut -d, -f2)
        mac=$(echo "$systems" | cut -d, -f3)
        switchid=$(echo "$systems" | cut -d, -f4)
        switchport=$(echo "$systems" | cut -d, -f5)
        sys_location=$(echo "$systems" | cut -d, -f6)
        desc=$(echo "$systems" | cut -d, -f7)

        for phones in $(cat phones.csv)
                do
                phone_ip=$(echo "$phones" | cut -d, -f1)
                phone_loc=$(echo "$phones" | cut -d, -f4,5,7)
                if [[ "$location" == "$phone_loc" ]]; then
                        phone_ip_final=$phone_ip
                        if [[ "$host_ip" == "$phone_ip_final" ]]; then
                                phone_ip_final="N/A"
                        fi
                        continue
                fi
                done

printf "$host_ip,$vlan,$mac,$switchid,$switchport,$sys_location,$desc,$phone_ip_final\n 
done

As a logic, if field 4,5,7 is match on both file write file1(all fields),file2(field1) if field 4,5,7 does not match write file1(all fields),N/A

I am trying to see if it can be done with awk.

  • as a logic, if field 4,5,7 is match on both file write file1(all fields),file2(field1) if field 4,5,7 does not match write file1(all fields),N/A – Depocu Apr 19 '16 at 22:08
  • 1
    specifications for your question should be edited into the question and not left as comments (where they might get buried or deleted) – Jeff Schaller Apr 19 '16 at 23:32
2

Try this awk script:

#!/bin/sh

awk '
    BEGIN { OFS = FS = "," }
    NR == FNR {
        voip[$4,$5,$7] = $1;
        next;
    }
    {
        if (! (ip = voip[$4,$5,$7])) ip = "N/A";
        print $0, ip;
    }
' phone.csv list.csv

where phone.csv is the second file in your example (containing the voip phones) and list.csv is the first file (containing the PCs).

0

I tried to do this using nawk:

     cat file1.csv |nawk -F, '
    BEGIN { f2="file2.csv"; while(getline line < f2) {split(line,ln,","); file2[ln[4],ln[5] ,ln[7]]=ln[1]; }
    }
    {   split($0,ln2,","); 
        printf("%s,%s,%s,%s,%s,%s,%s,%s\n",ln2[1],ln2[2],ln2[3],ln2[4],ln2[5],ln2[6],ln2[7],file2[ln2[4],ln2[5],ln2[7]]);
     }'

Which produces the following output:

 192.68.205.76,189,FC3F.DB02.ED78,2ndfloor-c001a-asw1,GigabitEthernet3/1,2ndfloor-access,K022E-NB1-C1,10.29.40.46
 192.68.189.231,189,EC9A.7432.D246,2ndfloor-c001a-asw1,GigabitEthernet3/2,2ndfloor-access,K020-CF8-C2,10.29.40.32
 10.68.194.185,1189,9C93.4E2D.EE1A,2ndfloor-c001a-asw1,GigabitEthernet3/3,2ndfloor-access,K022D-NB2-C2,
 192.68.189.26,189,9C8E.99DD.A49F,2ndfloor-c001a-asw1,GigabitEthernet3/4,2ndfloor-access,K013-EB3-C1,10.29.40.29
 192.68.189.32,189,9C8E.99DE.0BEF,2ndfloor-c001a-asw1,GigabitEthernet3/5,2ndfloor-access,K011-WB1-C1,10.29.40.43
 192.68.189.230,189,EC9A.7435.2177,2ndfloor-c001a-asw1,GigabitEthernet3/6,2ndfloor-access,K024-SB1-C1,
192.68.189.21,189,9C8E.99E3.7C33,2ndfloor-c001a-asw1,GigabitEthernet3/7,2ndfloor-access,K024-SB2-C1,10.29.40.55
192.68.189.34,189,70F3.95C1.11F8,2ndfloor-c001a-asw1,GigabitEthernet3/8,2ndfloor-access,K020-CF7-C1,
192.68.194.25,189,F4CE.4613.F9BB,2ndfloor-c001a-asw1,GigabitEthernet3/10,2ndfloor-access,K022D-NB1-C11,
192.68.189.36,189,F4CE.4613.F9A5,2ndfloor-c001a-asw1,GigabitEthernet3/14,2ndfloor-access,K004-WB3-C1,10.29.40.35
10.68.189.182,1189,001C.9B09.0504,2ndfloor-c001a-asw1,GigabitEthernet3/15,2ndfloor-access,K006-NW1-C1,
10.68.189.181,1189,9C93.4E16.D940,2ndfloor-c001a-asw1,GigabitEthernet3/17,2ndfloor-access,K004-WB1-C2,
192.68.205.77,189,FC3F.DB02.EDBC,2ndfloor-c001a-asw1,GigabitEthernet3/19,2ndfloor-access,K022E-SB1-C1,10.29.40.49
10.68.189.51,1189,0040.580D.15BC,2ndfloor-c001a-asw1,GigabitEthernet3/20,2ndfloor-access,K024HALL-KRONOS,
10.68.189.53,1189,0040.580D.1FC5,2ndfloor-c001a-asw1,GigabitEthernet3/22,2ndfloor-access,K009HALL-EW2-C1(KRONOS),
10.68.189.30,1189,0004.7D0B.3216,2ndfloor-c001a-asw1,GigabitEthernet3/25,2ndfloor-access,PAYST-CC1-C1,
192.68.189.181,189,0004.7D24.AE81,2ndfloor-c001a-asw1,GigabitEthernet3/26,2ndfloor-access,K018-CC2-C1,
192.165.45.183,1045,0040.1135.7DC6,zhd-04721-asw1,GigabitEthernet1/0/15,zhd-access,04740-WB1-C1(SECURITY),10.29.40.192
192.165.45.241,1045,00C0.B792.8DD1,zhd-04721-asw1,GigabitEthernet1/0/25,zhd-access,04721-NETBOT,10.29.40.105

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.