Vba to loop on a text file and extract data into excel ( reading text file from vba)

H

Harishiyer

Board Regular
Joined
Apr 19, 2015
Messages
61
Hi All,

I have a query. I have a text file in a path . I want to read the text file from excel . the text file looks as below

Code: 
------START---------
DATE_OF_ACTIVITY : 09182015_1107_WED
CLASS : 4D        SEC : C
REPORT : MONTH_WISE_REPORT           USER : ARJUN
S.NO      NAME              MATHS_MARK     SCIENCE_MARK     TOTAL     AVERAGE
----      ----              ----------     ------------     -----     -------
  JOUID:443345  SERVICE : NAT
0001    HARISH   785.26   856.87   1642.13           M
0002    GANSDD    45.65    10.00     55.65           T
  JOUID:441122  SERVICE : VAT
0003    HASASA    12.00    10.00     22.00           M
0004    HAAFFG    11.00    10.00     21.00           Y
     END OF SERVICE = DATA

CLASS : 5E        SEC : U
REPORT : DAY_WISE_REPORT           USER : SYSTEM
S.NO      NAME              MATHS_MARK     SCIENCE_MARK     TOTAL     AVERAGE SIGN LOGINID    PUBLIC
----      ----              ----------     ------------     -----     -------   ----    -------    ------
  JOUID:AS1234  SERVICE : CAT
0444    MANJIK    18.91    12.11     50.11           I    OPO      LOPOL         Y
0555    LOPING    40.11    10.00     50.11           O    WPO      MOPOL         N
  JOUID:567654  SERVICE : QAT
0666    QWERTY    40.11    10.00     50.11           P    OBB      LUUOL         N
0888    ZXCVBN    40.11    10.00     50.11           R    OPO      LOUOL         N
     END OF SERVICE = GYANN
 
CLASS : 4E        SEC : F
REPORT : MONTH_WISE_REPORT           USER : MANOJ
S.NO      NAME              MATHS_MARK     SCIENCE_MARK     TOTAL     AVERAGE
----      ----              ----------     ------------     -----     -------
  JOUID:44AA11  SERVICE : LAT
0045    gurtyy   785.26   856.87   1642.13           U
0056    MAJOIU    88.65    10.00     98.65           I
0072    LKPOIU    90.00    10.00    100.00           O
0091    ASDEWQ    45.00    10.00     55.00           P
     END OF SERVICE = DATA
 
------END---------

This is just a sample . The text file will be more tha 40000 lines. The output i want to acheive is looks as below



Code: 
[TABLE="width: 996"]
<TBODY>[TR]
[TD]DATE_OF_ACTIVITY</SPAN>
[/TD]
[TD]CLASS</SPAN>
[/TD]
[TD]SEC</SPAN>
[/TD]
[TD]USER</SPAN>
[/TD]
[TD]JOUID</SPAN>
[/TD]
[TD]SERVICE</SPAN>
[/TD]
[TD]S.NO</SPAN>
[/TD]
[TD]NAME</SPAN>
[/TD]
[TD]MATHS_MARK</SPAN>
[/TD]
[TD]SCIENCE_MARK</SPAN>
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]AVERAGE</SPAN>
[/TD]
[TD]END OF SERVICE</SPAN>
[/TD]
[TD]PARAMETER-1</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]443345</SPAN>
[/TD]
[TD]NAT</SPAN>
[/TD]
[TD]0001</SPAN>
[/TD]
[TD]HARISH</SPAN>
[/TD]
[TD]785.26</SPAN>
[/TD]
[TD]856.87</SPAN>
[/TD]
[TD]1642.13</SPAN>
[/TD]
[TD]M</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]HARISH0001</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]443345</SPAN>
[/TD]
[TD]NAT</SPAN>
[/TD]
[TD]0002</SPAN>
[/TD]
[TD]GANSDD</SPAN>
[/TD]
[TD]45.65</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]55.65</SPAN>
[/TD]
[TD]T</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]GANSDD0002</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]441122</SPAN>
[/TD]
[TD]VAT</SPAN>
[/TD]
[TD]0003</SPAN>
[/TD]
[TD]HASASA</SPAN>
[/TD]
[TD]12</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]22</SPAN>
[/TD]
[TD]M</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]HASASA0003</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]441122</SPAN>
[/TD]
[TD]VAT</SPAN>
[/TD]
[TD]0004</SPAN>
[/TD]
[TD]HAAFFG</SPAN>
[/TD]
[TD]11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]21</SPAN>
[/TD]
[TD]Y</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]HAAFFG0004</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0045</SPAN>
[/TD]
[TD]gurtyy</SPAN>
[/TD]
[TD]785.26</SPAN>
[/TD]
[TD]856.87</SPAN>
[/TD]
[TD]1642.13</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]gurtyy0045</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0056</SPAN>
[/TD]
[TD]MAJOIU</SPAN>
[/TD]
[TD]88.65</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]98.65</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]MAJOIU0056</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0072</SPAN>
[/TD]
[TD]LKPOIU</SPAN>
[/TD]
[TD]90</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[TD]O</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]LKPOIU0072</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0091</SPAN>
[/TD]
[TD]ASDEWQ</SPAN>
[/TD]
[TD]45</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]P</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]ASDEWQ0091</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


