phpMyAdmin Notes

I ran across a couple of things when uploading data to our MySql database that kept me puzzled for a few hours. I was trying to load data from the FAA on airports. There are 19,755 airports in an xls file. Last month I converted the records to semi-colon delimited fields and imported them without a problem. This month I wanted to add a few more fields, so I thought I’d just create the new fields and import the data in the same step. I used

INSERT INTO `Airports_NFDC` (`id`, `NFDC_id`, `type`, 
`FAA_id`, `state`, `city`, `name`, `owner`, `access`,
 `lat`, `lon`, `elev`, `var`, `TPA`, `DistCBD`, `DirCBD`,
 `Activation`) 
VALUES

to insert the data. However, it appears that this takes forever and my query would time out without importing any records. (It could be that something else was causing the termination, but I managed to get 5,000 records to import, so I’m reasonably certain that it is a transaction time issue.)

I converted the data to CSV delimited by a ‘;’ and within a minute the file was uploaded. One glitch occurred. It gave me an error on the last record. The last record did not have an end-of-line marker and the importer didn’t know that an end-of-file must mean that the line had ended as well. Inserting a return after the last line fixed the problem.

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.

Creating Vistaprint Mailing Lists

Now that the economy is picking up a bit I’ve been doing a some mailings. There are several postcard printing services out there and I picked Vistaprint because several people I know have used them and because they have Photoshop templates that tell you exactly how the postcards should be laid out to satisfy printing requirements and postal service regulations.

They also have a spreadsheet that you can use for uploading addresses. It’s a bit of a pain to get the names and addresses from my database to the spreadsheet so I thought I’d try the comma separated values method instead. Unfortunately, they have no guidance on how to label the columns but after a bit of trial and error I think I’ve figured it out.

First, I tried using the headings in the spreadsheet, but that didn’t work. Then I looked at a file of rejected addresses from a previous mailing. I noticed that the headings are separated by commas but do not have quotes and do not have the asterisks that they use in the spreadsheet to indicate required items.

This is an example of a successful upload.

First Name,Last Name,Address Line 1,City,State,Zip Code
"ROBERT","BORDEN","33 SEABRING AVE","PISMO BEACH","CA","93445"
"RODNEY R","BROWN","PO BOX 3955","GUADALUPE","CA","93465"

I’m guessing that the order doesn’t matter, but it’s easy to use the same order as the spreadsheet, so that’s what I’ll do. Missing fields are OK—as long as they aren’t the required ones.

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.

Dinner Plans

Dinner Plans…

A group of 40-year-old buddies discussed where they should meet for dinner. Finally, it was agreed that they should meet at Gasthaus Gutenberger because the waitresses there have low-cut blouses and nice breasts.

10 years later, the same group met, and once again, they discuss where they should get together for dinner. Finally, it was agreed that they should meet at the Gasthaus Gutenberger because the food there was very good and the wine selection was excellent.

10 years later, the group met once again, and they discussed where they should have dinner. Finally, they agreed to meet at the Gasthaus Gutenberger because they could eat there in peace and quiet and the restaurant was smoke-free.

10 years later, at 70 years of age, the group met again and once again they discussed where they should meet for dinner. And finally, it was agreed that they would meet at the Gasthaus Gutenberger because the restaurant was wheelchair accessible and had an elevator.

10 years later, the group reconvened, and again they discussed their plans for a reunion dinner. They agreed to meet at the Gasthaus Gutenberger because they have never been there before.