Cleaning Up Addresses-Capitalization

Capitalizing words using BBEdit catches everything you want to change in the address field, but also picks up Carrier Route (Cr), Rural Route (Rr), Mail Stop (Ms), and PO Box (Po Box) so you need to manually change them back when you are done. There are also some Cb’s in my database but I don’t know what it stands for. You should be safe if you do a case sensitive search and replace for all but Cr. I only have a few Cr’s in my database so I looked at them and changed them manually. Look for [space]Cr[[space] and [beginning of line]Cr. Then you need to replace Ne, Se, Nw, Sw when they are at the end of a line or when they have a space after them. For highways, need to change Us to US. You’re going to miss a bunch of states as well.

How to tell if someone is a programmer.

When you say “Hello”, they answer ”World”.

They use nested parentheses in normal writing (at least I do (sometimes)).

They are wearing socks (and sandals).

Ask them what languages they know.
You can tell the programmer by the way he names numerous langauges but forgets to include ‘English’.

If you ask them a question there is a pause as they break out of the for loop.

If you ask them whether they use Mac or Windows, the answer is ”Usually not.”

Faking Out The Vistaprint List Importer

One of the things that made us try out Vistaprint was the extremely well documented image format requirements. The same cannot be said of the mailing list formats. After some experimentation, I think I’ve found out how they turn your data into mailing labels.

If you have a simple customer list, using their .xls template should work fine. Export your data from you database, import it into another sheet and past the info into the appropriate columns. If you want to use comma separated lists—and your info is database matches their format—it is fairly stratghtforward. Just remember to use the .csv suffix on your file. You should also save the file as ‘Western ISO Latin’ so that it picks up special characters from other languages, like ñ, é, á, etc. Their algorithm detects Mac, Windows, and Linux line endings so you don’t have to worry about them.

I’ve been working on the same customer database since 1994. We’ve copied and pasted names and addresses form email and webs sites and added entries by hand. In the process lots of unprintable characters made their way into the database. My text editor (BBEdit) can remove the ‘gremlins’ so if you have a text editor that can zap them you should, otherwise you’ll have a bunch of records rejected without knowing why. If you can view invisible characters, you might want to do that as well to catch anything that wasn’t zapped.

The header line is not the same as in the .xls file. It should look like this—but all on one line:

Salutation,First Name,Middle Name,Last Name,Title,   
Company Name,Address Line 1,City,State,ZIP Code

You can leave out fields that you do not use.

The labels will print the info in the following order
Salutation,First Name,Middle Name,Last Name
Title
Company Name
Address Line 1
City,State,ZIP

You can also use Address Line 2 but I can’t figure out the rules for using two address lines so I don’t recommend using it.

My main customer database doesn’t match their format so it took a bit of experimenting to fake out their input algorithm. I noticed that you can put anything you want into the fields before Address Line 1 and it will print it in the order that it appears.

My database has a customer number, contact name—first name then last and sometimes initials for degrees or credentials, sometimes it has a company name, and company names often run over to a second line. Some companies have a department or building that is entered on the second line. I always have one street address line, city, state, and zip code so they aren’t a problem.

Their algorithm cares about the address, city, state, and zip but doesn’t check the data in the first fields. So I put the customer number into Salutation. the full contact name into Last Name, the first line of the company into the Title, and the department or second line of the company name into Company Name. The labels print out correctly.

This is what my data looks like:

Salutation,Last Name,Title,Company Name,
"487","Grey, Harrison A PhD","Community Speech & Hearing Ctr",""
"43","Arlene Johnson","Adult & Pediatric","Speech And Language"
"345" "Judith Krantner","Mankato State University","Communication Disorders"
"2490","Mary Patricia Connelly","University Of Florida","P K Young Dev Research School"
"2662","Julie Blaine, SLP","","",

Update: People have been buying my Spanish-language apps and I wanted to send a mailing to them. Unfortunately, VistaPrint mangles the non-ASCII characters in their names and addresses, e.g. ñ, é, á. It turns out that if you encode your .csv file as Western (ISO Latin 9) instead of Unicode (UTF 8) it works fine.