This data should be moved to a sheet called MONTH_WISE_REPORT


Code: 
[TABLE="width: 1035"]
<TBODY>[TR]
[TD]DATE_OF_ACTIVITY</SPAN>
[/TD]
[TD]CLASS</SPAN>
[/TD]
[TD]SEC</SPAN>
[/TD]
[TD]USER</SPAN>
[/TD]
[TD]JOUID</SPAN>
[/TD]
[TD]SERVICE</SPAN>
[/TD]
[TD]S.NO</SPAN>
[/TD]
[TD]NAME</SPAN>
[/TD]
[TD]MATHS_MARK</SPAN>
[/TD]
[TD]SCIENCE_MARK</SPAN>
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]AVERAGE</SPAN>
[/TD]
[TD]SIGN</SPAN>
[/TD]
[TD]LOGINID</SPAN>
[/TD]
[TD]PUBLIC</SPAN>
[/TD]
[TD]END OF SERVICE</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]AS1234</SPAN>
[/TD]
[TD]CAT</SPAN>
[/TD]
[TD]0444</SPAN>
[/TD]
[TD]MANJIK</SPAN>
[/TD]
[TD]18.91</SPAN>
[/TD]
[TD]12.11</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD]OPO</SPAN>
[/TD]
[TD]LOPOL</SPAN>
[/TD]
[TD]Y</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]AS1234</SPAN>
[/TD]
[TD]CAT</SPAN>
[/TD]
[TD]0555</SPAN>
[/TD]
[TD]LOPING</SPAN>
[/TD]
[TD]40.11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]O</SPAN>
[/TD]
[TD]WPO</SPAN>
[/TD]
[TD]MOPOL</SPAN>
[/TD]
[TD]N</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]567654</SPAN>
[/TD]
[TD]QAT</SPAN>
[/TD]
[TD]0666</SPAN>
[/TD]
[TD]QWERTY</SPAN>
[/TD]
[TD]40.11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]P</SPAN>
[/TD]
[TD]OBB</SPAN>
[/TD]
[TD]LUUOL</SPAN>
[/TD]
[TD]N</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]567654</SPAN>
[/TD]
[TD]QAT</SPAN>
[/TD]
[TD]0888</SPAN>
[/TD]
[TD]ZXCVBN</SPAN>
[/TD]
[TD]40.11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]R</SPAN>
[/TD]
[TD]OPO</SPAN>
[/TD]
[TD]LOUOL</SPAN>
[/TD]
[TD]N</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

this data should move a sheet called to DAY_WISE_REPORT

i Dont want to import the text and work on excel.
I want to read from the excel sheet.

I would be vary happy if some one can help.
Thanks,
Harish
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Guyz, If any one need any more information plz tell... Im very much delayed on the project and will be very happy if some one can assist

Thanks,
Harish
 
Upvote 0
In the text file, you have "REPORT : MONTH_WISE_REPORT USER : ARJUN" followed a few lines later by "END OF SERVICE = DATA".

However, in the MONTH_WISE_REPORT sheet, column "END OF SERVICE" is "GYANN", not "DATA". Which is correct?

The same apparent anomaly also applies to the "REPORT : MONTH_WISE_REPORT USER : MANOJ" section.
 
Upvote 0
Hi John,

Really sorry
MONTH_WISE_REPORT Sheet should contain the below data

