Solved

VB Code to extract entire record information from data file

Posted on 2009-05-13
14
172 Views
Last Modified: 2013-11-25
Hello.  I have VB Code that references a text file for search criteria (srCriteria.txt).  The search criteria is an account number.  The code then searches through a data file (data.txt) for that specific account number and extracts the account number to a log file (srOutput.log).  I need the code to extract all the information related to that record.  The code should extract the account number and all of lines of the record up until the next account number.  Any help would be greatly appreciated.  

Thank you
Diammond
Module Module1
 

    Sub Main()

        Dim DataInFile As String = ("c:\data.txt")

        Dim srCriteriaFile As String = ("c:\srCriteria.txt")

        Dim LogFile As String = ("c:\srOutput.log")

        Dim AccountNumber As String = Chr(34) & "*.*" & Chr(34)

        Dim PreviousLine As String = Nothing
 

        Dim srCriteria As New Dictionary(Of String, String)

        Using sr As New System.IO.StreamReader(srCriteriaFile)

            Dim line As String = sr.ReadLine

            While Not IsNothing(line)

                If line.StartsWith(Chr(34)) Then

                    srCriteria.Add(line, Nothing)

                End If

                line = sr.ReadLine

            End While

        End Using
 
 
 
 

        Using sr As New System.IO.StreamReader(DataInFile)

            'Using sw As New System.IO.StreamWriter(srCriteriaFile, False)

            Using log As New System.IO.StreamWriter(LogFile, False)

                Dim line As String = sr.ReadLine

                While Not IsNothing(line)

                    If Not IsNothing(PreviousLine) Then

                        If PreviousLine.StartsWith("#Account") Then

                            If line.StartsWith(Chr(34)) Then

                                If srCriteria.ContainsKey(line) Then

                                    log.WriteLine("Account#" & " " & line)

                                    line = AccountNumber
 

                                End If

                            End If

                        End If

                    End If
 
 
 

                    PreviousLine = line

                    line = sr.ReadLine

                End While
 
 

                log.Flush()

            End Using

        End Using
 

           System.Threading.Thread.Sleep(TimeSpan.FromSeconds(10).TotalMilliseconds) 
 

    

    End Sub
 

End Module

Open in new window

data.txt
srCriteria.txt
0
Comment
Question by:Diammond
  • 7
  • 7
14 Comments
 
LVL 39

Expert Comment

by:abel
ID: 24419817
You posted in VB area, but your code is VB.NET. So I answer with VB.NET. The easiest thing to do (and easy is most of the time better) is to read the whole file in memory as a string and then split it, like so:



Using sr As New StreamReader("..\..\Data\Q24405722.txt")

    Dim all As String = sr.ReadToEnd()

    Dim records As String() = all.Split(New String() {"#Account"}, StringSplitOptions.RemoveEmptyEntries)

    For Each record As String In records

        ' do something with the record '

        ' it contains everything from begin #Account to begin of next #Account '

    Next

End Using

Open in new window

0
 
LVL 39

Expert Comment

by:abel
ID: 24419820
my Q24405722.txt is your data.txt, btw :)
0
 

Author Comment

by:Diammond
ID: 24432325
Thank you for your response.  The data file is very large.  I've had issues in the past with trying to read this entire file into memory.  The code would choke.  The readline method allowed the code to continue.
0
 
LVL 39

Expert Comment

by:abel
ID: 24447013
Ah, ok. In which case you can do something like the following (the method GetRecord will return an empty string at the end of the file):

Public Sub Main()

    Using sr As New StreamReader("..\..\Data\Q24405722.txt")

        Dim record As String = ""

        Do

            record = GetRecord(sr)

        Loop While record <> ""

    End Using

End Sub
 

Shared sLastLine As String = ""

Private Function GetRecord(ByVal stream As StreamReader) As String

    Dim sb As New StringBuilder

    Dim sLine As String = ""
 

    ' first time only '

    If sLastLine = "" Then sLine = stream.ReadLine()
 

    Do

        sb.Append(sLine)

        sLine = stream.ReadLine()

    Loop While Not stream.EndOfStream AndAlso sLine <> "#Account"
 

    sLastLine = sLine

    Return sb.ToString()
 

