Need to capture Data from Huge .Txt file to Excel

dorairaj · Dec 2, 2017

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

SR.No	TRAIL	*** MAIN ROUTE *****	*** SPARE ROUTE *****
1	EXT_NPLC_VERIZON COMMUNICATIONS INDIA PVT LTD_4 MBPS_0127402731980_SRF-731980-100915-VC12_01	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	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

Haluk · Dec 3, 2017

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

Macropod · Dec 3, 2017

Cross-posted at: https://chandoo.org/forum/threads/need-to-capture-data-from-huge-txt-file-to-excel.36596/
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Haluk · Dec 3, 2017

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

Haluk · Dec 3, 2017

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

Need to capture Data from Huge .Txt file to Excel

dorairaj

New Member

Excel Facts

Haluk

Rules Violation

Macropod

Retired Moderator

Haluk

Rules Violation

Haluk

Rules Violation

Forum statistics

Share this page

Need to capture Data from Huge .Txt file to Excel

dorairaj

New Member

Excel Facts

Haluk

Rules Violation

Macropod

Retired Moderator

Haluk

Rules Violation

Haluk

Rules Violation

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock