RegEx in Dot Net

While attempting to parse a QuickBooks export file, I had need to match a rather complicate pattern.  The pattern was easy do express in Regular Expressions, but not so much with normal string handling in Dot Net.

Proving that everything and the kitchen sink is available in the Dot Net Framework, here is how you can use the beauty of Regular Expressions in Visual Basic 2008.

Imports    System.Text.RegularExpressions.
sub amatch
 dim aMatch = Regex.Match(InputField, ":[0-9A-Za-z]* " & Chr(&HB7))
if aMatch.Success then
   'Do Something
end if

More RegEx Goodness

It's hard to believe that I have spent hours copying and pasting and manually reformatting text files when I had the power of regular expressions at my fingertips.  Oh, how true is the maximum "knowledge is power"

I had a need to create a text file with database names and key fields in a CSV format.  What I had was a text file with the database schema like this (I highlighted the parts of the text file I actually wanted.)

CREATE TABLE [dbo].[BuildCheck] (
[key] [uniqueidentifier] NOT NULL ,
[BuildStamp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[version] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[dtproperties] (
[id] [int] NOT NULL ,
[objectid] [int] NULL ,
[property] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uvalue] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lvalue] [image] NULL ,
[version] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblCountyNames] (
[pkey] [uniqueidentifier] NOT NULL ,
[intCountyNum] [int] NULL ,
[txtCountyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtpassword] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

What I wanted was this.

"BuildCheck","key"

"dtproperties", "id"

"tblCountyNames", "pkey"

So I turned again to the hidden power of Regular Expressions.  I crafted the following RegEx line
^(CREATE TABLE *)([\d\w\s\]\[\. ]*)( *\(\r\s*)(\[\w*\])([\w,\[\]\(\)\r\s]*Go)
and then did a search and replace, replacing the schema with the second and fourth (one based) items.  I know it looks hopelessly complex, but it's not.

Item One
^(CREATE TABLE *)
Matches a "CREATE TABLE" command at the beginning of the line, and a chain of spaces (represented by the space character and the asterisk, which tells the engine to repeat the previous match)

Item Two
([\d\w\s\]\[\. ]*)
Matches a block of numbers (/d) words (/w) and whitespace (/s), beginning and ending brackets and periods.  This translates to the database name

Item Three
(\s*\(\r\s*)
Match some whitespace, then a left parenthesis, then a carriage return following by a string of whitespace.

Item Four
(\[\w*\])
Item Four is our key field name.  We're looking for an opening and closing bracket with word characters inside.

Item Five
([\w,\[\]\(\)\r\s]*GO)
Item five is everything else up and including to the word GO.

Then, I did a search for
(^\[dbo\]\.\[)(\w*)(\]\s)(,)(\[)(\w*)(\])
Replacing it with
"\2","\6"
and viola!

Filling one copy of a database from another

Say you have two databases. Say that these two databases have a table with the same structure. Say you want to update table 1 with data from table 2. Easy cheesy.

INSERT INTO TableName

(Field1, Field2)

SELECT Field1, Field2

FROM Database.dbo.TableName AS SourceTableAlias

Regular Expressions

Here is what I love about regular expressions. They can make your life much easier.

I had to take a list of enumerated values, like this. (The list was a lot longer than shown)

Barbour=1,

Berkeley =2,

Boone =3,

Braxton =4,

Brooke =5,

Cabell =6,

Calhoun =7,

Clay =8,

Doddridge =9,

Fayette =10,

Gilmer =11,

Grant =12,

State=0,

Unknown = 99

and turn it into a switch statements in C# like this...

case 1:

value= LocationID.Barbour;

break;

case 2:

value= LocationID.Berkeley;

break;

case 3:

value= LocationID.Boone;

break;

case 4:

value= LocationID.Braxton;

break;

case 5:

value= LocationID.Brooke;

break;

case 6:

value= LocationID.Cabell;

break;

case 7:

value= LocationID.Calhoun;

break;

case 8:

value= LocationID.Clay;

break;

case 9:

value= LocationID.Doddridge;

break;

case 10:

value= LocationID.Fayette;

break;

case 11:

value= LocationID.Gilmer;

break;

case 12:

value= LocationID.Grant;

break;

case 0:

value= LocationID.State;

break;

case 99:

value= LocationID.Unknown;

break;

I did so using Edit Pad Pro and this search and replace code with Regular expressions

Search:

([a-zA-Z]*)( =)([0-9]*)

Replace:

case \3:

value= LocationID.\1;

break;

Dissecting it, [a-zA-Z] Matches all letters, both upper and lower case, while the asterisk at the end tells it to match all occurrences of it. So "a" and "aaaaaa" would both be a match. The [0-9]* does the same for the code number.

The parentheses separate the search into three terms. The replace using only terms 1 and 3 by specifying a backslash and the term I want (\1 for the first term, \3 for the third term).

The two big advantages are that I don't have to mess with copying and pasting values, which is tedious and time consuming, and I can be sure that I didn't paste the wrong value somewhere.

Simple Human Verification

While doing some work that involved contacting webmasters to have them update information regarding our office, I ran across an innovative but simple approach to human verification.

In web form submissions, developers would like to avoid having spam bots from sending in thousands of forms with junk info, so they typically use a randomly generated graphic that humans can read (sometimes) and computers can't (yet).

This form is used by Michigan simply asks the user to do a simple math problem and type the answer in a form field.  Simple yet effective (for now).

Editing PDF's

We have several online forms in PDF format.  Since I did not create the forms, I don't know if the source documents even exist.

I had to make some revisions today, and remembered how much I hate dealing with Adobe when it comes to modifying Pre-existing files.

You have to use the touchup text tool (found under tools-advanced Editing) to change the text, the touchup object tool to move around the text boxes and other objects, and to move form fields, you have to click on forms, then 'edit form in acrobat'.

All well and good.  But, Acrobat had grouped several text items together in the same text box.  I needed to move them separately.  After searching fruitlessly online, I discovered thatI can hold down the controlkey and then draw a box around the item I needed to separate from the text box.

Fun with Recursion

I needed to backup a subdirectory and need to go through all subdirectories under it.

I used recursion to solve the problem.  

For those who don't know, recursion is when a function calls itself to solve a problem.  The classic example is factorial.  5 Factorial is 5*4*3*2*1.  here's a function to do that

Function Factorial (number as integer) as long
if number=1 then
return 1
        else
return factorial (number-1)*number
end if
end function

Run through the code