Forum Discussion

learnerjane's avatar
learnerjane
Copper Contributor
Jul 15, 2022
Solved

Excel VBA code to break and read text file line by line

  Hello,   I have a requirement. The below is a text file data.   In this, I have to ignore the lines starting with HEAD and count the number of lines starting with DET for each data group. Eg....
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 15, 2022

    learnerjane 

    Here you go.

    Sub ProcessTextFile()
    Dim sFile As String
    Dim f As Integer
    Dim sLines As String
    Dim aLines() As String
    Dim aLine() As String
    Dim i As Long
    Dim n As Long
    Dim sTitle As String
    ' *** Change sFile to the correct path and filename ***
    sFile = "Test.txt"
    f = FreeFile
    Open sFile For Input As #f
    sLines = Input(LOF(f), #f)
    Close #f
    aLines = Split(sLines, vbCrLf)
    For i = 0 To UBound(aLines)
        If aLines(i) <> "" Then
            aLine = Split(aLines(i), "|")
            Select Case aLine(0)
                Case "HEAD"
                    If n > 0 Then
                        Debug.Print "Group " & sTitle & " has " & n & " items"
                    End If
                    sTitle = aLine(1)
                    n = 0
                Case "DET"
                    n = n + 1
            End Select
        End If
    Next i
    Debug.Print "Group " & sTitle & " has " & n & " items"
End Sub
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 15, 2022

learnerjane 

I realise this is not an answer to the OP but, since HansVogelaar has written a significant block of VBA, I set out to see whether a similar result could be obtained using worksheet formulae.  The worst limitation is that most Lambda functions do not work correctly with 'nested arrays' which is something of a pain when the whole purpose of the calculation is to create an array of arrays.  Nevertheless, in this case

 

CountByTypeλ = LAMBDA(aLines,
    LET(
        type,     MAP(aLines,IdentifyTypeλ),
        filtered, FILTER(type, ISTEXT(type)),
        distinct, UNIQUE(filtered),
        count,    MAP(distinct, LAMBDA(type, COUNT(IF(filtered = type, 1)))),
        HSTACK(distinct, count)
    )
);

IdentifyTypeλ = LAMBDA(aLine,
    LET(
        split, IFERROR(TEXTSPLIT(aLine, "|"), "HEAD"),
        IF(INDEX(split, 1, 1) <> "HEAD", INDEX(split, 1, 2))
    )
);

 

My thought on this are, whilst Lambda functions to not replace either VBA or DAX, they are capable of making significant inroads for problems of modest dimension.

Note: the formula requires the text file to be imported to Excel which is not what was requested.  Power query would probably be my tool of choice for the data import.

Resources