Sep 102013
 

Not to be a shill in this series, but there are vendors that will print addresses on the outer set of wedding invitation envelopes when you place an order (the one the post office uses to deliver mail). Well, at least David’s Bridal does, which is who we happened to use. I’m not going to go so far as to say you should spend money on this kind of thing, just that we decided the convenience was worth it for us, and this is how it played out on our end. David’s Bridal takes address lists in an .xls spreadsheet with a set template. That template is:

Name Address 1 Address 2 Address 3 City State Zip Code International
Name Address Address Address City State Zip Code International

If that looks similar to the guest list from the last post, that’s because it is. In fact, if we only had a version of the guest list without any of the formulas and that didn’t list people by last name, we’d be ready to go. Now, you could go through and manually fix the names, and delete the calculated fields, but to hell with that. We’re developers! We write scripts for this mundane trivial crap! So download your Google Drive guest list as a csv, because here’s the script I wrote to change our guest list over to the David’s Bridal template:

#!/usr/bin/env python

import csv
import sys

def format_name(name):
    """
    Take the name from the source spreadsheet, clean it up and get it
    formatted for the David's Bridal template.

    @param name
    The name from the source spreadsheet.

    @return
    The name in the format ''
    """

    # First, let's get the first and last name parts.

    name = name.split(", ")
    first_name = name[1]
    last_name = name[0]

    # You don't write "and guest" on ANYTHING - filter it out

    first_name = first_name.replace(" and guest", "")

    # "Mr. and Mrs." <one family> is a perfectly acceptable 
    # salutation for a single family. Mr(s). and Mr(s). 
    # <first name> and <first name> <last name> 
    # <last name> is just stupid. Clean that up and then 
    # return the cleaned-up name.

    if "and" in first_name and first_name.split()[1] != "and":

        first_name_part_1 = first_name[0:first_name.index("and")]
        other_name = first_name[first_name.index("and"):]
        return first_name_part_1 + " " + last_name + " " + other_name

    # At this point, we're dealing with a family name, just return
    #  
    return first_name + " " + last_name

abbreviations_to_names = {
                            "": "",
                            "AL": "Alabama",
                            "AK": "Alaska",
                            "AZ": "Arizona",
                            "CA": "California",
                            "CO": "Colorado",
                            "CT": "Connecticut",
                            "DE": "Delaware",
                            "DC": "District of Columbia",
                            "FL": "Florida",
                            "GA": "Georgia",
                            "HI": "Hawaii",
                            "ID": "Idaho",
                            "IL": "Illinois",
                            "IN": "Indiana",
                            "KS": "Kansas",
                            "KY": "Kentucky",
                            "LA": "Louisiana",
                            "MA": "Massachusetts",
                            "ME": "Maine",
                            "MD": "Maryland",
                            "MI": "Michigan",
                            "MN": "Minnesota",
                            "MO": "Missouri",
                            "MS": "Mississippi",
                            "MT": "Montana",
                            "NC": "North Carolina",
                            "ND": "North Dakota",
                            "NE": "Nebraska",
                            "NH": "New Hampshire",
                            "NJ": "New Jersey",
                            "NM": "New Mexico",
                            "NV": "Nevada",
                            "NY": "New York",
                            "OH": "Ohio",
                            "OK": "Oklahoma",
                            "OR": "Oregon",
                            "PA": "Pennsylvania",
                            "RI": "Rhode Island",
                            "SC": "South Carolina",
                            "SD": "South Dakota",
                            "TN": "Tennessee",
                            "TX": "Texas",
                            "UT": "Utah",
                            "VA": "Virginia",
                            "VT": "Vermont",
                            "WA": "Washington",
                            "WI": "Wisconsin",
                            "WV": "West Virginia",
                            "WY": "Wyoming",
                         }

# Start here...

ADULT_NAME_FIELD = 0
FIRST_STREET_ADDRESS_FIELD = 8
SECOND_STREET_ADDRESS_FIELD = 9
CITY_FIELD = 10
STATE_FIELD = 11
POSTAL_CODE_FIELD = 12

NUM_HEADER_ROWS = 1

unformatted_guest_list = open(sys.argv[1], "r")

formatted_file_name = sys.argv[1].split(".csv")[0]
formatted_file_name += "_formatted.csv"
formatted_guest_list = open(formatted_file_name, "w")

reader = csv.reader(unformatted_guest_list, dialect="excel")
writer = csv.writer(formatted_guest_list, dialect="excel")

# Skip over header rows...

for num in len(0, NUM_HEADER_ROWS):
    reader.next()

# Process the names and addresses in the spreadsheet.

for row in reader:

    # We're leading with names, and when there's no name, there's no reason
    # to process the file, so quit the loop. (There's a blank row at the end
    # of the file, so we can get away with this)

    if not row[ADULT_NAME_FIELD]:
        break

    # Make sure nothing untoward is printed in the apartment section if we're
    # mailing to someone not in an apartment.

    output_row = [format_name(row[ADULT_NAME_FIELD]),
            row[FIRST_STREET_ADDRESS_FIELD], row[SECOND_STREET_ADDRESS_FIELD],
            "", row[CITY_FIELD], abbreviations_to_names[row[STATE_FIELD]],
            row[POSTAL_CODE_FIELD]]
    writer.writerow(output_row)

unformatted_guest_list.close()
formatted_guest_list.close()

We’re using the Python’s csv reader and writer to parse out specific fields and write it out to a new file (<Original File Name>_formatted.csv). Most of the “thinking” here is in the format_name() method, where we had several possible variations. We have to deal with:

  1. <Last name>, Mr. and Mrs. <First name>
  2. <Last name>, Mr(s). <First name> and guest
  3. <Last name 1>, Mr(s). <First name 1> and Mr(s). <First name 2> <Last name 2>

The first option here is nice, simple, and straightforward. Drop the comma, switch the 2 parts, and you’re done. The second option isn’t that bad, really. Just look for “and guest”, strip it out, and then you’re back to option 1. It’s option 3 that’s the problem. For this, you need to detect that you’re dealing with 2 different families, then group the names together correctly. The way I found to do that was to replace “and guest” with an empty string and then look for the location of the first instance of the word “and” after the comma. If it’s the 2nd word, I know I’m dealing with option 1, no big deal. Otherwise, I’m dealing with option 3. I know the part before the “and” is associated with the last name before the comma, and everything else is a properly set up first and last name set.

The other hiccup I ran into was state abbreviations. In our guest list, we used abbreviations for states, because we’re normal humans and write addresses as such. Apparently, wedding invitations should never be addressed by normal humans. Those suckers are to be addressed by people who absolutely adore the look of their own handwriting. We were paying to pre-print this stuff because we should not be addressing wedding invitations, but that’s still no reason not to do things “correctly”. So, 1 spite-filled dictionary later (we only mailed invitations to about 5 or 6 states tops, but I still put in all 50 states to prove a point, and so I had it “finished” for if I put it online anywhere).

So that’s a quick and dirty “didn’t-want-to-write-addresses” script for sending a wedding guest list to David’s Bridal. If you have a csv list full of people that you need to pull addresses from and put into a spreadsheet all by themselves, hopefully this gives you a great running start, especially for instances where you’re trying to address families formally. Good luck and happy planning.

 Posted by at 2:16 AM