Usando o awk para combinar dois arquivos csv em três campos também criar um novo arquivo csv

Navegue suas respostas
2

Gostaria de comparar campos em 2 arquivos csv e criar novo arquivo csv para saída.

Eu tenho 2 arquivos csv; este primeiro arquivo é um PC na minha rede: 

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

O segundo arquivo contém outros telefones voip no mesmo switch, a mesma porta. 

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

Se este segundo arquivo tiver o mesmo valor exato nos campos $ 4, $ 5 e $ 7, eu gostaria de adicionar o endereço IP voip do arquivo dois ao final da linha correspondente do arquivo 1.

exemplo: 

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

campo 4 = 2º andar-c001a-asw1

campo 5 = GigabitEthernet3 / 1

fiedl 7 = K022E-NB1-C1

saída para esta linha deve ser 

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

A saída esperada da saída de arquivos de amostra deve ser assim 

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

Eu completei isso com loop no bash mas acho que levará muito tempo quando eu der milhares de linhas.

código bash que eu uso: 

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

Como lógica, se o campo 4,5,7 for correspondente no arquivo write file1 (todos os campos), file2 (field1) se o campo 4,5,7 não corresponder ao arquivo de gravação1 (todos os campos), N / A

Estou tentando ver se isso pode ser feito com o awk.

    
por Depocu 19.04.2016 / 23:52

2 respostas

2

Experimente este script 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

onde phone.csv é o segundo arquivo em seu exemplo (contendo os telefones voip) e list.csv é o primeiro arquivo (contendo os PCs).

    
por 20.04.2016 / 00:47
0

Eu tentei fazer isso usando 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]]);
     }'

Que produz a seguinte saída: 

 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
    
por 20.04.2016 / 01:12

Tags

Systemd: Como iniciar / parar o script Python que deve ser executado em segundo plano, dentro do Virtualenv O que faz “sudo su -s / bin / bash - username”?