August 10, 2018 • Web

Chills, thrills and comma-separated values

Website: Quill, a Magazine by the Society of Professional Journalists
Client: Society of Professional Journalists
Technologies used: ASP, SQL, WordPress, HTML, PHP, CSS
Contributions: Exported the old site (in spite of itself), built a custom WordPress theme, rigged a couple plugins to display information in a way particular to the theme, imported the old site into the new site without breaking all the old links to stories that exist around the internet.
Demo: Quill website

For reasons both complicated and not terribly interesting, SPJ’s website has been hosted on a locked-in server since before I took the reins, and I have not lobbied successfully to host it somewhere more open, so it’s on me to deal with the peculiarities of this setup instead. Which is fine, because it gives me some interesting (to me; your mileage may vary) challenges to creatively (same) solve.

Take, for instance, SPJ’s magazine, Quill, which has an online as well as physical incarnation. Since its 2001 internet debut, Quill’s story archive has been locked away behind a members-only paywall, which means the database that contained all those stories was hitched to the locked-in website that also hosted SPJ’s membership information. Keeping that archive as a member perk necessitated keeping Quill on SPJ’s proprietary content management system, which was limited to the reader (stories almost never had images, even, unless we hand-coded them in) and a total pain to the folks on staff who had to add the magazine’s stories to that archive. Nobody won — not even members, who never accessed those older stories enough to justify them as a value add.

After years of unsuccessful lobbying, I finally received clearance to open that archive to all, not just members, which also meant I could host Quill anywhere and on any platform — provided I can export it off its existing proprietary platform without breaking the thousands of links pointing to Quill stories from all over the web.

The process wasn’t pretty, but the result looks rather nice. I hacked together a script to dump the entirety of the Quill database into a single CSV (comma-separated values; think spreadsheet but less attractive) file, created an empty WordPress database, reformatted the CSV file so that the data in the old proprietary database lined up perfectly with what WordPress would be looking for (including each post’s unique ID number, which would have to be identical to the ID number it had in the old database), and imported that CSV file into the WordPress database using SQL commands. (I warned you twice that this might not be terribly interesting, so not my fault if you’re bored but still with me here.)

Everything imported successfully, completing the hard part of the job, though I still had to manually match each story with its correct author because no import/export process could simplify that with the way that data appears in WordPress’s database. But after that, not only was 17+ years of stories available in the new Quill, but — thanks to a little script I wrote on SPJ’s web page — any time someone would visit a years-old Quill link, our site could use that unique ID number to send them to the same story on the new site without them ever knowing the difference. Like Arnold in that one Terminator 2 scene, I conquered the project without a single dead link as a casualty, which should always be the goal of any website migration, no matter how unwieldy.