DATE_OF_ACTIVITY</SPAN>CLASS</SPAN>SEC</SPAN>USER</SPAN>JOUID</SPAN>SERVICE</SPAN>S.NO</SPAN>NAME</SPAN>MATHS_MARK</SPAN>SCIENCE_MARK</SPAN>TOTAL</SPAN>AVERAGE</SPAN>END OF SERVICE</SPAN>PARAMETER-1</SPAN>
09182015_1107_WED</SPAN>4D</SPAN>C</SPAN>ARJUN</SPAN>443345</SPAN>NAT</SPAN>1</SPAN>HARISH</SPAN>785.26</SPAN>856.87</SPAN>1642.13</SPAN>M</SPAN>DATA</SPAN>HARISH0001</SPAN>
09182015_1107_WED</SPAN>4D</SPAN>C</SPAN>ARJUN</SPAN>443345</SPAN>NAT</SPAN>2</SPAN>GANSDD</SPAN>45.65</SPAN>10</SPAN>55.65</SPAN>T</SPAN>DATA</SPAN>GANSDD0002</SPAN>
09182015_1107_WED</SPAN>4D</SPAN>C</SPAN>ARJUN</SPAN>441122</SPAN>VAT</SPAN>3</SPAN>HASASA</SPAN>12</SPAN>10</SPAN>22</SPAN>M</SPAN>DATA</SPAN>HASASA0003</SPAN>
09182015_1107_WED</SPAN>4D</SPAN>C</SPAN>ARJUN</SPAN>441122</SPAN>VAT</SPAN>4</SPAN>HAAFFG</SPAN>11</SPAN>10</SPAN>21</SPAN>Y</SPAN>DATA</SPAN>HAAFFG0004</SPAN>
09182015_1107_WED</SPAN>4E</SPAN>F</SPAN>MANOJ</SPAN>44AA11</SPAN>LAT</SPAN>45</SPAN>gurtyy</SPAN>785.26</SPAN>856.87</SPAN>1642.13</SPAN>U</SPAN>DATA</SPAN>gurtyy0045</SPAN>
09182015_1107_WED</SPAN>4E</SPAN>F</SPAN>MANOJ</SPAN>44AA11</SPAN>LAT</SPAN>56</SPAN>MAJOIU</SPAN>88.65</SPAN>10</SPAN>98.65</SPAN>I</SPAN>DATA</SPAN>MAJOIU0056</SPAN>
09182015_1107_WED</SPAN>4E</SPAN>F</SPAN>MANOJ</SPAN>44AA11</SPAN>LAT</SPAN>72</SPAN>LKPOIU</SPAN>90</SPAN>10</SPAN>100</SPAN>O</SPAN>DATA</SPAN>LKPOIU0072</SPAN>
09182015_1107_WED</SPAN>4E</SPAN>F</SPAN>MANOJ</SPAN>44AA11</SPAN>LAT</SPAN>91</SPAN>ASDEWQ</SPAN>45</SPAN>10</SPAN>55</SPAN>P</SPAN>DATA</SPAN>ASDEWQ0091</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>
Click to expand...
 
Upvote 0
This is quite a tricky format, so it helps if you are accurate in your requirements.

Try this. You must edit the folder path and file name of the text file.

