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

Question

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?

Are you talking about a CSV? Like how many commas in a line? — Toddleson, Aug 11 '21 at 13:24
Its not comma separated but rather tab separated, and it has about 930 columns and 12,000 rows, which could be both subject to change in the future. — kyle, Aug 11 '21 at 13:33
Please figure out which part *"takes forever"*. Therfore run this code step by step using F8. See which code line takes so long. Probably opening the file takes long and then it is nothing wrong with that code, then it just takes that time to open the file. Please check if opening the file `Set wb = Workbooks.Open(FName)` takes so long or `lastColumn = rng.Find` takes so long or `wb.Close False`. — Pᴇʜ, Aug 11 '21 at 13:46
You might find the ImportCSV function found at the following link easy to adapt to get your column number: https://stackoverflow.com/questions/68545448/is-there-a-way-to-open-csv-with-vba-and-choose-the-delimiter/68547154#68547154 — Tragamor, Aug 11 '21 at 14:54

Toddleson · Accepted Answer · 2021-08-11T13:56:18.080

4

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.

edited Aug 11 '21 at 13:56

answered Aug 11 '21 at 13:48

Toddleson

4,321
1
6
26

2

in `CountOccur` instead of a time expensive loop just replace the seperator with `vbnullstring` (nothing) and compare lengths: `CountOccur = (Len(within_text) - Len(Replace$(within_text, look_for, vbNullString))) / Len(look_for)` would be a lot faster (and the only line of code you need in this function) and for most separators `CompareMethod` is irrelevant anyway. – Pᴇʜ Aug 11 '21 at 13:53
3

Or use split `CountOccur = Ubound(Split(within_text,look_for))+1`? – Scott Craner Aug 11 '21 at 13:55
2

Do you need to close the file afterwards ? – Gary's Student Aug 11 '21 at 13:56
2

Depending on how the text file data is (eg, if it has all the separators in each line no matter if there is data in the column or not), it might be enough to count the separators in the first line (instead of all lines). Would make it even faster. – Pᴇʜ Aug 11 '21 at 13:56
Those are all great suggestions and you guys should submit them as answers! @Pᴇʜ Yeah I realised that most "csv" type files have a standard width and I only need to check the first line. – Toddleson Aug 11 '21 at 13:59
Thank you all for responding! The way the file is structured is a text file separated by tabs and no commas. One problem is the way the text file is structure, when pasted into an excel spreadsheet, in the first column there are 208 rows of data only for column A not the rest. In row 209, then the rest of the data is populated to column 930. Also, i dont know if this will be a problem but there are two columns with no "title" but with data under them. Thanks! – kyle Aug 11 '21 at 14:00
@kyle The function has an option `CheckAll` which by default makes the function check the length of each row of the file and return the largest number of columns found. So it should not be a problem if the lines are different lengths! – Toddleson Aug 11 '21 at 14:02
if your seperator is tab then just use `Const SEPARATOR As String = vbTab` instead. – Pᴇʜ Aug 11 '21 at 14:03
1

Thank you guys! I wish i could up vote yall but it wont let me as I'm still new here. But thank you again, hopefully one day ill be half as good! – kyle Aug 11 '21 at 14:22
1

@kyle returning 1 means that the Separator was not found within any of the text lines. Try manually copying and pasting the separator from your text file into the code like `Const SEPARATOR As String = " "` – Toddleson Aug 11 '21 at 14:24
1

You got it sir, works like a charm. Cant begin to say how thankful i am! – kyle Aug 11 '21 at 14:27
3

Worth noting that at least for CSV, it's required for fields where the value contains a comma to be quoted eg `"hello, Jim"`, so you'd need to account for that too... – Tim Williams Aug 11 '21 at 15:57
@Nathan_Sav Yes, Scott Craner also suggested that, and it could be. – Toddleson Aug 20 '21 at 13:28
@Toddleson Apologies, I missed that one. – Nathan_Sav Aug 20 '21 at 13:30

score 4 · Answer 2 · answered Aug 11 '21 at 15:46

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.

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

2 Answers2