Need to capture Data from Huge .Txt file to Excel

D

dorairaj

New Member
Joined
Dec 2, 2017
Messages
1
Dear Team,

Do needful as iam having txt fine contains data in below mention format

================================================================================
TRAIL EXT_NPLC_VERIZON COMMUNICATIONS INDIA PVT LTD_4 MBPS_0127402731980_SRF-731980-100915-VC12_01
================================================================================


***** MAIN ROUTE *******


HR42_TTR-HIG_G1/r01s1b01p01 01/1/4.3
HR42_DRU-HIG_G2/r01s1b01p02 01/1/4.3
HR42_DRU-HIG_G2/r01s1b01p01 01/1/4.3
HR42_DRU-HIG_G1/r01s1b02p01 01/1/4.3
HR42_DRU-HIG_G1/r01s1b01p01 01/1/4.3
HR60_DHR-HIG_01/r01s1b31p002 01/1/4.3
HR60_DHR-HIG_01/r01s1b37p001 37/2/5.2 VSB#14-DHARUHERA#37=PROTECTED VIA BHIWADI-90298
HR60_DEL-VSB_06/r01s1b33p001 14/2/5.2 VSB#14-DHARUHERA#37=PROTECTED VIA BHIWADI-90298
HR60_DEL-VSB_06/r01s1b34p001 08/2/7.3
HR60_DEL-VSB_05/r01s1b28p001 08/2/7.3
HR60_DEL-VSB_05/r01s1b32p001 01/2/2.3
VNE_DEL-XDM_01.SL-I01/PT01 01/2/2.3


***** SPARE ROUTE *******


HR42_TTR-HIG_G1/r01s1b01p02 01/1/4.3
HR42_JNW-HIG_G1/r01s1b01p01 01/1/4.3
HR42_JNW-HIG_G1/r01s1b01p02 01/1/4.3
HR42_RWI-HSK_G1/r01s1b01p02 01/1/4.3
HR42_RWI-HSK_G1/r01s1b01p01 01/1/4.3
HR62_RWI-SDU_01/r01s1b10p05 01/1/4.3
HR62_RWI-SDU_01/r01s1b10p01 02/1/1.3
HR60_RWI-GKL_01/r01s1b35p001 02/1/1.3
HR60_RWI-GKL_01/r01s1b26p001 13/3/6.1 GOKUL BAZAR-DADRI-BHIWANI-HANSI
HR60_HNS-BRG_02/r01s1b28p001 37/3/6.1 GOKUL BAZAR-DADRI-BHIWANI-HANSI
HR60_HNS-BRG_02/r01s1b36p001 11/1/5.1
HR60_DEL-VSB_03/r01s1b33p001 11/1/5.1
HR60_DEL-VSB_03/r01s1b32p001 04/3/5.2
HR60_DEL-VSB_05/r01s1b38p001 04/3/5.2
HR60_DEL-VSB_05/r01s1b32p001 01/2/2.3


================================================================================
TRAIL VSB#14-DHARUHERA#37=PROTECTED VIA BHIWADI-90298
================================================================================


***** MAIN ROUTE *******


HR60_DHR-HIG_01/r01s1b37p001 37
DHR-HIG_01 SL-37 P-01 TOWARDS DELHI 37
NEW VNE-- 37
VNE_DEL-VSB_03.SLXX/PTYY 37
VNE_DEL-VSB_03.SLxx/PT01 35
VNE_DEL-XDM_01.SL-678/PT04 35
VNE_DEL-XDM_01.SL I/12-02 14
HR60_DEL-VSB_06/r01s1b33p001 14


================================================================================
TRAIL GOKUL BAZAR-DADRI-BHIWANI-HANSI
================================================================================


***** MAIN ROUTE *******


HR60_RWI-GKL_01/r01s1b26p001 13
HR60_RWI-GSM_01/r01s1b33p001 13
HR60_RWI-GSM_01/r01s1b37p001 62
HR60_KNA-HIG_01/r01s1b25p001 62
HR60_KNA-HIG_01/r01s1b34p001 62
HR60_DDR-HIG_02/r01s1b25p001 62
HR60_DDR-HIG_02/r01s1b37p001 62
HR60_BWN-HLS_01/r01s1b28p001 62
HR60_BWN-HLS_01/r01s1b25p001 61
HR60_BWN-GSM_01/r01s1b28p001 61
HR60_BWN-GSM_01/r01s1b34p001 62
HR60_HNS-GSM_01/r01s1b37p001 62
HR60_HNS-GSM_01/r01s1b25p001 37
HR60_HNS-GSM_02/r01s1b25p001 37
HR60_HNS-GSM_02/r01s1b28p001 37
HR60_HNS-BRG_02/r01s1b28p001 37


