Bash: Working with CSV file to build a loop and save the result

A pipeline to reduce the number of greps:

grep -rHo --include=\*_meta.xml -f <(awk -F, 'NR > 1 {print $1}' accounts.csv) \
| gawk -F, -v OFS=',' '
    NR == FNR {
      # store the filenames for each email
      if (match($0, /^([^:]+):(.+)/, m)) tally[m[2]][m[1]]
      next
    }
    FNR > 1 {$4 = length(tally[$1])}
    1
  ' - accounts.csv

Here is a solution using single awk command to achieve this. This solution will be highly performant as compared to other solutions because it is scanning each XML file only once for all the email addresses found in first column of the CSV file. Also it is not invoking any external command or spawning a sub0shell anywhere.

This should work in any version of awk.

cat srch.awk

# function to escape regex meta characters
function esc(s,      tmp) {
   tmp = s
   gsub(/[&+.]/, "\\\\&", tmp)
   return tmp
}
BEGIN {FS=OFS=","}
# while processing csv file
NR == FNR {
   # save escaped email address in array em skipping header row
   if (FNR > 1)
      em[esc($1)] = 0
   # save each row in rec array
   rec[++n] = $0
   next
}
# this block will execute for eaxh XML file 
{
   # loop each email and save count of matched email in array em
   # PS: gsub return no of substitutionx
   for (i in em)
      em[i] += gsub(i, "&")
}
END {
   # print header row
   print rec[1]
   # from 2nd row onwards split row into columns using comma
   for (i=2; i<=n; ++i) {
      split(rec[i], a, FS)
      # 6th column is the count of occurrence from array em
      print a[1], a[2], a[3], a[4], a[5], em[esc(a[1])]
   }
}

Use it as:

awk -f srch.awk accounts.csv $(find . -name '*_meta.xml') > tmp && mv tmp accounts.csv

A script that handles accounts.csv line by line and replaces the data in accounts.new.csv for comparison.

#! /bin/bash

file_old=accounts.csv
file_new=${file_old/csv/new.csv}

delimiter=","
x=1

# Copy file
cp ${file_old} ${file_new}

while read -r line; do
        # Skip first line
        if [[ $x -gt 1 ]]; then
                # Read data into variables
                IFS=${delimiter} read -r address foo bar tally somethingelse <<< ${line}

                cnt=$(find . -name '*_meta.xml' -exec grep -lo "${address}" {} \; | wc -l)
                # Reset tally
                tally=$cnt

                # Change line number $x in new file
                sed "${x}s/.*/${address} ${foo} ${bar} ${tally} ${somethingelse}/; ${x}s/ /${delimiter}/g" \
                        -i ${file_new}
        fi

        ((x++))
done < ${file_old}

The input and ouput:

# Input
$ find . -name '*_meta.xml' -exec cat {} \; | sort | uniq -c
      2 [email protected]
      1 [email protected]
$ cat accounts.csv
email,foo,bar,tally,somethingelse
[email protected],bar1,foo2,-1,blah
[email protected],bar2,foo3,-1,blah
[email protected],bar4,foo5,-1,blah

# output
$ ./test.sh
$ cat accounts.new.csv 
email,foo,bar,tally,somethingelse
[email protected],bar1,foo2,2,blah
[email protected],bar2,foo3,1,blah
[email protected],bar4,foo5,0,blah