Regular Expressions- extracting data from files

Posted on 2008-10-25
Last Modified: 2010-04-21

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!

Question by:montarch
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
LVL 27

Expert Comment

ID: 22803984
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.
LVL 27

Expert Comment

ID: 22804242
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?

Expert Comment

ID: 22804611
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 51

Expert Comment

ID: 22804741
> .. 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

LVL 16

Expert Comment

by:Bryan Butler
ID: 22805594
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?

Author Comment

ID: 22805800
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.

LVL 16

Expert Comment

by:Bryan Butler
ID: 22805877
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)
LVL 16

Expert Comment

by:Bryan Butler
ID: 22805891
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 ;)
LVL 27

Accepted Solution

ddrudik earned 500 total points
ID: 22806164
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


Author Closing Comment

ID: 31512326
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.
LVL 27

Expert Comment

ID: 22858907
Thanks for the question and the points.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As most anyone who uses or has come across them can attest to, regular expressions (regex) are a complicated bit of magic. Packed so succinctly within their cryptic syntax lies a great deal of power. It's not the "take over the world" kind of power,…
Do you hate spam? I do, and I am willing to bet you do as well. I often wonder, though, "if people hate spam so much, why do they still post their email addresses on the web?" I'm not talking about a plain-text posting here. I am referring to the fa…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question