Using awk to combine two csv files on three fields too create a new csv file

Question

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
– Depocu, Commented Apr 19, 2016 at 22:08
specifications for your question should be edited into the question and not left as comments (where they might get buried or deleted) — Jeff Schaller
– Jeff Schaller ♦, Commented Apr 19, 2016 at 23:32

Guido · Accepted Answer · 2016-04-19 22:47:17Z

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).

answered Apr 19, 2016 at 22:47

Guido

4,2441 gold badge15 silver badges22 bronze badges

Add a comment |

Nanthini Muniapan · Accepted Answer · 2016-04-19 23:12:52Z

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

Stack Exchange Network

Using awk to combine two csv files on three fields too create a new csv file

2 Answers 2

You must log in to answer this question.

Hot Network Questions

Using awk to combine two csv files on three fields too create a new csv file

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions