Extracting Data from Multiple Text Files into Excel

I

I3L4CK

New Member
Joined
Apr 24, 2017
Messages
3
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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]MSISDN[/TD]
[TD]CID[/TD]
[TD]City[/TD]
[TD]Location[/TD]
[TD]Last Activity[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]93751425811[/TD]
[TD]52412[/TD]
[TD]Baghlan[/TD]
[TD]BGNG074_KOH GADAI - 2[/TD]
[TD]2017-04-22 05:26:59[/TD]
[TD]Attached[/TD]
[/TR]
[TR]
[TD]93752425811[/TD]
[TD]45872[/TD]
[TD]Takhar[/TD]
[TD]TKRG024_GOLA-E-BAGH - 2[/TD]
[TD]2017-04-22 06:17:09[/TD]
[TD]Detached[/TD]
[/TR]
[TR]
[TD]93702478445[/TD]
[TD][/TD]
[TD][/TD]
[TD]Subscriber Not Found[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




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:

p0QEkBI.jpg


+++ 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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Found my own solution for anyone who has the same problem:

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Vba to loop on a text file and extract data into excel ( reading text file from vba)
2
Replies
16
Views
8K
Mankum
M

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top