How to split single rows of a CSV file into multiple depending on delimiter in 2nd column

Question

My base.csv file has several entries a sample of which is below.

NAME,IP_ADDRESS,PRI_IP,APP_CODE,APP_NAME,OWNER
test2,10.9.7.12/10.222.101.11,10.9.9.12,545,myapp1,owner1
mytest,10.9.7.12,10.9.9.12,545,myapp1,owner9
checkthis,10.9.7.12/10.222.101.11/10.3.2.111,10.9.9.12,545,myapp1,owner7

Note: the second column is always a single or multiple IP address seperated by delimiter /

I wish to create newbase.csv with second colum always having only a single ip.

Thus the desired newbase.csv would be like

test2,10.9.7.12,10.9.9.12,545,myapp1,owner1
test2,10.222.101.11,10.9.9.12,545,myapp1,owner1
mytest,10.9.7.12,10.9.9.12,545,myapp1,owner9
checkthis,10.9.7.12,10.9.9.12,545,myapp1,owner7
checkthis,10.222.101.11,10.9.9.12,545,myapp1,owner7
checkthis,10.3.2.111,10.9.9.12,545,myapp1,owner7

Below give me the desired columns that i need but does not split rowns containing multiple ips in the second column into multiple rows.

cat -- base.csv | cut -d, -f2-5 > newbase.csv

Can you please suggest ?

Answer 1

You could use the Miller nest function to explode values across records:

$ mlr --csv nest --explode --values --across-records --nested-fs '/' -f IP_ADDRESS base.csv
NAME,IP_ADDRESS,PRI_IP,APP_CODE,APP_NAME,OWNER
test2,10.9.7.12,10.9.9.12,545,myapp1,owner1
test2,10.222.101.11,10.9.9.12,545,myapp1,owner1
mytest,10.9.7.12,10.9.9.12,545,myapp1,owner9
checkthis,10.9.7.12,10.9.9.12,545,myapp1,owner7
checkthis,10.222.101.11,10.9.9.12,545,myapp1,owner7
checkthis,10.3.2.111,10.9.9.12,545,myapp1,owner7

With more recent versions, --explode --values --across-records --nested-fs '/' may be abbreviated to --evar '/' so

mlr --csv nest --evar '/' -f IP_ADDRESS base.csv >newbase.csv

Alternatively, with awk:

awk -F, '
  BEGIN{OFS=FS}
  {
    n = split($2,a,"/")
    for(i=1;i<=n;i++) {
      $2=a[i]; print
    }
  }
' base.csv >newbase.csv