VBA : To place the selected data from text file to excel sheet

[aishwarya gowda]

Hi,
I have a large text file, where i executed a single command multiple times .
1st line is the command executed
2nd and 3rd line - output of the command
and it repeats , as i executed the command multiple times.

sample text file is attached : test.txt
excel sheet when the required values are filled is attached: excel-sheet2

Requirement:
1.To fetch different data from all the three lines and need to place it in the respective column of the excel sheet, where template is already ready.
a. To fetch time value from line (1,4,7,10,13,......), where i run the command
b. memory value from line (2,5,8,11.......)
c. usr and sys value from line (3,6,9,12,.......)

I tried to fetch by considering the line numbers but facing errors.
Any kind of suggestion is helpful.
Thank you.

 

[Marc L]

Hi,​

​

attach the original text file without cutting / modifying it and the 'before' original workbook …​

​

I tried to fetch by considering the line numbers but facing errors.

Post at least your code …​

​

 

[aishwarya gowda]

Sub DataFromTextFile1()

    ' Text File Address
    Const strFile = "C:\Users\test5.txt"
    Const cCriteria As Long = 27  ' Criteria Position       
    Const cPosition As Long = 1  ' Target Position
    Const cChars As Long = 25      ' Target Chars

    ' Criteria Array
    Dim vntC As Variant: vntC = Array("root")
    ' Row Array
    Dim vntFR As Variant: vntFR = Array(3)
    ' Column Array
    Dim vntCC As Variant: vntCC = Array(2)

    Dim vntL As Variant           ' Criteria Length Array
    Dim vntR As Variant           ' Target Row Counter Array
    Dim LB As Long                ' Array Lower Bound
    Dim UB As Long                ' Array Upper Bound
    Dim i As Long                 ' Array Element Counter
    Dim t As Long                 ' Total Records Counter
    Dim lngFile As Long           ' Text File Number
    Dim strLine As String         ' Current Line (in Text File)

    ' Calculate Lower and Upper Bounds.
    LB = LBound(vntC): UB = UBound(vntC)

    ' Resize Criteria Length Array to the size of Criteria Array.
    ReDim vntL(UB) As Long
    ' Calulate the length of each element in Criteria Array and write
    ' the calculated values to Criteria Length Array.
    For i = LB To UB: vntL(i) = Len(vntC(i)): Next i
    ' Resize Target Row Counter Array to the size of Criteria Array.
    ReDim vntR(UB) As Long

    ' Aquire Text File Number.
    lngFile = FreeFile()          '

    ' Write Text File to memory.
    Open strFile For Input As #lngFile
        ' Loop through lines of Text File.
        Do While Not EOF(lngFile)
            ' Write current line of Text File (from memory) to Current Line.
            Line Input #lngFile, strLine
            ' Loop through elements of Arrays.
            For i = LB To UB
                ' Check if Current Criteria is found at current Target Position.
                If Mid(strLine, cCriteria, vntL(i)) = vntC(i) Then
                    ' Count Target Row by increasing the current value
                    ' of the current element in Target Row Counter Array.
                    vntR(i) = vntR(i) + 1
                    ' Write Current Target Value to ActiveSheet.
                    Cells(vntFR(i) + vntR(i) - 1, vntCC(i)) _
                      = Trim(Mid(strLine, cPosition, cChars))
                    ' Count Total (All) Records so far.
                    t = t + 1
                    Exit For
                End If
            Next i
        Loop
    Close #1

    MsgBox "Total Records Found: " & t, vbInformation

End Sub

Hi,
the above code works fine to fetch time and place it in excel

and to fetch the memory value , i need to make following changes in the above code:
for memory:
Const cCriteria As Long = 27 ' Criteria Position
Const cPosition As Long = 33 ' Target Position
Const cChars As Long = 6 ' Target Chars

Dim vntC As Variant: vntC = Array("mem:")
Dim vntFR As Variant: vntFR = Array(3)
Dim vntCC As Variant: vntCC = Array(5)

But, how can i together use the code, so that i can get result in one instant.
text file and excel sheet attached

 

[Marc L]

According to your last attachment a VBA beginner level starter demonstration just with Excel basics :​

Sub Demo1()
    Dim V, R&, W(), L&, N&
        V = ThisWorkbook.Path & "\test5.txt":  If Dir(V) = "" Then Beep: Exit Sub
        R = FreeFile
        Open V For Input As #R
        V = Application.Trim(Split(Input(LOF(R), #R), vbCrLf))
        Close #R
        ReDim W(1 To UBound(V) \ 3, 3)
    For R = 1 To UBound(V) Step 3
        L = InStr(V(R), "]"):  If L = 0 Then Exit For
        N = N + 1
        W(N, 0) = Left(V(R), L)
        W(N, 1) = Val(Split(V(R + 2))(3))
        W(N, 2) = Val(Split(V(R + 2))(5))
        W(N, 3) = Val(Split(V(R + 1))(3))
    Next
        If N Then [B3:E3].Resize(N) = W
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[aishwarya gowda]

Thank you for the response, it worked well on my requirement.