Count the number of fields in a delimited text file

[LaBam]

Please help me. I hope there is way this could be done in VBA. I have a text file that has delimiters "|" separating the various fields. I want to write a code that will count the number of delimiters in each line in the text file.

The idea is for my code to report whenever it encounters a line with less delimiters.

Any suggestion will be most welcome.

 

[ByteMyzer]

The following expression will tell you how many instances of the delimiter "|" appear in a line (make the appropriate substitution for the highlighted text):

UBound(Split([b][i]strLine[/i][/b], "|", , vbTextCompare))

 

[LaBam]

ByteMyzer, Thank you very much for the response.

I've tried including the function you suggested but it's not giving me what I want. I'm getting zero anytime I run it. I believe I'm not using it correctly. Could you please give me a sample code on how it could be used correctly.

I thank you very much in advance.

 

[ByteMyzer]

(siiggghhhhh) The following Sub will check a delimited file and report lines which do not contain the correct number of delimiters:

Public Sub fileCheckDelimiters( _
    ByVal sFileName As String, _
    ByVal Delimiter As String, _
    ByVal lCount As Long)

    Dim b() As Byte
    Dim ff As Integer
    Dim lOccurs As Long
    Dim sBuffer As String
    Dim sErrLog As String
    Dim sLine() As String
    Dim Y As Long

    ff = FreeFile
    Open sFileName For Binary Access Read Shared As ff
        ReDim b(LOF(ff) - 1)
        Get #ff, , b
    Close #ff

    sBuffer = StrConv(b, vbUnicode)
    sLine = Split(sBuffer, vbNewLine, , vbTextCompare)

    For Y = 0 To UBound(sLine)
        lOccurs = UBound(Split(sLine(Y), Delimiter, , vbTextCompare))
        If lOccurs <> lCount Then
            sErrLog = sErrLog & "Line " & Y + 1 _
                & " has " & lOccurs _
                & " delimiters." & vbNewLine
        End If
    Next Y

    Debug.Print sErrLog

End Sub

For example, to check the file C:\MyDelimFile.txt for lines which do not contain exactly 23 delimiters ("|"), call the sub thus:

fileCheckDelimiters "C:\MyDelimFile.txt", "|", 23

 

[LaBam]

Thanks very much for the code sample. But I cannot get it to run. Whenever I run it, I get the error "Subscript out of range". And when I looked at the file, I fould out that the program has deleted all its contents. There was nothing in the file.

Each time the error comes up, it highlights the portion:
------ ReDim b(LOF(ff) - 1)


I've tried the code five time with a new file every time and I'm getting the same outcome as described above.

Any suggestions?

 

[ByteMyzer]

(double-siiggghhhhh) If you copied the code EXACTLY as I posted it, and if the file you check it against is a legitimate delimited file, you will not get this problem. The only reason for the error you mentioned would occur is if the file opened by the code were completely empty. I tested the code myself and encountered no such error.