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

Question

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

Welcome to SO, on SO we encourage users to add their efforts which they have put in order to solve their own problems so kindly do add the same in your question and let us know then. — RavinderSingh13
Please add your desired output (no description) for that sample input to your question (no comment). — Cyrus
Read file1 into an array in awk (indexed by IP) then process file2 and lookup the hostname using the IP from fields 4 & 7 and set fields 3 & 6 equal to the resulting hostnames and print the record. — David C. Rankin

Pierre François Pierre François · Accepted Answer · 2020-05-08T10:36:48

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.

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

1 Answers