Do not…underestimate…the power…of a good text editor that supports Regular Expressions!

Yes, all my fellow “geeks” out there in the world probably expected the phrase “the power of the emperor,” or perhaps, “the power of the dark side.”  But, stick with me here, and something I learned this week about a great text editor that supports regular expressions might help you in the future.

This past week at work, I found myself dealing with a data issue for our Miami, FL operation.  Basically, we’ve got some data catch-up and cleanup efforts happening for our submissions to one of our contracting agencies.  We upload text files created by an export from our practice management  software.  But, due to a change in our relationship with the agency (we’re now a subcontractor of an agency that now stands between us and the final recipient of the data), we’ve had to change the way we submit our data.  Certain assessments and outcome data that we used to submit directly to the final recipient of the data through their web portal we now must submit via these FTP text files.  So, we had to work with our third-party software development company, Intrepid Services, LLC, to build one new export and update 3 others.   The problem was, there is a fourth export we didn’t touch…and we needed it this week.

Basically, the export builds a simple text file in order to update the Social Security Number (SSN) of one of our clients in the contracting agency’s database.  If we incorrectly keyed an SSN, we use this export to replace that incorrect SSN with the correct one.  Each line item has four pieces of information:  A Contractor ID , the old SSN, the new SSN, and the Provider ID for the agency serving the client. In the past, we would have put the same ID in for Contractor ID and Provider ID because we were a direct contractor with the contracting agency.  Now, we have to put our ID as the Provider ID since we’re a subcontractor, and the ID of the agency for which we subcontract in as the Contractor ID.

So, I was in a jam.  I had to get data uploaded on Friday for us to stay anywhere close to on schedule, and my developer couldn’t build anything fast enough to make a difference.  So, I pulled out my trusty text editor, created an SSN update file from our existing export, and was ready to do a find and replace to correct all the incorrect Contractor ID values in my gigantic text file.

But, OH NO!  Each line has our ID number TWICE; once INCORRECTLY for the Contractor ID, and once CORRECTLY for the Provider ID.  A find and replace would replace BOTH instances of our ID.  What to do!?

Then, I noticed something.  I use a text editor called TextPad, made by Helios Software Solutions.  This editor does a LOT of great things, such as allow you to display line numbers, and it will highlight code for PHP and other languages in such a way that you can tell if you’ve properly terminated strings and ended lines, etc.  You can buy a single user license of this editor for $27, and I’d say it’s a bargain at that price.  What I noticed was a checkbox in the “Replace” wizard of TextPad with a label that reads, “Regular expression.”  In the course of learning to program in PHP, I learned a little about using regular expressions.  Regular expressions are “a concise and flexible means for matching strings of text, such as particular characters, words, or patterns of characters (Wikipedia).”  I was thrilled.

To make a long story short (I know, too late), I was able to search for ONLY the FIRST instance of the string I wanted to replace on each line by using the following in the “Find what” box of the Find/Replace wizard:

^[0-9-]\{10\}

Let me break down what that means in regular expression (also called “regex” for short) terms:

^:  matches the start of the string the regex is applied to.

[0-9-]:  Anything in between square brackets ([ ]) defines a character class.  Here, I’m saying a number from 0 to 9 or a hyphen.

\: The escape character.  This is a little strange.  Normally, the escape character suppresses the behavior of a special character in regex.  But, because of the way TextPad is implementing regex, I had to escape the curly brackets ({ }).

{10}:  Forget the escape character for now.  In regex terms, {x} means the preceding defined item repeats x number of times.  So, Here I’m saying that any number from 0 to 9 or a hyphen repeated 10 times*.

* These notes on regular expressions borrow from text at http://www.regular-expressions.info/reference.html.

So, ^[0-9-]\{10\} all together means, “Match starting at the beginning of the string any 10 characters so long as the characters are 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, or –.  Since our ID is in the format of a business tax ID (e.g., 99-9999999) that’s what I needed to match.

In the “Replace with” field of the text editor, I just put the ID of the agency for which we are now a subcontractor.   Then, I clicked “Replace All.”

TextPad treated each line as a separate string.  It replaced the first instance of our ID in each line (which, you’re probably inferred, is at the very beginning of each line), but left the second alone.

Then, in the course of the same project, I realized that I needed to query our database to get some data about a large set of our clients.  The agency had sent us a report with the client IDs for our practice management sofware included.  So, I pasted the list of client IDs into TextPad.  Those client IDs take the form of a letter, followed by 5 digits.  I had something that looked like this:

F00001

F98765

F54637

F65432

And so on…but a lot longer.  To query our database, I needed to get this into a SQL query looking more like

(

‘F00001’,

‘F98765’,

‘F54637’,

‘F65432’

)

But, with this regex feature, that was NO PROBLEM.   I used ^ to match the beginning of the string, and replaced it with ‘.  That didn’t replace the first character, but instead shoved the ‘ in front of the existing string.  Then I used $ to match the end of the string and replaced it with ‘,.  The $ sign is the regex symbol for matching the end of a string.

