Remember the mail merge script I started writing a while back? Yeah, that was quite some time ago. I got sidetracked with the Linux Journal Anniversary special issue (see my article "Back in the Day: UNIX, Minix and Linux"), and then I spun off on a completely different tangent for my last article ("Breaking Up Apache Log Files for Analysis"). I blame it on...
SQUIRREL!
Oh, sorry, back to topic here. I was developing a shell script that would let you specify a text document with embedded field names that could be substituted iteratively across a file containing lots of field values.
Each field was denoted by #fieldname#
, and I identified two
categories of fieldnames: fixed and dynamic. A fixed value
might be #name#
, which would come directly out of the data
file, while a dynamic value could be #date#
, which would be
the current date.
More interesting, I also proposed calculated values,
specifically #suggested#
, which would be a value calculated
based on #donation#
, and #date#
, which would be replaced by
the current date. The super-fancy version would have a
simple language where you could define the relationship between
variables, but let's get real. Mail merge. It's just mail
merge.
It turns out that the additions needed for this script aren't too difficult. The basic data file has comma-separated field names, then subsequent lines have the values associated with those fields.
Here's that core code:
if [ $lines -eq 1 ] ; then # field names
# grab variable names
declare -a varname=($f1 $f2 $f3 $f4 $f5 $f6 $f7)
else # process fields
# grab values for this line (can contain spaces)
declare -a value=("$f1" "$f2" "$f3" "$f4" "$f5" "$f6" "$f7")
The declare
function turns out to be ideal for this,
allowing you to create an array varname
based on the
contents of the first line, then keep replacing the values
of the array value
, so that varname[1] =
value[1]
, and so
on.
To add the additional variables #date#
and
#suggested#
, you
simply can append them to the varname
and
value
arrays. The
first one is easy, but it did highlight a weakness in the
original code that I had to fix by adding quotes as shown:
declare -a varname=("$f1" "$f2" "$f3" "$f4" "$f5"
"$f6" "$f7" "date" "suggested")
The f1–f7 values needed to be quoted to ensure that there
always are the same number of values in the varname
array
regardless of actual value (if any).
Adding the values to the value
array is a smidge more
tricky because you actually need to calculate values. Date
is easy; it can be calculated once:
thedate=$(date "+%b %d, %Y")
Calculating the suggested value—donation/2
—is also
fairly easy to accomplish, but must be done within the main
loop so that it changes for each letter being sent. The
original donation amount in the demo is field 3, so the
necessary code is:
# amount=f3, so suggested=(f3/2)
suggested="$(( $f3 / 2 ))"
The main block of code doesn't require any changes at all, fortunately, so with just those few tweaks, you now can use the mail merge script to generate, yes, a fully customized email message:
$ subs.sh
------------------------
Apr 13, 2019
Dear Eldon Tyrell, I wanted to start by again thanking you
for your generous donation of $500 in July. We couldn't do
our work without support from humans like you, Eldon.
This year we're looking at some unexpected expenses,
particularly in Sector 5, which encompasses California, as
you know. I'm hoping you can start the year with an
additional contribution? Even $250 would be tremendously
helpful.
Thanks for your ongoing support.
Rick Deckard
Society for the Prevention of Cruelty to Replicants
Notice that date
and suggested
are both replaced with
logical values, the former showing the current date in a
pleasant format (the date format string, above), and the
suggested value as 50% of the donation.
The biggest bug that's still in the script at this point is that although the donors source list has more than one donor listed, the script actually only ever shows results for that first donor and then quits.
To debug this part, let's look at just the key lines in the main loop:
while IFS=',' read -r f1 f2 f3 f4 f5 f6 f7
do
if [ $lines -eq 1 ] ; then # field names
# grab variable names
declare -a varname=("$f1" "$f2" "$f3" "$f4" "$f5"
"$f6" "$f7" "date" "suggested")
else # process fields
. . .
echo "------------------------"
exec $sed "$SUBS" $inputfile
fi
done < "$datafile"
Can you see the problem here? In a burst of enthusiasm for
efficient coding and fast execution, the script actually
commits a sort of digital seppuku with an exec
call instead
of just running the sed
and continuing the loop.
Oops. My bad!
The solution is simply to remove the word exec
from the
loop, and it suddenly works exactly as desired. The problem
then is how do you split out all the individual letters?
Having it all stream out as one long sequence of text is
rather useless.
There are a number of possible solutions, but I'm going to
create individual files based on the donor's name. Since
that value is $f1
once the data has been parsed, this is
easy:
outfile="$(echo $f1 | sed 's/ /-/g')-letter.txt"
echo "Letter for $f1. Output = $outfile"
$sed "$SUBS" $inputfile > $outfile
You can see that the outfile value is composed by replacing
all spaces with dashes, and the subsequent echo
statement
offers a status output. Finally, the actual sed
invocation
now eschews the evil exec
call (okay, it's not evil) and
adds an output redirect.
Here's the source donor file:
$ cat donors.txt
name,first,amount,month,state
Eldon Tyrell,Eldon,500,July,California
Rachel,Rachel,100,March,New York
Roy Batty,Roy,50,January,Washington
And, here's what happens when the script is run:
$ sh bulkmail-subs.sh
Letter for Eldon Tyrell. Output = Eldon-Tyrell-letter.txt
Letter for Rachel. Output = Rachel-letter.txt
Letter for Roy Batty. Output = Roy-Batty-letter.txt
Great. Now, what about one of those letters? Let's see what you'd be sending that rich head of industry, Eldon Tyrell:
$ cat Eldon-Tyrell-letter.txt
Apr 13, 2019
Dear Eldon Tyrell, I wanted to start by again thanking you
for your generous donation of $500 in July. We couldn't do
our work without support from humans like you, Eldon.
This year we're looking at some unexpected expenses,
particularly in Sector 5, which encompasses California, as
you know. I'm hoping you can start the year with an
additional contribution? Even $250 would be tremendously
helpful.
Thanks for your ongoing support.
Rick Deckard
Society for the Prevention of Cruelty to Replicants
Solved—and neatly too. Now, what would you do differently or add to make this script more useful? Without vast overkill, of course.
In my next article, I plan to take an entirely different direction. I'm not sure what, but I'll come up with something.