Get all occurrences of data from text file using VBA

[Vipul_Sharma]

I have a text file which contains lots of repetitive data.

COB TRAC
Green With Comments
Issue Description: Users were not able to use the document upload/download functionality as well as the ARP/TRP module of CoBTrac.
Root cause: GPA working with CATE to help troubleshoot the issue as we migrated from WebBank to Cloud this past Friday (10/20/15). All other Modules are working fine.
Business Impact: Users not able to upload document for CoBTrac.
Compensating Controls: TBD.
Work around: No workaround, GPA/CATE working on resolution.
Issue Reported On: 17th November 2015 – 12:17PM EST
Environment (Prod/UAT): PROD
Technology: Application
Engineer SOEID (Who has worked on this): RK55542
Ticket Type (VT/EMS) & Number & Status: HYPERLINK "https://servicemanagement.citigroup.net/nav_to.do?uri=incident.do%3Fsys_id=6e42f9df6ff34e00dad82fe64f3ee430%26syspa rm_record_list=active=true%5esys_class_name!=u_major_incident"INC0026404246
Issue Category (APP/SA/DB): APP
Issue Severity: Medium

CitiRisk Credit
Green With Comments
Issue Description: The CitiRisk Credit Application Production Oracle RAC database server had one node (of three) go down due to a power issue.
Root Cause: Infrastructure issue - Server Power failure, RC to be determined.
Application Impact : As a result of this issues, 3 listeners and 1 data load job for the application failed. They were immediately re-run to success.
Business Impact: None
Compensating Control: None
Work Around: DBA team is checking with service providers (Oracle & HP) to find a fix to such infra issue. The application team are looking at the reason for the failures of the listeners and data load job.
Issue Reported On (Date/Time): 11:30 PM EST 11/24/2015
Environment (PROD/UAT): PROD
Technology: Oracle
Engineer SOEID (Who is working on this): gg13851
Ticket Type(VT/EMS) & Number & Status: INC0026451991 (SA) & INC0026451020 (DBA)
Issue Category(APP/SA/DB): APP
Issue Severity: Severity - 2 (High)

I need to get the data like Issue Severity, Issue Category, etc for each occurrence.
I am able to open the file and read but I dont know how to get all the required data for all tickets.
I am using following code to open the file



Dim strTestFile2 AsString
strTestFile2 ="C:\Users\"& Environ("Username")&"\Desktop\ETSDaily.txt"
Dim dataArray2 AsVariant
' Open the file and assign data to array
Open strTestFile2 For Input As#1
dataArray2 = Split(Input$(LOF(1),#1), vbLf)
Close #1

Thanks in Advance

[Kenneth Hobs]

Welcome to the forum!

For future posts, please attach a sample file(s) to get the best help. Click the Go Advanced button in the lower right of a reply, click the paperclip icon in the toolbar, and then Browse and Upload. When pasting code, please paste between code tags. You can click the # icon to insert the tags.

If you have just one instance of those strings to search for, you can use Filter() to filter your array. If you need more specifics, please post back.

[Charlize]

Problem you will face that those tickets aren't equally in number of lines. First one has 14 lines, second one 15. ie "Application Impact" isn't provided with the first ticket ... What I say is that you need to check the part before ": " with split and put that in the correct column in excel. If everything was equally logged into a text file you could try this one.

Sub Get_Info_Tickets()'free fileno
Dim fileno As Long
'the lineinfo of the textfile
Dim theinfo As String
'the path
Dim mypath As String
'the file
Dim myfile As String
'rowno in a sheet
Dim rowno As Long
'columnno in a sheet
Dim columnno As Long
'looping through the items of the ticket
Dim theloop As Long
'worksheet holder
Dim myws As Worksheet


mypath = ThisWorkbook.Path & "\"
myfile = "ETSDaily.txt"
'your file ETSDaily.txt resides in the same directory as the workbook
Set myws = ActiveSheet
rowno = 1
columno = 1
    fileno = FreeFile
    Open mypath & myfile For Input As #fileno
    Do While Not EOF(fileno)    ' Loop until end of file.
        Line Input #fileno, theinfo ' Read line into variable.
        If theinfo <> vbNullString Then
            If rowno = 1 Then
'we check for headers to put above the columns
                For theloop = 1 To 14
                    If InStr(1, theinfo, ": ") > 0 Then
                        myws.Cells(rowno, theloop) = Split(theinfo, ": ")(0)
                        myws.Cells(rowno + 1, theloop) = Split(theinfo, ": ")(1)
                    Else
                        myws.Cells(rowno + 1, theloop) = theinfo
                    End If
                    Line Input #fileno, theinfo
                Next theloop
                rowno = rowno + 1
            Else
                For theloop = 1 To 14
                If InStr(1, theinfo, ": ") > 0 Then
                        myws.Cells(rowno + 1, theloop) = Split(theinfo, ": ")(1)
                    Else
                        myws.Cells(rowno + 1, theloop) = theinfo
                    End If
                    Line Input #fileno, theinfo
                Next theloop
                rowno = rowno + 1
            End If
        End If
    Loop
    Close #fileno
End Sub

Charlize

[Charlize]

Little idea, what if "Issue Severity:" is ALWAYS present in your ticket. Then you can loop through your file until EOF but when you find "Issue Severity:" you know that you have to go to a new row with the next line ...

Charlize