End Function

Open in new window

0
 

Author Comment

by:Diammond
ID: 24485292
I will give this a try and get back to you.  Thank you.
0
 

Author Comment

by:Diammond
ID: 24539575
I'm not getting too far with this.  I'm sure it is because I don't have things in the correct sequence.  Based on my original code and your suggestion, can you post what the code should look like?

Thank you.
0
 
LVL 39

Expert Comment

by:abel
ID: 24542693
That's a bit troublesome, because that's what I already did ;-). I mean, I took your question and looked at your approach and implemented a method to get each record.

You can open a new (test) project and paste my code in it and the data file. You can then run the code and see what it does. It should run smoothly (I tested my code with your data).

Once that works, we can find a way to get your other requirement working: the sCriteria file, but that's the easier part.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Diammond
ID: 24548506
Thanks for the explanation.  Yes, the code is grabbing all of the information in each record.  Now we just need to incorporate the criteria and the log file.
0
 
LVL 39

Expert Comment

by:abel
ID: 24552810
It was a bit tricky to get right, it seems, but using the following code will print:

Found a record according to criteria: 5555555555555
Found a record according to criteria: 7777777777777



Dim criteria As New Dictionary(Of String, Boolean)
 

Using srCrit As New StreamReader("criteria.txt")

    Dim allCrits As String() = Regex.Split(srCrit.ReadToEnd(), "[^0-9]+", RegexOptions.Multiline)

    For Each crit As String In allCrits

        If IsNumeric(crit) Then

            criteria.Add(crit, True)

        End If

    Next

End Using
 

Using sr As New StreamReader("data.txt")

    Dim record As String = ""

    Do

        record = GetRecord(sr)

        Dim accountNr As String = Regex.Replace(record, ".*?(?:#Account)?\s+\D(\d+).*", "$1", RegexOptions.Singleline Or RegexOptions.Multiline)

        If criteria.ContainsKey(accountNr) Then

            Debug.WriteLine("Found a record according to criteria: " + accountNr)

        End If

    Loop While record <> ""

End Using
 

' getrecord function '

Shared sLastLine As String = ""

Private Function GetRecord(ByVal stream As StreamReader) As String

    Dim sb As New StringBuilder

    Dim sLine As String = ""
 

    ' first time only

    If sLastLine = "" Then sLine = stream.ReadLine()
 

    Do

        sb.AppendLine(sLine)

        sLine = stream.ReadLine()

    Loop While Not stream.EndOfStream AndAlso sLine <> "#Account"
 

    sLastLine = sLine

    Return sb.ToString()
 

End Function

Open in new window

0
 
LVL 39

Accepted Solution

by:
abel earned 500 total points
ID: 24552825
A little bug crept in. Replace the regex with this:

Regex.Replace(record, ".*?(?:#Account)?\s*\D(\d+).*", "$1", RegexOptions.Singleline Or RegexOptions.Multiline)
and replace the GetRecord return statement with this:

Return sb.ToString().Trim()
that is because of the empty newline in the last statement as a result of sb.AppendLine.
0
 

Author Comment

by:Diammond
ID: 24556862
Thank you Abel.  I will give this a try.
0
 

Author Comment

by:Diammond
ID: 24574014
Hi Abel.  The data file is actually on a server.  If I log into the server and run the code it works great!  However, if I run the code via a mapped drive to the server from my workstation I get a long unhandled exception error....Unhandled Exception: System.Security.SecurityException: Request for the permissi
on of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0., etc....  

Any suggestions?

Thanks
0
 

Author Comment

by:Diammond
ID: 24710444
Hi Abel.  I was able to figure this out.  The code is working perfectly.  Thanks again for your help!

Diammond
0
 
LVL 39

Expert Comment

by:abel
ID: 24710480
Missed the follow-up, sorry 'bout that. Glad you got it working though :)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now