☰

awk를 사용하여 세 필드에 두 개의 csv 파일을 결합하고 새 csv 파일도 생성합니다.

tag-icon • tag-icon bash shell-script awk
awk를 사용하여 세 필드에 두 개의 csv 파일을 결합하고 새 csv 파일도 생성합니다.

2개의 csv 파일의 필드를 비교하고 출력용 새 csv 파일을 만들고 싶습니다.

2개의 csv 파일이 있습니다. 첫 번째 파일은 내 네트워크의 컴퓨터에서 가져온 것입니다.

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

두 번째 파일에는 동일한 스위치의 동일한 포트에 있는 다른 VoIP 전화가 포함되어 있습니다.

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

두 번째 파일의 $4, $5, $7 필드에 정확히 동일한 값이 있는 경우 파일 2의 VoIP IP 주소를 파일 1의 일치하는 줄 끝에 추가하고 싶습니다.

예:

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

필드 4 = 2ndfloor-c001a-asw1

필드 5 = 기가비트 이더넷 3/1

필드 7 = K022E-NB1-C1

이 줄의 출력은 다음과 같아야 합니다.

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

예제 파일 출력의 예상 출력은 다음과 같아야 합니다.

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

Bash에서 루프를 사용하여 이 작업을 수행했지만 수천 줄이 주어지면 시간이 너무 오래 걸릴 것 같습니다.

내가 사용하는 bash 코드는 다음과 같습니다.

#!/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

논리로서, 필드 4,5,7이 두 파일 모두에서 일치하면 파일 1(모든 필드)을 쓰고, 필드 4,5,7이 일치하면 파일 2(필드 1)를 쓰고, 일치하지 않으면 파일 1(모든 필드)을 씁니다. 그것은 적용되지 않습니다.

awk를 사용하여 수행할 수 있는지 확인하고 싶었습니다.

답변1

다음 awk스크립트를 사용해 보세요.

#!/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

여기서 는 phone.csv예제의 두 번째 파일(VoIP 전화 포함)이고 list.csv첫 번째 파일(PC 포함)입니다.

답변2

나는 이것을 하기 위해 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]]);
     }'

다음과 같은 출력이 생성됩니다.

 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

관련 정보