Email — you may be addicted to it, you may hate it, abuse it, love it, or eschew it. Whatever your relationship, troubled or otherwise, email is and continues to be one of the world’s few, new, great things. When it comes to “killer-apps,” it is the undefeated heavy-weight champion of the world. Email is the backbone of social and commercial intercourse. Commerce flows through it, along with pain and joy, and work and play, and many of the hours of my day.
While you may order that inflatable, remote-controlled zeppelin online, the acknowledgement nevertheless comes via email, as does the receipt, and the shipping updates.
Email is the truck that moves freight – light and heavy – on the information-super-goat-trail. Plain, simple, elegant, boring, your-grandma-has-an-AOL-address-type email remains the venerable heavy lifter of the online world.
Strangely, it has also become the de facto identity management tool. It is universally used to authenticate just who we are, on everything from my bank to the myriad of social and anti-social real-time networking sites. When we forget just who we are, it’s the delivery method of choice to jog the memory or to trigger a reset — ironically, given how totally insecure it really is, likened to a postcard.]
But, the core problem with email is not security. The real problem with email is it’s really stupid. It’s dumb as a bucket of overripe bananas. I mean it. It’s really god-awful stupid. It can’t help it. It was designed that way.
When push comes to pull, with email, you really don’t get much, and that illustrates its frailty and its amazing functionality.
With email, you see, all you really get is an “envelope” (consisting of minor variations on “From,” “To” a “Subject” along with tiny little bits of routing data that nobody pays attention to) and a giant blob of undifferentiated stuff called a “message body.” What’s in that message body is anything, unstructured, and undifferentiated – a blob.
What’s in there could be secret silly croonings to your one-true love, it could be the confirmation of your getaway flight to a land without extradition, or it could be my secret recipe for the world’s best gazpacho (and hence your necessary and immediate flight from justice).
With email, the medium hides the message. (If I keep this up, I’m likely to be haunted by McLuhan.)
Since its launch in 1971, we have improved it. We’ve tweaked it and twiddled it. We’ve made it better, making it easy, for example, to stuff it with bits of binary. We’ve said goodbye and good riddance to Uuencode and its ilk. Now digital civilians needn’t know a MIME type from a mime troupe. We’ve prettied it up, too — love it or hate it — with HTML, providing that ever-so-useful ability to deliver ugly fonts, in all the sizes, shapes, and colours your little heart could desire, rendering it pretty much unreadable
[For the record: I think I sent my first in the fall of 1979, using a service called EIES. I've got a copy of it around here someplace. It was a message to the fellow at the desk next to me, suggesting we get lunch at the Burrito King (tacos al carbon); truly important stuff!]
And, so, email moves the world, moving commerce and confirmations, in the wink of an eye. We’ve filled the tubes with everything from solicitations for various dysfunctional systems (whether erectile or congressional), to orders for green tea, multiple drafts, rewrites and painful iterations of your latest annual report, and those important PDF copies of your sinister plans for global domination through puppy adoption. They’re all sent on their respective ways via email.
Email is the go-to tool for everything from “donuts in the kitchen,” to presidential elections. But, inside, it’s still one dumb blob.
Blob, meet the software equivalent of Steve McQueen: Email2DB– one magnificent tool.
Steve McQueen (saving diners) in the Blob!
Email2DB has become a necessary cog in my machinery. It lets me take those dumb blobs and ferret out the necessary bits and pieces of the message, shaping them, cleaning them up, adding value in terms of structure, and then, gently slipping that data into a giant database.
In the best of all possible worlds, I wouldn’t have to do this. In that world, I wouldn’t be tasked with figuring out what to do with thousands email messages sent willy-nilly to just about any email address, person, or inanimate object you might care to imagine. But I do.
I am charged with capturing and organizing thousands of inquiries — inquiries that arrive in every way imaginable, some via a web form, others via email, and still others via such unspeakable things as (shudder) fax. I even think a few get slipped under the door at night by pixies. They’re all important, and regardless of origination, I want them all to end up in the same place — a database. Despite their disparate origins, I want them all channeled into the waiting, eager programmatic minds for review. I am all about the smooth flow of information. My motto: Never, ever, type it twice.
Email2DB keeps me true to my motto. In a nutshell, Email2DB “deconstructs” the email. It breaks it into its constituent parts, slicing and dicing the blob, parsing not only the header, but the contents, and gently slipping those deconstructed pieces into the database of your choice – in my case, the same-same database used to capture the content entered via a fancy online web form. Derrida would be proud.
Well known parts of the email —like the TO, FROM, SUBJECT, DATE — as well as some of the arcane bits and pieces of the underlying protocol (Originating IP, MessageID, ReplyTo) are a breeze to deconstruct.
They’re “pre-programmed” into the software, and with a single mouse click, you can pull those wee bits apart and slide them into a database. It talks to all-comers: Access, SQL Server, Oracle, MySQL, Access, ODBC, yada yada yada. It’ll even write it out as a CSV if you’re living in 1996.
Once you’ve extracted the pieces you can use them for nefarious purposes: perhaps to construct a new message, sending back, for example, custom acknowledgments, or forwarding on reformatted confirmations, or simply adding them into a database for further processing. Email2DB takes email and turns the contents into fields and records in your favorite database.
The Taming of the Blob -- Smart Parsing for Dumb Blobs
Fancy stuff is easy. Within an hour I was rolling my own routines to parse more bits and pieces from the message, isolating the “First name” and “Last name” from the so-called “Friendly Name” portion of the “From” field. “Don’t stop there,” I said to myself.
So, I tackled the blob itself. With a little head-scratching, a smattering of OOP concepts under my belt (and a passing familiarity with VB), I was able to deconstruct bits of the body of the message itself, scanning through the text for familiar references that might match my mighty database elements.
With only a little fancy footwork, I was even able to detach any attachments, saving them with a unique “key” to a SharePoint document library, along with a PDF copy of the original message (also tagged with the same unique key).
The beastie will read and process messages from POP3, IMAP, and Exchange servers. It will also read and process messages directly from Outlook folders, including Exchange “Public Folders.” It has a fairly full-featured scripting language, and variables, once created are reusable.
While this is all well and good for me and mine, the beauty of this product is its universal application. There’s not a week goes by that someone on doesn’t ask me for the easy way to get information from a web site to a database. While there are a myriad of ways — some are easy and some are not. None are as easy as email.
Moreover, if it’s email generated by a web form, you control the structure. If the structure is predictable, Email2DB can easily grab that email, work with your structure, find the right bits and pieces, deconstruct them into the raw data you need, and then, easily slip that deconstructed data into an eagerly awaiting database. All is right with the world.
The requirements are minimal. The Email2DB software costs $300, $500 or $1,000, depending on features. I went with the $500 copy, as I needed the scripting engine and attachment processing. You need an email account (any will do, including those reached via SSL). Finally, I run it on a virtualized XP machine, rigged to autostart, autologin, and autorun, should anything interrupt its dedicated rounds.
Overall, the customization took about three days. I created scripts for:
- pattern matching to extract first and last names
- file-renaming to save copies of the original message and attachments to a SharePoint library
- Unique (per message) tags so that saved items could be retrieved as a group
- URL constructions so the database could include links to the original message and attachments
- Other custom flags for the source, date and time received, and type of inquiry
Whether you’re looking to manage the email meteor shower, stave off an invasion of unstoppable email blobs, or just want to turn a few dumb ones into smartly structured data, Email2DB can do it. It’s not often you can find software that will not only stop an alien invasion, but will also send you an acknowledgement when it’s done. Steve McQueen not included.
