Wednesday, October 20, 2010

Consultant Tools Series: Windows Grep (WinGrep)

Yesterday I received an e-mail from a client saying that they were seeing duplicate journal entry lines in Dynamics GP for JEs that had been imported from an external system.

Knowing how my eConnect JE import was developed, I was pretty confident that my integration wasn't inadvertently importing JE lines twice, and the client found that only certain JEs were being duplicated.  When I looked at the JEs in Dynamics GP, I saw that they were nearly identical, but the DR and CR descriptions were slightly different in the "real" JE vs. the "duplicate" JE.  That let me to believe that the external system was potentially sending some duplicate data to GP.

With this in mind, I chose two duplicate JEs out of Dynamics GP from September and documented them.  Now I just had to confirm what the external system sent for those JEs.

Since I always archive a copy of all source data files for my integrations, I jumped over to the archive directory and extracted all 2,115 JE files for the month of September.  The files are CSV files, but have a .dat extension.  I first tried to use the Windows Server 2008 search feature that is built into Windows Explorer.  I typed in the unique ID for one of the JEs I had documented, but received no results.  Figuring that Windows wouldn't search the .dat file extensions, I renamed all of the files to *.txt and tried again.  No search results.  I was sure that the data that I was searching for was present in one of the files, but the Windows Search feature just wasn't doing the job.

I even tried copying the files over to my Windows 7 workstation, which I have had some success with, but still no results.

So I did some Googling for full text file searching in Windows, and one solution was constantly referenced in the results:  Windows Grep, or WinGrep.

Windows Grep is a shareware application that implements the functionality of Unix grep in an easy to use Windows application.  I downloaded Windows Grep, pointed it to my directory with over 2,000 .dat files, and told it to search for my unique JE ID.  In a few seconds, it found the file that I needed.  When I opened the file, sure enough, it contained two JEs.

I was able to quickly find the source data files for the two JEs that I had documented, and send everything to the vendor that maintained the external system.  They then discovered that a cross join was being performed during their export, resulting in duplicate JEs being exported.

I'm guessing I only used 0.1% of the functionality of Windows Grep, and it has many additional features that sound interesting, so I'm impressed with it so far, and will try and see if I can figure out any other situations where it may be useful.  For $30, it is a bargain if you need its powerful search (and replace) functionality.

Update:  Reader Stanley Glass has posted a comment suggesting the free Agent Ransack utility, developed by Mythicsoft out of Oxford England, for searching files.  It also appears to have a comprehensive feature set, so now I have two tools to consider for these types of searches!

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.


Stanley Glass said...

Another program that does this is Agent Ransack . I use this all the time. I have to constantly find data in xml or csv files. In Agent Ransack you can also use Regular Expressions.

Steve Endow said...

Excellent, thanks for the tip! I'll update the post to note Agent Ransack as an alternate (and apparently free) option.