That got my list so that each line looked something like:  ‘F00001’,

All I had to do was go to the last line, and delete the comma.  Then I had strings encased in single quotes, with each line followed by a comma except the last, which I pasted into Microsoft Query Analyzer on our database server to query for the client data I needed.

I could have spent time carefully cutting and pasting to fix this problem, but it would have taken a long time and I might have made mistakes selecting characters to paste over.  I probably could have gotten creative with Excel and used the data import features of that software to separate the lines of my text file into fixed width chunks ported to separate columns that I then could have updated.  But, regex in a text editor was the solution I stumbled upon that made the most sense.

If you find yourself in a similar spot, give it a shot.  If you aren’t familiar with regex at all, start with a website like http://www.regular-expressions.info/reference.html and learn some of the basics.  You’ll find yourself using this pattern-matching method to solve the most confounding problems with data.  I know I have over the years!

Maintaining balance as a professional 30-something

My job gets heavy sometimes.  I’ve worked two 16 hour days this week trying to help clean up a data problem for a couple of my company’s regional offices.  I’m stressed about deadlines on that project.  I work from home, and have for the past 7 years and 9 months, and I’ve found that makes it a little more challenging to get out in the world and maintain a social life.  Perhaps that challenge is compounded by the fact that I am, by nature, a bit of a computer geek and tend to spend my available social time gaming.

So, what’s the answer?  Of late, I’ve started to think that the answer is scheduling time to be social and do the things I love.  Two summers running, I’ve decided to audition for theatre productions with Music Theatre Louisville, a community theatre in my area.  The rehearsals and run of the show last about 5 weeks total.  I don’t feel like I can commit to a longer period…I worry about problems coming up at work even during that 5 weeks.  But, the great thing is that being committed to a rehearsal schedule forces me to get out of my office, and out of my house, and spend time with people who have a common interest in theatre!

It’s also nice that signing up for a show gets me back to my theatrical roots.  From the time I was in 4th grade until I was about 23, I was constantly in a show of some kind.  I performed constantly.  My undergraduate degree is a BFA in Musical Theatre from Otterbein College.  I made a (pathetic) living performing for a few years after finishing that bachelor’s, and stumbled from a theatre gig at The Lost Colony Outdoor Symphonic Drama into my first serious “desk job;” Director of Marketing for that theatre.  My experience as The Lost Colony’s Director of Marketing lead to another marketing director job for the company for which I now work, the Institute for Family Centered Services (IFCS).  At IFCS, I naturally evolved into working with software and computers, things I’ve always loved.  I went back to school for an MBA with a Management Information Systems certificate from East Carolina University to grow my skill set in that area of management.  Suddenly, I was doing work I enjoyed, but I’d lost my connection to the theatre work that I loved.  Doing shows seems to help me recharge around the stress of work.  What I’m trying to share here is that when I stopped actually making time for the (now) hobby that I love, I stopped doing it altogether.  By committing to shows and talking to my boss and teammates in advance about rehearsal time, I’m finding it’s possible to make time for one show a year.  It presents a bit of a challenge in that I can’t really travel for work during these 5 weeks.  The good news is that modern video-conferencing and telecommuting practices make it to where I don’t have to travel all that much.  Maybe you, reader, can work out a similar commitment to something that makes you happy in a way that doesn’t conflict with your work.

I’m also trying to get on the golf course a few times a week.  That effort has been somewhat impacted by work, rain, and the recital schedule for my wife’s performing arts studio business.  However, I find walking 9 holes of golf after work can make a big difference in keeping my stress level down.  Here’s something my friends and acquaintances around Louisville might now know:  Louisville has an AMAZING set of public golf courses run by Louisville Metro Parks!    The Shawnee Golf Course is right down the street from my house, and it’s pretty easy to walk on for 9 holes without a tee-time after 5 pm…and rates drop at  6pm!

To summarize, I think if there is a “magic trick” to managing stress, it may be to find a few things you love to do that are important to you, and actually schedule time for them.  Put recurring calendar events on your Outlook or Blackberry calendar for them.  Maybe it’s family time, or jogging, or theatre, or golf…whatever the case may be.  Put the time in your calendar, and if you’re anything like me, I think you’ll find that you actually do a better job of taking time for those things.  Your stress level will come down, and you’ll be more effective in all the areas of your life, including work.  “All work and no play…” as they say.  I believe it.

Testing the Blog

OK, I’m not exactly sure how this thing is going to work, but I want to start blogging, so here I go.  I’m thinking this will be a way for me to journal in a way that friends and colleagues can see, if they like.  I also think it will help me create an online identity, or “brand,” that extends beyond just what users can find about me on Facebook and such.

I’d like my blog to be meaningful.  I’d like it to encompass what I learn along the way in the course of doing my job and my supplemental study of  software development.  I figure I’ve got something worthwhile to share with the world in sharing myself.  So, here’s where it begins.