================================================================================
TRAIL EXT_NPLC_VERIZON COMMUNICATIONS INDIA PVT LTD_4 MBPS_0127402731980_SRF-731980-100915-VC12_02
================================================================================


***** MAIN ROUTE *******


HR42_TTR-HIG_G1/r01s1b01p01 01/2/1.2
HR42_DRU-HIG_G2/r01s1b01p02 01/2/1.2
HR42_DRU-HIG_G2/r01s1b01p01 01/2/1.2
HR42_DRU-HIG_G1/r01s1b02p01 01/2/1.2
HR42_DRU-HIG_G1/r01s1b01p01 01/1/6.1
HR60_DHR-HIG_01/r01s1b31p002 01/1/6.1
HR60_DHR-HIG_01/r01s1b37p001 37/2/5.1 VSB#14-DHARUHERA#37=PROTECTED VIA BHIWADI-90298
HR60_DEL-VSB_06/r01s1b33p001 14/2/5.1 VSB#14-DHARUHERA#37=PROTECTED VIA BHIWADI-90298
HR60_DEL-VSB_06/r01s1b34p001 47/2/3.2
HR60_DEL-VSB_05/r01s1b28p001 47/2/3.2
HR60_DEL-VSB_05/r01s1b32p001 01/2/3.3
VNE_DEL-XDM_01.SL-I01/PT01 01/2/3.3


***** SPARE ROUTE *******


HR42_TTR-HIG_G1/r01s1b01p02 01/2/1.2
HR42_JNW-HIG_G1/r01s1b01p01 01/2/1.2
HR42_JNW-HIG_G1/r01s1b01p02 01/2/1.2
HR42_RWI-HSK_G1/r01s1b01p02 01/2/1.2
HR42_RWI-HSK_G1/r01s1b01p01 01/2/2.2
HR62_RWI-SDU_01/r01s1b10p05 01/2/2.2
HR62_RWI-SDU_01/r01s1b10p01 02/3/1.2
HR60_RWI-GKL_01/r01s1b35p001 02/3/1.2
HR60_RWI-GKL_01/r01s1b26p001 02/2/5.3
HR60_RWI-GSM_01/r01s1b33p001 02/2/5.3
HR60_RWI-GSM_01/r01s1b37p001 05/2/7.3 REWARI GSM M-DADRI MUX VC4S1
HR60_DDR-HIG_02/r01s1b25p001 05/2/7.3 REWARI GSM M-DADRI MUX VC4S1
HR60_DDR-HIG_02/r01s1b37p001 03/1/6.1
HR60_BWN-HLS_01/r01s1b28p001 03/1/6.1
HR60_BWN-HLS_01/r01s1b25p001 03/1/2.2
HR60_BWN-GSM_01/r01s1b28p001 03/1/2.2
HR60_BWN-GSM_01/r01s1b34p001 01/3/5.2
HR60_HNS-GSM_01/r01s1b37p001 01/3/5.2
HR60_HNS-GSM_01/r01s1b34p001 05/3/6.2
HR60_HNS-BRG_01/r01s1b25p001 05/3/6.2
HR60_HNS-BRG_01/r01s1b24p001 13/3/1.1
HR60_HNS-BRG_02/r01s1b31p001 13/3/1.1
HR60_HNS-BRG_02/r01s1b36p001 01/1/4.2
HR60_DEL-VSB_03/r01s1b33p001 01/1/4.2
HR60_DEL-VSB_03/r01s1b32p001 11/1/2.3
HR60_DEL-VSB_05/r01s1b38p001 11/1/2.3
HR60_DEL-VSB_05/r01s1b32p001 01/2/3.3


================================================================================
TRAIL VSB#14-DHARUHERA#37=PROTECTED VIA BHIWADI-90298
================================================================================


***** MAIN ROUTE *******


HR60_DHR-HIG_01/r01s1b37p001 37
DHR-HIG_01 SL-37 P-01 TOWARDS DELHI 37
NEW VNE-- 37
VNE_DEL-VSB_03.SLXX/PTYY 37
VNE_DEL-VSB_03.SLxx/PT01 35
VNE_DEL-XDM_01.SL-678/PT04 35
VNE_DEL-XDM_01.SL I/12-02 14
HR60_DEL-VSB_06/r01s1b33p001 14


================================================================================
TRAIL REWARI GSM M-DADRI MUX VC4S1
================================================================================


where I need Same data to be converted to excel file as

Below

[TABLE="width: 838"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]SR.No[/TD]
[TD]TRAIL[/TD]
[TD]
***** MAIN ROUTE *******[/TD]
[TD]***** SPARE ROUTE *******[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]EXT_NPLC_VERIZON COMMUNICATIONS INDIA PVT LTD_4 MBPS_0127402731980_SRF-731980-100915-VC12_01[/TD]
[TD]HR42_TTR-HIG_G1/r01s1b01p01 01/1/4.3
HR42_DRU-HIG_G2/r01s1b01p02 01/1/4.3
HR42_DRU-HIG_G2/r01s1b01p01 01/1/4.3
HR42_DRU-HIG_G1/r01s1b02p01 01/1/4.3
HR42_DRU-HIG_G1/r01s1b01p01 01/1/4.3
HR60_DHR-HIG_01/r01s1b31p002 01/1/4.3
HR60_DHR-HIG_01/r01s1b37p001 37/2/5.2 VSB#14-DHARUHERA#37=PROTECTED VIA BHIWADI-90298
HR60_DEL-VSB_06/r01s1b33p001 14/2/5.2 VSB#14-DHARUHERA#37=PROTECTED VIA BHIWADI-90298
HR60_DEL-VSB_06/r01s1b34p001 08/2/7.3
HR60_DEL-VSB_05/r01s1b28p001 08/2/7.3
HR60_DEL-VSB_05/r01s1b32p001 01/2/2.3
VNE_DEL-XDM_01.SL-I01/PT01 01/2/2.3 [/TD]
[TD]HR42_TTR-HIG_G1/r01s1b01p02 01/1/4.3
HR42_JNW-HIG_G1/r01s1b01p01 01/1/4.3
HR42_JNW-HIG_G1/r01s1b01p02 01/1/4.3
HR42_RWI-HSK_G1/r01s1b01p02 01/1/4.3
HR42_RWI-HSK_G1/r01s1b01p01 01/1/4.3
HR62_RWI-SDU_01/r01s1b10p05 01/1/4.3
HR62_RWI-SDU_01/r01s1b10p01 02/1/1.3
HR60_RWI-GKL_01/r01s1b35p001 02/1/1.3
HR60_RWI-GKL_01/r01s1b26p001 13/3/6.1 GOKUL BAZAR-DADRI-BHIWANI-HANSI
HR60_HNS-BRG_02/r01s1b28p001 37/3/6.1 GOKUL BAZAR-DADRI-BHIWANI-HANSI
HR60_HNS-BRG_02/r01s1b36p001 11/1/5.1
HR60_DEL-VSB_03/r01s1b33p001 11/1/5.1
HR60_DEL-VSB_03/r01s1b32p001 04/3/5.2
HR60_DEL-VSB_05/r01s1b38p001 04/3/5.2
HR60_DEL-VSB_05/r01s1b32p001 01/2/2.3[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
The below code will not produce the result that you exactly want, but may help you for now;

Code: 
Sub Test()
    Dim MyFile As String
    Dim InputData As String
    Dim strCheck1 As String, strCheck2 As String, strCheck3 As String
    
    strCheck1 = "TRAIL"
    strCheck2 = "***** MAIN ROUTE *******"
    strCheck3 = "***** SPARE ROUTE *******"
    
    MyFile = "[COLOR=#ff0000][B]C:\TestFolder\TestDocument.txt[/B][/COLOR]"
    
    Open MyFile For Input As #1
        i = 1
        Do While Not EOF(1)
            Line Input #1, InputData
            If Left(InputData, 1) = "=" Or InputData = "" Then GoTo 10:
            If Left(InputData, 5) = strCheck1 Then MyColumn = 1
            If InputData = strCheck2 Then MyColumn = 2
            If InputData = strCheck3 Then MyColumn = 3
            Cells(i, MyColumn) = InputData
            i = i + 1
10:
        Loop
    Close #1
End Sub
 
Last edited:
Upvote 0
As I've seen that no answer has been given at the cross-posted site, I wanted to post my revised code, which I assume will satisfy the needs.

Code: 
Sub Test_2()
    Dim strCheck1 As String, strCheck2 As String, strCheck3 As String
    Dim MyFile As String
    Dim InputData As String
    Dim MyColumn As Byte
    Dim i As Long, x1 As Long, x2 As Long, x3 As Long
    
    ActiveSheet.Cells.ClearContents
    
    strCheck1 = "TRAIL"
    strCheck2 = "***** MAIN ROUTE *******"
    strCheck3 = "***** SPARE ROUTE *******"
    
    MyFile = Application.GetOpenFilename
    If MyFile = "False" Then Exit Sub
    
    Range("A1") = strCheck1
    Range("B1") = strCheck2
    Range("C1") = strCheck3
    
    Open MyFile For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
        i = 0
        Do While Not EOF(1)
            Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , InputData
            
            If Left(InputData, 1) = "=" Or InputData = "" Then GoTo 10:
            
            If Left(InputData, 5) = strCheck1 Then MyColumn = 1
            If InputData = strCheck2 Then MyColumn = 2
            If InputData = strCheck3 Then MyColumn = 3
            
            If MyColumn = 1 Then
                x2 = Cells(65536, 2).End(xlUp).Row
                x3 = Cells(65536, 3).End(xlUp).Row
                i = Application.WorksheetFunction.Max(x2, x3) + 1
            ElseIf MyColumn = 2 Then
                x2 = Cells(65536, 2).End(xlUp).Row
                x3 = Cells(65536, 3).End(xlUp).Row
                i = Application.WorksheetFunction.Max(x2, x3) + 1
                If InputData = strCheck2 Then GoTo 10:
            ElseIf MyColumn = 3 Then
                x1 = Cells(65536, 1).End(xlUp).Row
                x3 = Cells(65536, 3).End(xlUp).Row
                i = Application.WorksheetFunction.Max(x1 - 1, x3) + 1
                If InputData = strCheck3 Then GoTo 10:
            End If
                Cells(i, MyColumn) = InputData
10:
        Loop
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub
 
Upvote 0
And, the below code will include the caption "SR.No" as a column label in column-A and the new trails in column B will be numbered acoordingly.

The last revised code is;

Code: 
Sub Test_3()
    Dim strCheck1 As String, strCheck2 As String, strCheck3 As String
    Dim MyFile As String
    Dim InputData As String
    Dim MyColumn As Byte
    Dim i As Long, x1 As Long, x2 As Long, x3 As Long
    
    ActiveSheet.Cells.ClearContents
    
    strCheck1 = "TRAIL"
    strCheck2 = "***** MAIN ROUTE *******"
    strCheck3 = "***** SPARE ROUTE *******"
    
    MyFile = Application.GetOpenFilename
    If MyFile = "False" Then Exit Sub
    
    Range("A1") = "SR.No"
    Range("B1") = strCheck1
    Range("C1") = strCheck2
    Range("D1") = strCheck3
    Range("A1:D1").Font.Bold = True
    Range("A1:D1").Font.Color = vbRed
    
    Open MyFile For Input As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
        i = 0
        Do While Not EOF(1)
            Line Input [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , InputData
            
            If Left(InputData, 1) = "=" Or InputData = "" Then GoTo 10:
            
            If Left(InputData, 5) = strCheck1 Then
                MyColumn = 2
                j = j + 1
            End If
            If InputData = strCheck2 Then MyColumn = 3
            If InputData = strCheck3 Then MyColumn = 4
            
            If MyColumn = 2 Then
                x3 = Cells(65536, 3).End(xlUp).Row
                x4 = Cells(65536, 4).End(xlUp).Row
                i = Application.WorksheetFunction.Max(x3, x4) + 1
                Cells(i, 1) = j
            ElseIf MyColumn = 3 Then
                x3 = Cells(65536, 3).End(xlUp).Row
                x4 = Cells(65536, 4).End(xlUp).Row
                i = Application.WorksheetFunction.Max(x3, x4) + 1
                If InputData = strCheck2 Then GoTo 10:
            ElseIf MyColumn = 4 Then
                x2 = Cells(65536, 2).End(xlUp).Row
                x4 = Cells(65536, 4).End(xlUp).Row
                i = Application.WorksheetFunction.Max(x2 - 1, x4) + 1
                If InputData = strCheck3 Then GoTo 10:
            End If
                Cells(i, MyColumn) = InputData
10:
        Loop
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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