Extracting Data from Multiple Text Files into Excel

[I3L4CK]

Dear All,

I need VBA code to extract certain data from multiple text files into a table. I tried to code it myself but couldn't figure out everything by myself so I'm seeking help. There is also a Hexadecimal value in text files that I need to convert to Decimal in the final table. I also need to put in the city according to the 3 character code in the beginning the location so if it is possible to do it like vlookup so the corresponding code can be replaced with a city which is manually inserted in the code. I need the table to look like this:

MSISDN	CID	City	Location	Last Activity	Status
93751425811	52412	Baghlan	BGNG074_KOH GADAI - 2	2017-04-22 05:26:59	Attached
93752425811	45872	Takhar	TKRG024_GOLA-E-BAGH - 2	2017-04-22 06:17:09	Detached
93702478445			Subscriber Not Found

<tbody>
</tbody>

I need below bold data from the text files:

MSISDN = 93751425811
Cell Identity = 412011F7CA1BB -> this needs to be converted to Decimal
Cell Identity Name = BGNG040_KOH-E-MARKHANA - 3 -> first 3 letters (BGN) should be associated with city "Baghlan" from a list
IMSI Attach Flag = Attached
Date and time of last action = 2017-04-22 06:44:37+04:30

Below is the actual log file:

+++ MSOFTX/*MEID:5 MENAME:KNZMSX06*/ 2017-04-22 07:05:07+04:30
O&M #251
%%DSP USRINF: UNT=MSISDN, D=K'9370822XXXX;%%
RETCODE = 0 Operation succeeded

IMSI=41201200499XXXX user basic info
------------------------------------
MSISDN = 9370822XXXX
(Number of results = 1)

USER DYNAMIC INFO
-----------------
IMSI = 41201200499XXXX
ownMSISDN = NULL
TMSI = 5610F59A
IMEI = 35514507655XXXX
IMEI reused times = 6
Roaming Type = Local Subscriber
Mobile station = Double Freq Mobile
Available times authentication sets can be used = 32
Location Area Identity = 412011F7C
Location Area Name = KDZBSC48_LAC8060
Cell Identity = 412011F7CA1BB
Cell Identity Name = BGNG040_KOH-E-MARKHANA - 3
MSC Number = 937023XXXX
VLR Number = 937023XXXX
HLR Number = 9370299XXXX
BSC index = 14
RNC ID = 65535
IMSI Attach Flag = Attached
Radio Confirm Flag = Confirmed
Subscriber Data Confirmed by HLR = Confirmed
Location Info Confirmed in HLR = Confirmed
Enhance Roaming Restrict = No
UnSupport feature Roaming Restrict = No
Zone code Roaming Restrict = No
National Roaming Restrict = No
Mobile Station Not Reachable Flag (MNRF) = Reachable
Camel Announce Suppressed = No
Gs Association Status = Not Associated
SMS Capability = Yes
LCS Notify Capability = No
LCS Processing = No
RR Status = Idle
Paging Type = Idle
SGSN Number = NULL
Associate Detach Cause = Normal
Date and time of registration = 2017-04-21 13:32:40+04:30
Date and time of last action = 2017-04-22 06:44:37+04:30
Type of last action = Mobile Terminating Call Service
IMEI check counter = 7
Equipment status = White listed
Access network type = GERAN
Server Index = 0
Module in which subscriber exists = 1303
Call seqence = 255
Super-Charger subscriber type = Not Super-Charger subscriber
Is Backup User = No
Backup Resource MSC Name = Invalid
Is SIP User = No
SMS Restrict Status = Unrestricted
Is ICS User = No
SGs Association Status = Not Associated
MME Number = NULL
SGs Associate Detach Cause = Normal
Is IM-SSF User = No
Is CSFB Proxy User = No
(Number of results = 1)

USER BASIC SIGN INFO
--------------------
User Type = Common user
Is O-CSI CAMEL User = Yes
Is D-CSI CAMEL User = No
Is VT-CSI CAMEL User = No
Is MO-SMS-CSI CAMEL User = Yes
Is SS-CSI CAMEL User = No
Is M-CSI CAMEL User = No
Is LCS User = No
Is MT-CSI CAMEL User = No
IST alert timer = 0 minutes
Allowed Roaming Area Num(ZoneCode) = All Area
Charging Area Code Number = All Area
ODB Subscriber Status = No
ODB All outgoing calls Barred = No
ODB International outgoing calls Barred = No
ODB International Outgoing calls not to HPLMN Country Barred = No
ODB Premium Rate Information Outgoing calls Barred = No
ODB Premium Rate Entertainment Outgoing calls Barred = No
ODB Supplement Service Access Barred = No
PLMN Specific Barring Type1 = No
PLMN Specific Barring Type2 = No
PLMN Specific Barring Type3 = No
PLMN Specific Barring Type4 = No
All ODB ECT Services Barred = No
ODB ECT Service Barred when an ECT Initiator Is Charged for at Least One Call = No
ODB ECT Service Barred when an ECT Initiator Is charged Based on the International Charge Rate for at Least One Call = No
ODB ECT Service Barred when an ECT Initiator Is Charged Based on the Inter-Area Charge Rate for at Least One Call = No
ODB ECT Service Barred when an ECT Initiator Is Charged for Two Calls = No
ODB Subscribers Not Allowed to Originate Multiple ECT Services = No
All ODB Outgoing Calls Roaming to the Home PLMN Country Barred = No
Support NAEA = No
NAEA Code = NULL
Line2 MSISDN = NULL
VP MSISDN = NULL
eMLPP Max Entitled Priority = No Priority Applied
eMLPP Default Priority = No Priority Applied
Access restriction data = No AccessRestriction
E-Category = Invalid
EMA = Invalid
R-Category = Invalid
CARP = Invalid
Is HOTBILL User = No
Is PPS User = No
Extension Service Code = Invalid
Account code length = No signature
Is special LCS subscriber = No
Intelligent Network Supplement Service Code 1 = Invalid
Intelligent Network Supplement Service Code 2 = Invalid
Addrou Code = 0
Call Redirection Index = Invalid
ICS Indicator = No
Intelligent SMS SCP Address = NULL
Intelligent SMS Detection Point Name = No
Intelligent SMS Service Key = NULL
Intelligent SMS Triggering All Multiple Messages = No
Intelligent MM SCP Address = NULL
Intelligent MM Detection Point Name = NULL
Intelligent MM Service Key = NULL
Intelligent MM Location Update Type = NULL
Maximum call duration allowed by MOC anti-fraud = 65535-Unlimited
Processing mode when the maximum call duration allowed by MOC anti-fraud is reached = -
Maximum call duration allowed by CF anti-fraud = 65535-Unlimited
Processing mode when the maximum call duration allowed by CF anti-fraud is reached = -
(Number of results = 1)

--- END

+++ MSOFTX/*MEID:5 MENAME:KNZMSX06*/ 2017-04-22 07:05:09+04:30
O&M #253
%%DSP USRINF: UNT=MSISDN, D=K'9370502XXXX;%%
RETCODE = 0 Operation succeeded

IMSI=41201210301XXXX user basic info
------------------------------------
MSISDN = 9370502XXXX
(Number of results = 1)

USER DYNAMIC INFO
-----------------
IMSI = 41201210301XXXX
ownMSISDN = NULL
TMSI = 4110AB74
IMEI = 35634205646XXXX
IMEI reused times = 9
Roaming Type = Local Subscriber
Mobile station = Double Freq Mobile
Available times authentication sets can be used = 1
Location Area Identity = 412011F68
Location Area Name = BGNLAC_8040
Cell Identity = 412011F68A390
Cell Identity Name = BGNG087_BAGHLAN-E-JADID_MTN - 2
MSC Number = 937023XXXX
VLR Number = 937023XXXX
HLR Number = 9370299XXXX
BSC index = 5
RNC ID = 65535
IMSI Attach Flag = Attached
Radio Confirm Flag = Confirmed
Subscriber Data Confirmed by HLR = Confirmed
Location Info Confirmed in HLR = Confirmed
Enhance Roaming Restrict = No
UnSupport feature Roaming Restrict = No
Zone code Roaming Restrict = No
National Roaming Restrict = No
Mobile Station Not Reachable Flag (MNRF) = Reachable
Camel Announce Suppressed = No
Gs Association Status = Not Associated
SMS Capability = Yes
LCS Notify Capability = No
LCS Processing = No
RR Status = Idle
Paging Type = Idle
SGSN Number = NULL
Associate Detach Cause = Normal
Date and time of registration = 2016-12-29 13:46:51+04:30
Date and time of last action = 2017-04-22 05:54:01+04:30
Type of last action = Location Update Periodic
IMEI check counter = 1083
Equipment status = White listed
Access network type = GERAN
Server Index = 0
Module in which subscriber exists = 1302
Call seqence = 255
Super-Charger subscriber type = Not Super-Charger subscriber
Is Backup User = No
Backup Resource MSC Name = Invalid
Is SIP User = No
SMS Restrict Status = Unrestricted
Is ICS User = No
SGs Association Status = Not Associated
MME Number = NULL
SGs Associate Detach Cause = Normal
Is IM-SSF User = No
Is CSFB Proxy User = No
(Number of results = 1)

USER BASIC SIGN INFO
--------------------
User Type = Common user
Is O-CSI CAMEL User = Yes
Is D-CSI CAMEL User = No
Is VT-CSI CAMEL User = No
Is MO-SMS-CSI CAMEL User = Yes
Is SS-CSI CAMEL User = No
Is M-CSI CAMEL User = No
Is LCS User = No
Is MT-CSI CAMEL User = No
IST alert timer = 0 minutes
Allowed Roaming Area Num(ZoneCode) = All Area
Charging Area Code Number = All Area
ODB Subscriber Status = No
ODB All outgoing calls Barred = No
ODB International outgoing calls Barred = No
ODB International Outgoing calls not to HPLMN Country Barred = No
ODB Premium Rate Information Outgoing calls Barred = No
ODB Premium Rate Entertainment Outgoing calls Barred = No
ODB Supplement Service Access Barred = No
PLMN Specific Barring Type1 = No
PLMN Specific Barring Type2 = No
PLMN Specific Barring Type3 = No
PLMN Specific Barring Type4 = No
All ODB ECT Services Barred = No
ODB ECT Service Barred when an ECT Initiator Is Charged for at Least One Call = No
ODB ECT Service Barred when an ECT Initiator Is charged Based on the International Charge Rate for at Least One Call = No
ODB ECT Service Barred when an ECT Initiator Is Charged Based on the Inter-Area Charge Rate for at Least One Call = No
ODB ECT Service Barred when an ECT Initiator Is Charged for Two Calls = No
ODB Subscribers Not Allowed to Originate Multiple ECT Services = No
All ODB Outgoing Calls Roaming to the Home PLMN Country Barred = No
Support NAEA = No
NAEA Code = NULL
Line2 MSISDN = NULL
VP MSISDN = NULL
eMLPP Max Entitled Priority = No Priority Applied
eMLPP Default Priority = No Priority Applied
Access restriction data = No AccessRestriction
E-Category = Invalid
EMA = Invalid
R-Category = Invalid
CARP = Invalid
Is HOTBILL User = No
Is PPS User = No
Extension Service Code = Invalid
Account code length = No signature
Is special LCS subscriber = No
Intelligent Network Supplement Service Code 1 = Invalid
Intelligent Network Supplement Service Code 2 = Invalid
Addrou Code = 0
Call Redirection Index = Invalid
ICS Indicator = No
Intelligent SMS SCP Address = NULL
Intelligent SMS Detection Point Name = No
Intelligent SMS Service Key = NULL
Intelligent SMS Triggering All Multiple Messages = No
Intelligent MM SCP Address = NULL
Intelligent MM Detection Point Name = NULL
Intelligent MM Service Key = NULL
Intelligent MM Location Update Type = NULL
Maximum call duration allowed by MOC anti-fraud = 65535-Unlimited
Processing mode when the maximum call duration allowed by MOC anti-fraud is reached = -
Maximum call duration allowed by CF anti-fraud = 65535-Unlimited
Processing mode when the maximum call duration allowed by CF anti-fraud is reached = -
(Number of results = 1)

--- END

There are also failed results that can be extracted to but not necessary and can be done manually.

