Now let's apply what we've learned by solving a problem using a hypothetical scenario. John Starbird, the marketing director at your company would like to do a monthly mail merge for an e-mail newsletter and for alerting customers to product recalls. He keeps the customer list in Goldmine and agrees to do a monthly export into CSV format.

We'll start out with a script that reads in the CSV file and sends an e-mail from a pre-written file. Also, we'll assume that the CSV format is the same as the sample we used above:

"Name","Email","Phone","City","State","Zip"
"Jonathan Eisenzopf","eisen@pobox.com", "703-555-1212","Reston","VA",20191
"John Bigboote","bigboote@yoyodyne.com", "703-555-1213","Fairfax","VA",20814

Below is the script that I've written that sends an e-mail from the message.txt file using the names and e-mail addresses from the text2.txt CSV file.

1  use Mail::Send;
2  open(CSV,"test2.txt") or die "Cannot open test2.txt for read\n";
3  open(MESSAGE,"message.txt") or die "Cannot open message.txt
    for read\n";
4  my $message = join ('',<MESSAGE>);
5  my $string = <CSV>;
6
7  while (<CSV>) {
8      my @list = split (/,/);
9      s/^"|"$//g foreach @list;
10     my $msg = new Mail::Send (To      => "$list[0] <$list[1]>",
11 			         Subject => "Yoyodyne Newsletter");
12     my $fh = $msg->open;
13     print $fh $message;
14     print "$list[0] <$list[1]>\n";
15     $fh->close;
16 }

[The red lines above are one line. They have been split for formatting purposes.]

You'll notice on Line 1 that we will utilize the Mail::Send module to actually send the e-mails. In Lines 2 and 3, we open the CSV and message files for read access and assign the CSV and MESSAGE file handle to them respectively. On Line 4, we perform a one line trick utilizing the join() function.

The join() function is another useful text processing routine built into Perl. It's actually the opposite of the split() function. Instead of passing the delimiter to split on, we pass the character that we will be using as the delimiter when we fuse multiple items into one string. This character should be placed between two single quotes. On Line 4, there isn't any character between the two single quotes, because in this case, we don't want one. The second argument in the join() syntax is a list of items to join. This can be a comma separated list of individual variables or an array. In our case, we're passing the MESSAGE filehandle, which is in fact treated like an array. Each line of the file is one element of the array. This is perfectly legal in Perl and serves as a short way to read a file into a single variable, in this case the $message variable.

On Line 5, we read the first line of the CSV file into a variable that we aren't going to use. That's because the first line contains the field names, but no values, so we simple toss it.

Lines 7 through 16 contain the while() operator that loops over each line of the CSV file and sends the message to the recipient specified in the record. Line 8 calls the split() function on the line. Since we didn't pass a string as a second argument, it defaults to $_, which contains the current line of the file. The first parameter specifies that the delimiter to search for is a comma. Line 9 contains the search and replace operator that removes the double quotes from the beginning and ending of the string. Lines 10 to 15 send the message using the Mail::Sender module.

Merging Multiple CSV Files

John Littleberries, the sales director, also has a list of customer prospects that he would like to include in mailings. After examining the contents of a few test exports, you discover that there are a number of discrepancies you'll have to deal with. First, John Starbird's CSV file has the first and last name in one field while the first and last name are in separate fields in John Littleberries' file. Also, John Littleberries' file has the fields in a different order. You also notice that John Littleberries seems to alternate between using a comma and colon as a delimiter. You've tried explaining that you need a common delimiter, but you're not sure he's caught on yet. A sample of the data follows:

"Email","First Name","Last Name"
"buckaroo@buckaroobanzai.com","Buckaroo","Banzai"
"tommy@buckaroobanzai.com","Perfect","Tommy"

Ok, not a big deal, we can modify the script to bring in the right fields in the right order and join John Littleberries' two name fields into one after we split each line and also accept both the comma and colon delimiters.

1  use Mail::Send;
2  open(CSV1,"test2.txt") or die "Cannot open test2.txt for read\n";
3  open(CSV2,"test3.txt") or die "Cannot open test3.txt for read\n";
4  open(MESSAGE,"message.txt") or die "Cannot open message.txt
    for read\n";
5  my $message = join ('',<MESSAGE>);
6  my $string = <CSV>;
7
8  while (<CSV1>) {
9      my @list = split (/,/);
10     s/^"|"$//g foreach @list;
11     my $msg = new Mail::Send (To      => "$list[0] <$list[1]>",
12 			         Subject => "Yoyodyne Newsletter");
13     my $fh = $msg->open;
14     print $fh $message;
15     $fh->close;
16 }
17
18 while (<CSV2>) {
19     my @list = split (/,|:/);
20     s/^"|"$//g foreach @list;
21     my $msg = new Mail::Send (To      => "$list[1] $list[2]
        <$list[0]>",
22 			         Subject => "Yoyodyne Newsletter");
23     my $fh = $msg->open;
24     print $fh $message;
25     $fh->close;
26 }

[The red lines above are one line. They have been split for formatting purposes.]

On Line 3, we added a line to open the second CSV file. We've also added Lines 18 through 26, which are identical to Lines 8 through 16 except for Line 19, which adds the ability to match a colon as well as a comma delimiter and Line 21, which reorders and combines the fields based on the different order and combination.

Well, now that we've got that solved, we're ready to go. Unfortunately, John Starbird sent an e-mail this morning with the following message:

Processing Text with Perl Functions - Page 6
Weaving Magic With Regular Expressions
Merging and Cleaning Data - Page 8