Excel VBA: # Columns in .csv

What VBA do I need to find the number of columns in a .csv file?
ouestqueAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Here's an FSO version of the code I posted.
Function ColumnsInCSV(strFilePath As String) As Long
Dim FSO As Object
Dim FL As Object
Dim strHeaderRow As String

    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set FL = FSO.OpenTextFile(strFilePath, 1)
    
    strHeaderRow = FL.ReadLine

    FL.Close

    ColumnsInCSV = UBound(Split(strHeaderRow, ",")) + 1
    
End Function

Open in new window

PS I also fixed a couple of typos.
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...
Sub NumberOfColumnsInCSVFile()
Dim wb As Workbook
Dim csvFilePath As String
Dim col As Long

Application.ScreenUpdating = False

'Change the csv filepath with filename in the below line
csvFilePath = "C:\Users\ouestque\Desktop\YourCSVFile.csv"

Set wb = Workbooks.Open(csvFilePath)
col = wb.Sheets(1).Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
wb.Close False
MsgBox "Total no. of columns : " & col
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Or if you need it to call in another procedure, you may convert it to a Function like below...


Function ColumnsInCSVFile(ByVal FilePath As String) As Long
Dim wb As Workbook
Application.ScreenUpdating = False
Set wb = Workbooks.Open(FilePath)
ColumnsInCSVFile = wb.Sheets(1).Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
wb.Close False
Application.ScreenUpdating = True
End Function

Open in new window

And then you may call it in another procedure like below...

Calling procedure:
Sub Test()
Dim col As Long
col = ColumnsInCSVFile("C:\Users\ouestque\Desktop\YourCSVFile.csv")
MsgBox col
End Sub

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
NorieVBA ExpertCommented:
Try this.
Function ColumnsInCSV(strFilePath As String) As Long
Dim HeaderRow As String
Dim FF As Integer

    FF = FreeFile()

    Open strFilePath For Input As #FF

    Line Input #FF, strHeaderRow

    Close #FF

    ColumnsInCSV= UBound(Split(strHeaderRow, ","))+1

End Function

Open in new window

2
 
Roy CoxGroup Finance ManagerCommented:
Try this

Option Explicit

Sub GetColumnCount()
    Dim wb As Workbook
    Dim Cnt As Long
    Dim sFileName As String

    sFileName = x    ''/// change X to file path of csv

    Set wb = Workbooks.Open(sFileName)
    With wb.Worksheets(1)

        Cnt = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With

    MsgBox "The file contains " & Cnt & "columns"
    wb.Close False
End Sub

Open in new window

0
 
aikimarkCommented:
If your CSV files might originate from a non-Windows system, you should probably use FileSystemObject in place of the intrinsic file open/line input in Norie's code.  FSO tolerates EOL sequences other than CrLf
0
 
ouestqueAuthor Commented:
aikimark question. Will you submit a code sample that does what you mentioned in your most recent comment?

Also, awesome answers everyone!!
0
 
aikimarkCommented:
This is Norie's answer.  I'm merely a fly on the wall.
0
 
ouestqueAuthor Commented:
Thanks Everyone!!! Great Feedback!!!
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.