Good things for your toolkit: RegEx and TextPad

Everybody has a favorite text editor.  My fav was B.R.I.E.F., a DOS editor that I think I paid $300 for back in the ’80s, and it was well worth it.  I love puns, and BRIEF was made by a software company called Under Ware.  BRIEF was also my introduction to version control systems when a package called Sourcer’s Apprentice was released for it, that doubly-appealed to me as I worked for a game company called Flying Buffalo, and they published a magazine called Sorcerer’s Apprentice.  SA was a very good VCS for its time, I used it a huge amount with FoxPro and DataFlex.

But VCS is not the topic du jour, though I am currently in the process of digging in to that: I found out that the current and previous versions of Access are compatible with VCS!  So that’s going to be interesting to research.

BRIEF is still around, but I’m not really interested in using it anymore as the editors built in to Access and SSMS are quite good.  But there are times, such as when I’m working on CMD or Perl files, that I need an external editor.  For that I’ve pretty much settled on TextPad.  It’s been around since 1992 and exists as both a free and paid version.  It supports RegEx and you can also open files in a binary mode which I found very useful a few years back when I had a file downloaded from a vendor that wasn’t loading correctly in to SQL Server (turns out they were losing either the CR or the LF from the end of the line, making the entire file one massively long string) and the binary mode showed me the problem.  Of course, getting a tech weenie at A Major Bank to understand that yes, both carriage returns and line feeds take space in a file was an interesting exercise in patience.

Yesterday and today I’ve been working on some moderately complicated reports in Access and have found TextPad to be very useful in getting done what I needed to do.  Here’s a sample string of a total field adding up several columns:

=[SumPVLP]+[SumPVLS]+[SumPVLT]+[SumRDP]+[SumRDS]+[SumRDT]...

I needed to wrap each field in a NZ(), so I manually added the first part to the beginning of the string and the second part to the end, producing the following:

=nz([SumPVLP]+[SumPVLS]+[SumPVLT]+[SumRDP]+[SumRDS]+[SumRDT],0)

Then TextPad’s Find & Replace, with regular expressions turned off, I replaced + with ,0) + nz(, producing this:

=nz([SumPVLP],0)+nz([SumPVLS],0)+nz([SumPVLT],0)+nz([SumRDP],0)+nz([SumRDS],0)+nz([SumRDT],0)

Finally, for the grand total line, I needed to change the Sums in the previous line with Tots, again, TextPad’s F&R:

=nz([TotPVLP],0)+nz([TotPVLS],0)+nz([TotPVLT],0)+nz([TotRDP],0)+nz([TotRDS],0)+nz([TotRDT],0)

Since each line had 15 or 18 field names, and this was spread across three reports, it saved me a heck of a lot of work in the end.  It’s a tool that I do not want to do without.  Yes, I could have used the Access VBA editor, but I like NotePad and my brain wasn’t in VBA mode at that time: any editor could have done this.  I have used other editors such as Notepad++ (my wife’s an Emacs freak), but I keep going back to TextPad.

I also use it to strip out a lot of lines from table scripts produced by SSMS using RegEx, it eliminates a lot of dross that I can do without.

I think the point of this post is to encourage people to work smarter, not harder.  I could have done all the changes in Access in a zoomed box, but I like minimizing the amount of typing that I do if I can (you wouldn’t think it as verbose as I am!).  Doing it in TextPad was pretty simple and using it helps to refresh my memory of its specific key commands and quirks.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s