How can I Count the number of columns in a .txt file in VBA

user_16633052 0
Viewed 348

I am Trying to count the number of columns a .txt file has using a macro. My code how ever takes for ever to run and a culprit is this code

Set wb = Workbooks.Open(FName)
            Dim lastColumn As Integer
            Dim rng As Range
            Set rng = wb.Worksheets(1).Cells
            lastColumn = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
    wb.Close False

Basically im opening the .txt file i think, counting them, storing it in a value, and closing it which takes forever, is there a better way to do this?

import vba excel
2 Answers

I enjoy solving these types of things as a morning exercise, so I made a function that counts the columns of a "csv" without opening the file into the excel application.

VBA has I/O interface that allows it to open text files without spending excess resources loading it into the application as a workbook. Using this method should be much faster than Workbooks.Open.

To change it from "csv" to tab separated, change SEPARATOR to your file's specific character.

Sub test2() 'Example usage of the function
    Const FilePath As String = "C:\Users\Me\Desktop\Book1.csv"
    MsgBox CSVColumnCount(FilePath)
End Sub

Function CSVColumnCount(FileName As String, Optional CheckAll As Boolean = True) As Long
    Const SEPARATOR As String = ","
    
    Dim FileNum As Long
    FileNum = FreeFile
    
    Open FileName For Input As #FileNum
    
    Dim LineText As String, ColCount As Long, LineCol As Long

    Do While Not EOF(FileNum)
        Line Input #FileNum, LineText
        LineCol = CountOccur(SEPARATOR, LineText) + 1
        If LineCol > ColCount Then ColCount = LineCol
        If Not CheckAll Then Exit Do
    Loop
    CSVColumnCount = ColCount
    Close FileNum
End Function
Function CountOccur(look_for As String, within_text As String, Optional CompareMethod As VbCompareMethod = vbBinaryCompare) As Long
    'Count the number of times the given string appears within the larger text body.
    Dim Count As Long, Start As Long
    While InStr(Start + 1, within_text, look_for, CompareMethod) <> 0
        Start = InStr(Start + 1, within_text, look_for, CompareMethod)
        Count = Count + 1
    Wend
    CountOccur = Count
End Function

I made the function check the length of each line and report the maximum because I was thinking that there might be short or long lines but I am realizing that it shouldn't be the case for most "csv" type files. So I'm adding an option to only check the first line.

user_146087500

You could also investigate using ADO.

Function ColumnCount(strPath As String, strFileName As String) As Long

Dim c As New ADODB.Connection
Dim r As New ADODB.Recordset

c.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties='text;HDR=Yes;FMT=Delimited';"
c.Open

r.Open "select * from [" & strFileName & "]", c

ColumnCount = r.Fields.Count

r.Close
c.Close

Set c = Nothing
Set r = Nothing

End Function

Calling like so ColumnCount("c:\dev","test_csv.csv") A bit of work could be done to check file exists, split out path etc. Just an idea.

user_54147980
Related