CODEWITHSUNDEEP
vbaexcel
D. Sean
D. Sean

Reputation: 145

How to get the number of lines of data in CSV file in VBA

I tried to get the number of lines of data in several CSV files in VBA. Here is the code.

Sub Woo_Products()

Dim fso As New FileSystemObject
Dim flds As Folders
Dim fls As Files
Dim strText As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim extfind As String
Dim FilePath As String
Dim sLineOfText As String

On Error Resume Next

Workbooks.Open Filename:="F:\Work\scrape\" & "woocommerce-products.csv", UpdateLinks:=3

Set fls = fso.getfolder("C:\Users\star\Downloads").Files
k = 2
For Each f In fls
    strText = f.Name
    extfind = Right$(strText, Len(strText) - InStrRev(strText, "."))
    If extfind = "csv" Then
        FilePath = "C:\Users\star\Downloads\" & strText

        Open FilePath For Input As #1
        i = 0
        Do Until EOF(1)
            Line Input #1, sLineOfText
            If sLineOfText <> "" Then i = i + 1
        Loop
        Close #1

    End If
Next

Windows("woocommerce-products.csv").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

But I am getting the same count for each file. Of course, each file has different lines of data. Hope to help me for this.

Upvotes: 2

Views: 8155

Answers (4)

D. Sean
D. Sean

Reputation: 145

i = ActiveWorkbook.ActiveSheet.Cells(ActiveWorkbook.ActiveSheet.Rows.Count, 1).End(xlUp).Row

It's working so well.

Upvotes: -2

JosephC
JosephC

Reputation: 929

You'll need to add the references (Tools --> References)

Microsoft Scripting Runtime
Microsoft VBScript Regular Expressions 5.5

This will count "Return & NewLine" characters in the file. 

Private Function LineCount(ByVal PathFile As String) As Long
    Dim sData As String
    Dim oFile As New FileSystemObject
    sData = oFile.OpenTextFile(PathFile, ForReading).ReadAll

    Dim oRegX As New RegExp
    oRegX.Pattern = "\r\n"
    oRegX.Global = True
    LineCount = oRegX.Execute(sData).Count + 1

    Set oRegX = Nothing
    Set oFile = Nothing
End Function

Upvotes: 2

Kostas K.
Kostas K.

Reputation: 8518

Another approach using FileSystemObject:

Public Function GetLineCount(ByVal Path As String) As Long
    With CreateObject("Scripting.FileSystemObject")
        GetLineCount = UBound(Split(.OpenTextFile(Path, 1).ReadAll, vbNewLine)) + 1
    End With
End Function

Upvotes: 3

TinMan
TinMan

Reputation: 7759

If all you need is a line count, I would write a function to return the count.

Function getFileLineCount(FullFileName As String, Optional LineDelimiter As String = vbNewLine) As Long
    Dim text As String
    Dim fileNo As Integer, n As Long
    fileNo = FreeFile
    Open FullFileName For Input As #fileNo
    Do Until EOF(1)
        Line Input #1, text
        n = n + 1
    Loop
    Close #fileNo
    getFileLineCount = n
End Function

Upvotes: 4

Related Questions