repeated value in .csv file

Question

everybody, I've got this script that I've used without any problem until now, it uses two files in order to create a .csv, those two files came from another script and it contains information about devices connected to a network, this is how the files look when the error is caused.

file1.dat:

SN: FCQ1632Y0UQ
Estadio_Admon
ip_address: 148.000.000.123

file2.dat:

Device ID: ESTADIO_19
IP address: 148.000.000.119
Interface: FastEthernet0/3
Port ID (outgoing port): GigabitEthernet0

Device ID: ESTADIO_18
IP address: 148.000.000.118
Interface: FastEthernet0/4
Port ID (outgoing port): GigabitEthernet0

Device ID: ESTADIO_16
IP address: 148.000.000.116
Interface: FastEthernet0/6
Port ID (outgoing port): GigabitEthernet0

Device ID: ESTADIO_PALCOS
IP address: 148.000.000.66
Interface: GigabitEthernet0/2
Port ID (outgoing port): GigabitEthernet0/1
SN: FCQ1632Y0US

Device ID: ESTADIO_22
IP address: 148.000.000.122
Interface: FastEthernet0/8
Port ID (outgoing port): GigabitEthernet0

Device ID: SIPCCEF485DE89A
IP address: 148.000.000.92
Interface: FastEthernet0/16
Port ID (outgoing port): Port 1

Device ID: SIPCCEF485DE87B
IP address: 148.000.000.72
Interface: FastEthernet0/13
Port ID (outgoing port): Port 1

Device ID: SIPCCEF485E5719
IP address: 148.000.000.76
Interface: FastEthernet0/17
Port ID (outgoing port): Port 1

Device ID: SIPCCEF485DE894
IP address: 148.000.000.84
Interface: FastEthernet0/14
Port ID (outgoing port): Port 1

Device ID: ESTADIO_TAQUILLAS
IP address: 148.000.000.125
Interface: GigabitEthernet0/1
Port ID (outgoing port): GigabitEthernet1/0/27
SN: FOC1616Y091

script:

awk -v orig=$(awk '$1=="SN:" {print $2}' file1.dat) '
    BEGIN {
        RS = "\n\n"                                               
        FS = "\n"                                                 
        OFS = ","                                                 
        print "Device_SN_O,Device_SN_D,Interface,Port_ID"         
    }                                                             

    {                                                             
        for(i=1; i<=NF; i++) {                                    
            split($i, a, ": ");                                   
            k[a[1]] = a[2]                                        
        }                                                         
        print orig, k["SN"], k["Interface"], k["Port ID (outgoing port)"]
    }' file2.dat>final.csv

expected output:

Device_SN_O,Device_SN_D,Interface,Port_ID
FCQ1632Y0UQ,,FastEthernet0/3,GigabitEthernet0
FCQ1632Y0UQ,,FastEthernet0/4,GigabitEthernet0
FCQ1632Y0UQ,,FastEthernet0/6,GigabitEthernet0
FCQ1632Y0UQ,FCQ1632Y0US,GigabitEthernet0/2,GigabitEthernet0/1
FCQ1632Y0UQ,,FastEthernet0/8,GigabitEthernet0
FCQ1632Y0UQ,,FastEthernet0/16,Port 1
FCQ1632Y0UQ,,FastEthernet0/13,Port 1
FCQ1632Y0UQ,,FastEthernet0/17,Port 1
FCQ1632Y0UQ,,FastEthernet0/14,Port 1
FCQ1632Y0UQ,FOC1616Y091,GigabitEthernet0/1,GigabitEthernet1/0/27

the output I've got:

Device_SN_O,Device_SN_D,Interface,Port_ID
FCQ1632Y0UQ,,FastEthernet0/3,GigabitEthernet0
FCQ1632Y0UQ,,FastEthernet0/4,GigabitEthernet0
FCQ1632Y0UQ,,FastEthernet0/6,GigabitEthernet0
FCQ1632Y0UQ,FCQ1632Y0US,GigabitEthernet0/2,GigabitEthernet0/1
FCQ1632Y0UQ,FCQ1632Y0US,FastEthernet0/8,GigabitEthernet0
FCQ1632Y0UQ,FCQ1632Y0US,FastEthernet0/16,Port 1
FCQ1632Y0UQ,FCQ1632Y0US,FastEthernet0/13,Port 1
FCQ1632Y0UQ,FCQ1632Y0US,FastEthernet0/17,Port 1
FCQ1632Y0UQ,FCQ1632Y0US,FastEthernet0/14,Port 1
FCQ1632Y0UQ,FOC1616Y091,GigabitEthernet0/1,GigabitEthernet1/0/27

As you can see, the Device_SN_D is being repeated until a different one is found, I've used the same script in different iterations and this is the first that gives me this error.

Hope you can help me with this.

File1.dat and File2.dat are always changing, the 'structure' is the same (Supposedly) I will update the question with a succeed output example. — Cesar Alejandro Villegas Yepez, Apr 19 '17 at 15:38
you are right @RakeshSharma there were missed info. but I just edit the post. — Cesar Alejandro Villegas Yepez, Apr 19 '17 at 15:45

score 4 · Accepted Answer · answered Apr 19 '17 at 15:50

When your data doesn't have any "SN: ....." , you do not assign k["SN"] an empty value, so the last value is still in there.

You just need to add a : delete k (see https://unix.stackexchange.com/a/147958/27616) just before processing the next line, so that the next line is processed with a "fresh k array"

For exemple:

awk -v orig=$(awk '$1=="SN:" {print $2}' file1.dat) '
BEGIN {
    RS = "\n\n"                                               
    FS = "\n"                                                 
    OFS = ","                                                 
    print "Device_SN_O,Device_SN_D,Interface,Port_ID"         
}                                                             

{                                                             
    for(i=1; i<=NF; i++) {                                    
        split($i, a, ": ");                                   
        k[a[1]] = a[2]                                        
    }                                                         
    print orig, k["SN"], k["Interface"], k["Port ID (outgoing port)"]
    delete k;  rem="So that the next line is processed with an emptied k array"
}' file2.dat>final.csv

On your provided data it gives in final.csv:

Device_SN_O,Device_SN_D,Interface,Port_ID
FCQ1632Y0UQ,,FastEthernet0/3,GigabitEthernet0
FCQ1632Y0UQ,,FastEthernet0/4,GigabitEthernet0
FCQ1632Y0UQ,,FastEthernet0/6,GigabitEthernet0
FCQ1632Y0UQ,FCQ1632Y0US,GigabitEthernet0/2,GigabitEthernet0/1
FCQ1632Y0UQ,,FastEthernet0/8,GigabitEthernet0
FCQ1632Y0UQ,,FastEthernet0/16,Port 1
FCQ1632Y0UQ,,FastEthernet0/13,Port 1
FCQ1632Y0UQ,,FastEthernet0/17,Port 1
FCQ1632Y0UQ,,FastEthernet0/14,Port 1
FCQ1632Y0UQ,FOC1616Y091,GigabitEthernet0/1,GigabitEthernet1/0/27

as expected

repeated value in .csv file

1 Answers1