Text Parsing and Split Function

[redbull]

Hello everyone. I have a report that runs nightly, it is saving text files successfully. I am trying to clean up these reports with Split functions and parsing... I have read about this on the MSDN site...Here is what I am trying to do...

These reports contain raw data.. and the information I need inside of it.... Wanting every line begininng with a "< " to be removed and then the extra spaces removed. I know this can be done thru excel using macros but I am hoping the same can be accomplished here...

<0d:CR>                      ( 3:31) ==> ( 3: 1)
<0c:FF>                      ( 4: 1) ==> 
<0d:CR>                      ( 4: 1) ==> ( 4: 1)
<0a:LF>                      ( 4: 1) ==> ( 5: 1)
<0d:CR>                      ( 5: 1) ==> ( 5: 1)
<0a:LF>                      ( 5: 1) ==> ( 6: 1)
          Groups with Communication Problems  04/12/2012  09:01
<0d:CR>                      ( 6:73) ==> ( 6: 1)
<0a:LF>                      ( 6: 1) ==> ( 7: 1)
<0d:CR>                      ( 7: 1) ==> ( 7: 1)
<0a:LF>                      ( 7: 1) ==> ( 8: 1)
<0d:CR>                      ( 8: 1) ==> ( 8: 1)
<0a:LF>                      ( 8: 1) ==> ( 9: 1)
Autodial       Phone #             Next Time Trigger   Total/Finals/Reprints
<0d:CR>                      ( 9:77) ==> ( 9: 1)
<0a:LF>                      ( 9: 1) ==> (10: 1)
<0d:CR>                      (10: 1) ==> (10: 1)
<0a:LF>                      (10: 1) ==> (11: 1)
<0d:CR>                      (11: 1) ==> (11: 1)
<0a:LF>                      (11: 1) ==> (12: 1)
  DOUGC        915xxxxxxx     09:55                    1/1/0
<0d:CR>                      (12:66) ==> (12: 1)
<0a:LF>                      (12: 1) ==> (13: 1)
  GASTR        915xxxxxxx       09:41                    6/5/0
<0d:CR>                      (13:66) ==> (13: 1)
<0a:LF>                      (13: 1) ==> (14: 1)
  LUUMD        913xxxxxxx      09:12                    1/1/0
<0d:CR>                      (14:66) ==> (14: 1)
<0a:LF>                      (14: 1) ==> (15: 1)
  PHILM        915xxxxxxx       09:12                    6/5/0
<0d:CR>                      (15:66) ==> (15: 1)
<0a:LF>                      (15: 1) ==> (16: 1)
  QDXAPHSP     919xxxxxxx     09:04                    9/7/0
<0d:CR>                      (16:66) ==> (16: 1)
<0a:LF>                      (16: 1) ==> (17: 1)
  SON          913xxxxxxx       08:57                    8/5/0
<0d:CR>                      (17:66) ==> (17: 1)
<0a:LF>                      (17: 1) ==> (18: 1)
<0d:CR>                      (18: 1) ==> (18: 1)
<0a:LF>                      (18: 1) ==> (19: 1)
<0d:CR>                      (19: 1) ==> (19: 1)
<0a:LF>                      (19: 1) ==> (20: 1)
          ********* Autodials queued to report STAT *********
<0d:CR>                      (20:62) ==> (20: 1)
<0a:LF>                      (20: 1) ==> (21: 1)
<0d:CR>                      (21: 1) ==> (21: 1)
<0a:LF>                      (21: 1) ==> (22: 1)

So these text files are saved locally on my PC. Is there a way to have the VBA parse these either after they are created, or While they are being formatted?? With the help of people here I have this as my formatting technique..

Name "C:\Program Files\LIFT Ericom Software\PowerTerm\trace.log" As "C:\TroubleList\Florida\Florida" & Format(Now(), "yyyymmddhhmm") & ".txt"

MSDN site = http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

What I want the final report to look like...

   Groups with Communication Problems  04/12/2012  09:01
Autodial       Phone #             Next Time Trigger   Total/Finals/Reprints
 DOUGC        915xxxxxxx     09:55                    1/1/0
 GASTR        915xxxxxxx       09:41                    6/5/0
 LUUMD        913xxxxxxx      09:12                    1/1/0
 PHILM        915xxxxxxx       09:12                    6/5/0
 QDXAPHSP     919xxxxxxx     09:04                    9/7/0
 SON          913xxxxxxx       08:57                    8/5/0

As always I appreciate any assistance, I hope I provided enough information.

[June7]

You have gotten as far as extracting this data from some program (PowerTerm?) and saving as a text file?

VBA code can read the text file and selectively import data to a table. Use that table as RecordSource for a report.

Text import is common topic. Here is one recent thread https://www.accessforums.net/showthr...ccess-database

And an oldie http://www.dbforums.com/6274796-post24.html

Advise no spaces, special characters, punctuation (underscore is exception) in any names, nor reserved words as names.

[redbull]

Very cool, So I am going to start digging into those links. Yea Powerterm is an old unix emulator thing... also known as a nightmare.

[redbull]

Is there a way to do this using regular expression?

[June7]

What do you mean by 'regular expression'? Regular as opposed to what?

[MarvinP]

Hi,

It looks to me you want all records that don't start with the "<" character.
After you import the text you can do a query using a filter of -Not Like "<*"-
Then the only records left will be the ones you want.

[redbull]

Okay, I cannot take credit for this.. We have a ninja here and this is what he came up with..

Sub RegEx()


  Dim oFS As FileSystemObject
  Dim oText As TextStream
  Dim strRawData, strFormattedData As String
  Dim oRegEx As RegExp
  Dim oMatches As MatchCollection
  Dim oMatch As Match


  Set oRegEx = New RegExp
  oRegEx.Global = True
  oRegEx.Pattern = "\s[A-Z0-9\s]*\s*\d{7,12}\s*[^(]\d\d:\d\d[^)]\s*\d*/\d*/\d*\s"
  ' Other ID    \s[A-Z0-9]\s*
  ' Invoice #   \d{10,}\s*
  ' Time        [^(]\d\d:\d\d[^)]
  ' Date        \s\d*/\d*/\d*\s
    
  Set oFS = New FileSystemObject
  Set oText = oFS.OpenTextFile("C:\Program Files\LIFT Ericom Software\PowerTerm\trace.log", ForReading)
    
  strRawData = oText.ReadAll
    
  oText.Close
  Set oText = Nothing
  
  Set oMatches = oRegEx.Execute(strRawData)


  Set oText = oFS.OpenTextFile("C:\TroubleList\Atlanta\skb " & Format(Date, "mm-dd-yyyy") & ".txt", ForWriting, True)
  
  For Each oMatch In oMatches
    strFormattedData = Replace(oMatch, vbCr, "")
    oText.WriteLine Replace(strFormattedData, vbLf, "")
  Next oMatch
  
  oText.Close
  Set oText = Nothing
  
  Set oRegEx = Nothing
  Set oMatches = Nothing
  
  Set oFS = Nothing


  MsgBox "New Parsed File Created"
End Sub

We were able to test this here http://www.gskinner.com/RegExr/, this will select only the text we want, format the text and save it correctly.