Read txt, Text file into VBA, search for strings, concatenate strings 1

[theavayaguy]

A) I have a text file which is appends the same report file for one day. The day/time of the report is written as the header to each report. Here is a snippet of the report file named myfile.txt:


12/22/2006 10:10 AM - Data Export

Voice System name: my pbx - Reports - SYSTEM CAPACITY

System
Used Available Limit
-----------------------
HUNT GROUPS, SPLITS, OR SKILLS
Groups/Splits/Skills: 861 1139 2000
Administered Logical Agents: 8863 11137 20000
Administered Logical Agent-Skill Pairs: 27061 152939 180000
Logged-In ACD Agents: 908 892 1800

->snip ...

12/22/2006 10:40 AM - Data Export

Voice System name: my pbx - Reports - SYSTEM CAPACITY

System
Used Available Limit
-----------------------
HUNT GROUPS, SPLITS, OR SKILLS
Groups/Splits/Skills: 861 1139 2000
Administered Logical Agents: 8863 11137 20000
Administered Logical Agent-Skill Pairs: 27061 152939 180000
Logged-In ACD Agents: 910 889 1800


B) So I want to read in myfile.txt, look for text "Data Export" assign it to a variable and then look for text "Logged-In ACD Agents" and write assign it another variable. Then concatenate both of those variables to a new variable and finally write out this new variable to a file called test.txt. See snippet of pseudo test.txt below. "My objective is to look through this file for occuranecs of Data Export, then the next appearance of "Logged-In ACD Agents" and write them like this":

12/22/2006 10:10 AM - Data Export Logged-In ACD Agents: 908 892 1800
12/22/2006 10:40 AM - Data Export Logged-In ACD Agents: 910 889 1800


C) Here is my code:

Dim strTemp, strTemp2, strFile, strFile2, CombinedString As String
Private Sub GetLine() 'read first string from file to variable
strFile = "c:\myfile.txt" 'your file with full path file must exist before you read from it
strFile2 = "c:\temp\test.txt"

Open strFile For Input As 1 'open text file for reading
Open strFile2 For Append As 2 'open text file for writing, appends to what is there.

Do While Not EOF(1)

Line Input #1, strTemp 'read one line of text file into variable

If InStr(strTemp, "Data Export") > 0 Then 'looks for "Data Export" value in strTemp

Do While Not EOF(1)

Line Input #1, strTemp2 'read first string to variable

If InStr(strTemp2, "Logged-In ACD Agents") > 0 Then

CombinedString = strTemp + strTemp2
Print #2, CombinedString ' prints new combined string

End If
Loop

End If

Loop ' until the last line is read

Close #1
Close #2

End Sub

D) Here is the output I get into test.txt:

12/22/2006 10:10 AM - Data Export Logged-In ACD Agents: 908 892 1800
12/22/2006 10:10 AM - Data Export Logged-In ACD Agents: 910 889 1800


E) You will notice the first occurance of the "Data Export" line is repeated. I think I need another nested loop, any ideads?

I am newbie to vba.

Thanks,
theavayaguy

 

[PHV]

And what about this ?

Private Sub GetLine()
Dim strTemp As String, strTemp2 As String, strFile As String, strFile2 As String
Dim intFlag As Integer
strFile = "c:\myfile.txt"
strFile2 = "c:\temp\test.txt"
Open strFile For Input As 1
Open strFile2 For Append As 2
intFlag = 0
Do While Not EOF(1)
  Line Input #1, strTemp    'read one line of text file into variable
  If intFlag = 0 Then
    If InStr(strTemp, "Data Export") > 0 Then
      strTemp2 = strTemp
      intFlag = 1
    End If
  ElseIf intFlag = 1 Then
    If InStr(strTemp, "Logged-In ACD Agents") > 0 Then
      Print #2, strTemp2 & strTemp
      intFlag = 0
    End If
  End If
Loop
Close #1
Close #2
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[theavayaguy]

PHV,

Simply brilliant, it does exactly what I needed.

Thanks.

Happy Holidays!

theavayaguy

 

[WinblowsME]

This is pretty much the same.

Sub Get_Line()
Dim in_file As String, out_file As String, curr_line As String, date_time As String

in_file = "C:\in.txt"
out_file = "C:\out.txt"

Open out_file For Append As #1
Open in_file For Input As #2
Do While Not EOF(2)
Line Input #2, curr_line

If InStr(1, curr_line, "/") > 0 And InStr(1, UCase(curr_line), "DATA EXPORT") > 0 Then
date_time = curr_line
ElseIf InStr(1, UCase(curr_line), "LOGGED-IN ACD AGENTS:") > 0 Then
Print #1, date_time & " " & curr_line
End If
Loop
Close #2
Close #1
End Sub