Code: 
Public Sub Import_Text_File()

    Dim dataFile As String
    Dim fileLine As String, item As String, parts As Variant
    Dim i As Long, n As Long
    Dim monthData() As Variant, dayData() As Variant
    Dim dateActivity As String, class As String, sec As String, report As String, user As String, _
        jouid As String, service As String
    Dim monthReportDest As Range, mRow As Long, dayReportDest As Range, dRow As Long
    
    dataFile = "C:\Folder\Path\To\data file.txt"            'CHANGE THIS FOLDER PATH AND FILE NAME
    
    With Worksheets("MONTH_WISE_REPORT")
        .Cells.Clear
        .Range("A1:N1").Value = Array("DATE_OF_ACTIVITY", "CLASS", "SEC", "USER", "JOUID", "SERVICE", "S.NO", _
            "NAME", "MATHS_MARK", "SCIENCE_MARK", "TOTAL", "AVERAGE", "END OF SERVICE", "PARAMETER-1")
        Set monthReportDest = .Range("A2")
        mRow = 0
    End With
    
    With Worksheets("DAY_WISE_REPORT")
        .Cells.Clear
        .Range("A1:P1").Value = Array("DATE_OF_ACTIVITY", "CLASS", "SEC", "USER", "JOUID", "SERVICE", "S.NO", _
            "NAME", "MATHS_MARK", "SCIENCE_MARK", "TOTAL", "AVERAGE", "SIGN", "LOGINID", "PUBLIC", "END OF SERVICE")
        Set dayReportDest = .Range("A2")
        dRow = 0
    End With

    Open dataFile For Input As #1
    
    n = 0
    
    While Not EOF(1)
    
        Line Input #1, fileLine
        Debug.Print fileLine
        
        item = GetItem(fileLine, "DATE_OF_ACTIVITY : ")
        If item <> "" Then dateActivity = item
        
        item = GetItem(fileLine, "CLASS : ")
        If item <> "" Then class = item
        
        item = GetItem(fileLine, "SEC : ")
        If item <> "" Then sec = item
        
        item = GetItem(fileLine, "REPORT : ")
        If item <> "" Then report = item
        
        item = GetItem(fileLine, "USER : ")
        If item <> "" Then user = item
        
        item = GetItem(fileLine, "JOUID:")
        If item <> "" Then jouid = item
        
        item = GetItem(fileLine, "SERVICE : ")
        If item <> "" Then service = item
        
        parts = Split(Application.WorksheetFunction.Trim(fileLine), " ")
        
        If UBound(parts) = 5 Then
            If IsNumeric(parts(0)) Then
                n = n + 1
                ReDim Preserve monthData(1 To 14, 1 To n)
                monthData(1, n) = dateActivity
                monthData(2, n) = class
                monthData(3, n) = sec
                monthData(4, n) = user
                monthData(5, n) = jouid
                monthData(6, n) = service
                monthData(7, n) = parts(0)
                monthData(8, n) = parts(1)
                monthData(9, n) = parts(2)
                monthData(10, n) = parts(3)
                monthData(11, n) = parts(4)
                monthData(12, n) = parts(5)
                'Note: monthData(13, 1 to n) is filled by the loop below
                monthData(14, n) = parts(1) & parts(0)
            End If
        End If
        
        If UBound(parts) = 8 Then
            If IsNumeric(parts(0)) Then
                n = n + 1
                ReDim Preserve dayData(1 To 16, 1 To n)
                dayData(1, n) = dateActivity
                dayData(2, n) = class
                dayData(3, n) = sec
                dayData(4, n) = user
                dayData(5, n) = jouid
                dayData(6, n) = service
                dayData(7, n) = parts(0)
                dayData(8, n) = parts(1)
                dayData(9, n) = parts(2)
                dayData(10, n) = parts(3)
                dayData(11, n) = parts(4)
                dayData(12, n) = parts(5)
                dayData(13, n) = parts(6)
                dayData(14, n) = parts(7)
                dayData(15, n) = parts(8)
            End If
        End If
        
        item = GetItem(fileLine, "END OF SERVICE = ")
        
        If item <> "" Then
        
            If report = "MONTH_WISE_REPORT" Then
            
                For i = 1 To n
                    monthData(13, i) = item
                Next
                monthReportDest.Offset(mRow, 0).Resize(n, UBound(monthData)).Value = Application.Transpose(monthData)
                mRow = mRow + n
                n = 0
            
            ElseIf report = "DAY_WISE_REPORT" Then
            
                For i = 1 To n
                    dayData(16, i) = item
                Next
                dayReportDest.Offset(dRow, 0).Resize(n, UBound(dayData)).Value = Application.Transpose(dayData)
                dRow = dRow + n
                n = 0
           
            End If
        End If
                
    Wend
    
    Close #1
    
    MsgBox "Finished"
    
End Sub
 
Upvote 0
Sorry, add this code at the bottom of the module:

Code: 
Private Function GetItem(text As String, item As String) As String

    Dim p1 As Long, p2 As Long
    
    GetItem = ""
    p1 = InStr(text, item)
    If p1 > 0 Then
        p1 = p1 + Len(item)
        p2 = InStr(p1, text, " ")
        If p2 = 0 Then p2 = Len(text) + 1
        GetItem = Mid(text, p1, p2 - p1)
        'Debug.Print GetItem
    End If

End Function
 
Upvote 0
Hey , I seriously dont know how can i thank you....
You have helped so much in this case.
Thanks a lot.
Working beautifully.
Let me see how it runs with the big data
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,351
Messages
6,124,445
Members
449,160
Latest member
nikijon

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