Excel VBA: # Columns in .csv

What VBA do I need to find the number of columns in a .csv file?
ouestqueAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
NorieAnalyst Assistant Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
NorieAnalyst Assistant Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.