Solved

VB Code to extract entire record information from data file

Posted on 2009-05-13
14
178 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
[X]
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
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

739 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