+++ MSOFTX/*MEID:5 MENAME:KNZMSX06*/ 2017-04-22 07:05:08+04:30
O&M #252
%%DSP USRINF: UNT=MSISDN, D=K'9370008XXXX;%%
RETCODE = 1030 Subscriber number does not exist

 

[I3L4CK]

Bump

 

[I3L4CK]

Found my own solution for anyone who has the same problem:

Sub ImportTextFile()

    Dim rowNumber As Long
    Dim textLine As String
    
      
    rowNumber = 0
    Dim City As Variant
    Dim cityCodes As Variant
    
    City = Array("Badakhshan", "Badghis", "Baghlan", "Balkh", "Bamyan", "Daikundi", "Farah", "Faryab", "Ghazni", "Ghor", "Herat", "Hilmand", "Jawzjan", "Kabul", "Kandahar", "Kapisa", "Khost", "Kunar", "Kunduz", "Laghman", "Logar", "Nangarhar", "Nimroz", "Nuristan", "Paktika", "Paktya", "Panjsher", "Parwan", "Samangan", "Sar-e-Pol", "Takhar", "Uruzgan", "Wardak", "Zabul")
    cityCodes = Array("BDN", "BDS", "BGN", "BLK", "BMN", "DKD", "FRH", "FRB", "GZI", "GWR", "HRT", "HLD", "JZN", "KBL", "KDR", "KPA", "KST", "KNR", "KNZ", "LGN", "LGR", "NGR", "NMZ", "NRN", "PKK", "PKA", "PJR", "PRN", "SMN", "SPL", "TKR", "UZN", "WRK", "ZBL")
    
    Open "D:\AWCC Documents\Temp\SuccessMML_Result_2017-05-02-12-36-12.txt" For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
    ActiveSheet.Cells.ClearContents
    
    While Not EOF(1)
        Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , textLine

        If InStr(textLine, "D=K'") = 27 Then
            Cells(2, 1).Offset(rowNumber, 0).Value = Mid(textLine, 31, 11)
            rowNumber = rowNumber + 1

        ElseIf InStr(textLine, "Cell Identity") = 35 Then
            Cells(1, 2).Offset(rowNumber, 0).Value = Application.WorksheetFunction.Hex2Dec(Mid(textLine, 62, 4))
        
        ElseIf InStr(textLine, "Cell Identity Name") = 30 Then
            Cells(1, 3).Offset(rowNumber, 0).NumberFormat = "@"
            Cells(1, 4).Offset(rowNumber, 0).Value = Mid(textLine, 53, 100)
                
                Dim cityCode As String
                Dim cityCodeNu As Integer
                cityCode = Mid(textLine, 53, 3)
                cityCodeNu = Application.WorksheetFunction.Match(cityCode, cityCodes, 0)
                cityCodeNu = cityCodeNu - 1
            Cells(1, 3).Offset(rowNumber, 0).Value = City(cityCodeNu)

        ElseIf InStr(textLine, "IMSI Attach Flag") = 32 Then
            Cells(1, 6).Offset(rowNumber, 0).Value = Mid(textLine, 53, 8)
            
        ElseIf InStr(textLine, "MENAME") = 23 Then
            Dim meName As String
            meName = Mid(textLine, 30, 8)
            ActiveSheet.Name = meName
            
        ElseIf InStr(textLine, "Date and time") = 20 Then
            Cells(1, 5).Offset(rowNumber, 0).NumberFormat = "@"
            Cells(1, 5).Offset(rowNumber, 0).Value = Mid(textLine, 53, 19)
               
        End If
    Wend
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
         
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "MSISDN"
    Selection.NumberFormat = "@"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "CID"
    Selection.NumberFormat = "@"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "City"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Site"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Last Activity"
    Selection.NumberFormat = "@"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Status"
    Range("A1:F1").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Columns("A:A").ColumnWidth = 12
    Columns("B:B").ColumnWidth = 7
    Columns("C:C").ColumnWidth = 10
    Columns("D:D").ColumnWidth = 40
    Columns("E:E").ColumnWidth = 18.5
    Columns("F:F").ColumnWidth = 10
    ActiveSheet.Range("A1").CurrentRegion.Select
    
End Sub