CODEWITHSUNDEEP
csvawk
scriptdummy
scriptdummy

Reputation: 23

Append columns in csv file by matching the fields with another csv file in bash

I have 2 csv files. File1 is an existing list of private IP address & its hostname. File2 is a daily report which has 8 columns in which 2 containing the private IP. I want to compare file2 with with file1 by matching field 4 and field 7 of file2 with field 2 of file1. Then, upon matching, I want to append field 3 and field 6 of file2 according to the matches of field 4 and field 7 with field 2 of file1.

File1.csv

PC1,192.168.3.1
PC2,192.168.3.2
PC3,192.168.3.3

File2.csv (Has about 50 lines)

Port,Type,S_Host,S_IP,Port,D_Host,D_IP,Port
2,tcp,N/A,192.168.3.1,2,N/A,192.168.3.2,8
3,tcp,N/A,192.168.3.2,2,N/A,192.168.3.3,3

I need to do a bash script to automate file2.

Desired output:

Port,Type,S_Host,S_IP,Port,D_Host,D_IP,Port
2,tcp,PC1,192.168.3.1,2,PC2,192.168.3.2,8
3,tcp,PC2,192.168.3.2,2,PC3,192.168.3.3,3

Upvotes: 0

Views: 354

Answers (1)

Pierre François
Pierre François

Reputation: 6061

If your input files look like this, i.e. the first version, with spaces after the comma:

File1.csv

Host, IP
PC1, 192.168.3.1
PC2, 192.168.3.2
PC3, 192.168.3.3

and:

File2.csv

Port, Type, S_Host, S_IP, Port, D_Host, D_IP, Port
2, tcp, N/A, 192.168.3.1, 2, N/A, 192.168.3.2, 8
3, tcp, N/A, 192.168.3.2, 2, N/A, 192.168.3.3, 3

Try:

#!/bin/bash
awk '
  BEGIN {FS = ", "; OFS = ", "}
  (FNR == NR) && (NR > 1) {hh[$2] = $1}
  NR > FNR {
    if (FNR == 1) 
      print; 
    else 
      print $1, $2, hh[$4], $4, $5, hh[$7], $7, $8;
  }
' File1.csv File2.csv

This is the ouput I get:

Port, Type, S_Host, S_IP, Port, D_Host, D_IP, Port
2, tcp, PC1, 192.168.3.1, 2, PC2, 192.168.3.2, 8
3, tcp, PC2, 192.168.3.2, 2, PC3, 192.168.3.3, 3

Also, if the IP is a public IP, I need to do a whois search instead to get the OrgName

I suggest you to post another question about this second topic. It is like in professional emails: one item = one question.

Upvotes: 1

Related Questions