Regular Expressions- extracting data from files


I've been learning Regular Expressions in preparation for an upcoming project, and need a little advice on how to preceed to the next step.

I have around 1,100 reports that I need to extract various data from. The reports are in .wpd format (version 12), and I've converted a test group of 150 files to .txt format.

I've created and tested several RegEx strings, and they all seem to work well.

Now, how do I 'use' these regex strings to parse and extract the data from the 1,100 text files and push that data into either/or-
   -individual text files (1 for each corresponding report) for further refinement
   -into a single .txt, .csv or other format

I have a variety of tools at my disposal ( 2003, MS Office 2003, parse-o-matic, textpad, etc..), and am not against purchasing/acquiring/learning other software to help in this task (should the .net learning curve be too overwhelming).

Should I jump into coding a solution? If so, what platform or tools would be appropriate?

Is there a more complete 'packaged' solution available for this task? What are your experiences and preferences regarding these?

Thanks again for your help!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

VB.NET would  be a fine candidate for such a project.  Please provide a text sample, specify what data you want to pull from that text sample, and what you want to do with that data pulled.
Also, you should explain more about the regex patterns and how they relate to the files, for example you say you have 1 regex file per file, would that same regex pattern match properly any other files in your set?  How are your files named in your exported file set?
This really looks to me like a command-line application. Consider doing it in Python ( The language is free and has a shallow learning curve. A working prototype of the program you want (reading from standard input and writing to standard output) is short and easy to understand without even reading the tutorial:

import re
import sys
myregex = re.compile('^This is a test line$')
for line in sys.stdin:
    if myregex.match(line):
        print line.rstrip()

Open in new window

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

> .. looks to me like a command-line application.
I'll Cc that:

egrep 'your-regex here' your-1100-files-here > single.txt

the result is as you requested:
>    -into a single .txt, .csv or other format

Bryan ButlerCommented:
I believe egrep, or some kinds of stream editor/text manipulation language (sed/awk) would be the quickest.  Perl has always been the best known as the best text handling language in my book, but that probably would be the quickest way to go.  Python is an easy language to pick up, and if you want to do some specific things, such as date manipulation or other test transformation, then a high level language such as this, or one with a good ability/plugin/library to handle these text changes would be more appropriate.  I've been starting to use PowerShell for some text handling, and it ties in all of the MS/.net technologies.  Does any of that help?
montarchAuthor Commented:
Thanks for the responses, folks.
To answer your questions -as best I can- however a little long in the explaination:
The report files are Archaeological Survey Project Reports.
1 File = 1 Report = 1 Project.

I want to-
- collect 3 pieces of data from every report
- extract those 3 pieces of data to fields in an output file called "projects.csv"

At a later date, the data in this output file (projects.csv) will be imported into our existing  'Projects Database'.

Examples and Samples of the 3 pieces of data to collect, extract, output-
-I realize that while my RegExps work, they need alot of refining. I'll worry about that later.-
Data #1- Permit Number
Report sample:      U-03-MQ-123a
RegEx to match:      U-[0-9][0-9]-MQ-\d{1,4}.?(\w*.?)*
To fld in .CSV:      permitnumber
Data #2- Project County
Report sample:      LOS GENERIC COUNTY, UTAH
RegEx to match:      \w* ?\w+ ?county,( ?| +)Utah
To fld in .CSV:      projectcounty
Data #3- Project Number
Report sample:      MYCO Report No. 03-100
RegEx to match:      (Report No.).?\d\d-\d\d\d?
To field in .CSV:      'projectnumber'
I hope this is enough information. Let me know otherwise.
I'm also going to look into the Python approach.

Bryan ButlerCommented:
Sounds like just a quick solution using whatever regex language you feel comfortable with.  If you're on unix, then go with just a quick 3 line script.  But it sounds like you're on windows and if you want to use what you got, instead of installing an IDE/compiler and learning a language, you could use WSH/powershell as these are basically the 'dos' part of windows, only they are like dos on steroids.  Here is a powershell script I used in another answer that could be modified to do it.  This was splitting an SQL string by reading one line at a time and sending the output to a file.  You would not want to sue the "Replace", but rather the "search".

$aryCDSs = Get-Content ".\test1.txt"
$iter = 0
do {
    [regex]::Replace($aryCDSs[$iter], "Update", "`n`$0") >> test2.txt

} until ($iter -eq $aryCDSs.length)
Bryan ButlerCommented:
And have 3 lines as in:

$aryCDSs = Get-Content ".\test1.txt"
$iter = 0
do {
    [regex]::Replace($aryCDSs[$iter], "<regex1>", "`$0") >> test2.txt
    "," >>test2.txt  #add comma
    [regex]::Replace($aryCDSs[$iter], "<regex2">, "`$0") >> test2.txt
    "," >>test2.txt   #add comma
    [regex]::Replace($aryCDSs[$iter], "<regex3>", "`$0") >> test2.txt
   "'n">>test2.txt  # adds newline char

} until ($iter -eq $aryCDSs.length)

Does that help?  If you have something like an mainframe/ebcdic file with packed decimals or something, then that's a whole different story ;)
It all depends on your source text, but the following is VB.NET command-line project code that worked with the source text you provided.  I named the file projects.csv but I delimited the file with | instead of , since you have , in one of your columns (the county).  You could choose to delimit in , and enclose that column in " if you prefer.
Imports System.Text.RegularExpressions
Imports System.IO
Module Module1
    Sub Main()
            If File.Exists("projects.csv") Then
                Console.WriteLine("'projects.csv' file found, deleted file.")
            End If
            For Each datafile In Directory.GetFiles("c:\datafiles")
                Dim sr As StreamReader = New StreamReader(datafile)
                Dim filetext As String = sr.ReadToEnd()
                Console.WriteLine("Processing: " & datafile)
                Dim repermitnumber As Regex = New Regex("U-\d{2}-MQ-\d{1,4}[a-z]*")
                Dim reprojectcounty As Regex = New Regex("\w* ?\w+ ?county, *Utah", RegexOptions.IgnoreCase)
                Dim rereportnumber As Regex = New Regex("(?<=Report No\. ?)\d\d-\d\d\d?")
                Dim mpermitnumber As Match = repermitnumber.Match(filetext)
                Dim mprojectcounty As Match = reprojectcounty.Match(filetext)
                Dim mreportnumber As Match = rereportnumber.Match(filetext)
                Dim sw As StreamWriter = New StreamWriter("projects.csv", True)
                Dim dataline As String = mpermitnumber.Groups(0).Value & "|" & mprojectcounty.Groups(0).Value & "|" & mreportnumber.Groups(0).Value
                Console.WriteLine("   Writing: " & dataline)
        Catch E As Exception
            Console.WriteLine("An error was encountered:")
        End Try
    End Sub
End Module

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
montarchAuthor Commented:
Thanks for everyones help, and sorry for not getting back sooner.
I've spent the last week working over the various solutions provided, based on the tools that I have ( 2003, windows powershell), and the solution worked out the best for me. I did enjoy diving into powershell, however, and plan on using it in other tasks. Powershell just didn't give me the results that did. Thanks all, and thanks ddrudik- I have more questions about this project, but I'll pout those in another post.
Thanks for the question and the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Regular Expressions

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.