The story begins with To CATCH a Thief
Part Three: Emergency!
Comcast Digital Cable of Charlottesville, Virginia needed help cleaning up their address database. They had to become E911-compliant before they could offer telephone service to their customers. I came on as a temp and began comparing their data to the United States Postal Service address database.
This was pure drudgery, at first. They used various proprietary databases. Nearly everyone exported their reports into Microsoft® Excel and expected to receive corrected data in spreadsheet format. When the addresses were corrected, we manually updated one of the databases and made notations on the spreadsheets. We emailed these spreadsheets on a daily basis.
After a month of torture, I wrote a small script in VBA to simulate keystrokes. I wanted to read in account numbers, “type” them into the billing address database and then “type” the sequence of keys that would let me step through the account, looking for discrepancies.
I was thrilled when I got it to work, sort of. I used a command called SendKeys. It was “semi-automatic”, but it speeded up my work considerably.
I expanded the script, created a front end and standardized the import/export process between the various databases and spreadsheets. I still had to manually update the database, but the SendKeys was faster and more accurate than I.
Eventually, the project leader wondered how I was getting done so quickly. When she saw the program in action, she asked me to make it available to the other temps.
Naturally, I obliged. (They already knew about it, but had politely declined to use it, probably because most of them were faster typists than I.)
Once the core application was written, I turned my attention to automating as many of the tasks as possible.
One of the major headaches was the length of the field for street names. Unfortunately, Comcast had no standardized way of spelling or abbreviating street names. So, we made up our own! While one guy went through an entire zip code by hand, I wrote an Abbreviation Manager and imported his handiwork.
Although this helped us tremendously, his work was in vain, because nobody “upstairs” could come to an agreement on how best to get the entire database standardized.
We named the first version of our project Cable Mach 5 (“GO Speed Racer, Go!”) and I started the painful task of documenting the action steps. That was boring.
Professional pride made me implement an on-screen help system with a printable version, too! Too bad I didn’t consider using a Wiki.
After about two months of semi-automatic processing, I was visiting one of the programmers in the billing department. She showed me a fantastic script that used Dynamic Data Exchange (DDE) to communicate directly with the proprietary database! She gave me a copy, which I promptly plopped into the growing Access database.
This change was so dramatic, compared to my primitive SendKeys method, that even my colleagues asked for a copy. As this liberated us from the drudgery, I knew we had to coordinate the workflow. I made an agreement with my colleagues: if they split the daily work between themselves, I would tackle some of the analysis using queries, charts and even more VBA code. I spent several weeks making incremental changes, implementing business logic and creating gee-whiz reports to impress the folks “upstairs”.
This is rather anti-climactic: once all of the market segments reached 90%+ compliance, there was nothing for us to do but to send back reports of uncorrected addresses. One of my colleagues created an elaborate coding system to explain why we couldn’t correct an address. This was the last report developed for the Cable Mach 5 System.
Here is a collage of the database objects.
|The ParserMonster story is a time-mellowed account of my adventures in programming. The experiences led to the creation of ParserMonster, a framework for automating text-based tasks.|