How to parse through csv file with bash

Question

I have CSV file with IP addresses and open ports:

IP,1,3,4,6,7,9,13,17,19,20,21,22,23,24,25,26
1.1.1.2,,,,,,,,,,,open,,,,,
1.1.1.3,,,,,,,,,,,open,,,,,
1.1.1.4,,open ,open,,,,,,,,open,,,,,
1.1.1.5,,,,,,,,,,,open,,,,,
1.2.3.4,,,,,,,,,,,open,,,,,
1.4.5.6,,,,,open,,,,,,open,,,,,
1.4.5.6,,,,,,,,,,,open,,,,,
1.1.3.4,,,,,,,,,,,open,,,,,

For each IP address with an open port, I need to execute a command using both the IP address and the port number of the open port (taken from the CSV header).

Answer 1

Solving this competely in pure bash would, I think, not be advisable. See e.g. the question "Why is using a shell loop to process text considered bad practice?".

Instead, let's make the input data a bit easier to digest.

awk 'FNR == 1 { split($0, port, ","); FS=","; next }
              { for (i=2; i<=NF; ++i) if ($i == "open") print $1, port[i] }' file.csv

This awk command would first read the port numbers from the first line of the CSV file into an array called port. The port number for column N would be stored in port[N].

It does this by splitting the first line of the input on commas and simply storing the result in the port array. This is what the split() command does. The condition FNR == 1 means "If this is the first line of the file, then do this..." (FNR is the line number in the current input file). A code block with no condition on it will be executed for each line of input.

After calling split(), FS, the field separator, is set to a comma. This means that the other lines in the file will automatically be split on commas into fields. This is used in the loop in the second block to loop over the CSV fields (from the second field to the last) on each line.

For each of the other rows in the data, it loops through the comma-delimited fields, and when it finds a field whose value is the string open, it prints out the IP address (the first field) and the corresponding port number.

The output from this command, given the data in the question is

1.1.1.2 21
1.1.1.3 21
1.1.1.4 4
1.1.1.4 21
1.1.1.5 21
1.2.3.4 21
1.4.5.6 7
1.4.5.6 21
1.4.5.6 21
1.1.3.4 21

This can easily be read by a loop in the shell:

while read -r ip port; do
    telnet "$ip" "$port"    # or whatever your command is
done

This reads the IP address and port number, one by one.

To combine these into a complete script:

#!/bin/sh

awk 'FNR == 1 { split($0, port, ","); FS=","; next }
              { for (i=2; i<=NF; ++i) if ($i == "open") print $1, port[i] }' file.csv |
while read -r ip port; do
    telnet "$ip" "$port"    # or whatever your command is
done

The output of the awk command is piped to the while loop which reads the values and calls the command (note the | pipe after the input filename).

Note that if an IP address has more than one port open, the command will be executed multiple times for that address.

Answer 2

Select your first and next respective fields using awk.

awk -d "," -F '{print $1, $n...}