CODEWITHSUNDEEP
vbafilecsv
codedude
codedude

Reputation: 6519

How to find length of all .csv files in directory?

I have multiple .csv files that I need to find the length of in my directory. (The number of rows that have data in them.) I'm running the following code from a .xlsx file in the same directory. (I intend to copy data from the .csv files to the .xlsx file eventually.) 

i = 1
FilePath = Application.ActiveWorkbook.Path & "\"
file = Dir(FilePath & "*.csv")
Do While Len(file) > 0
    Open FilePath & file For Input As #1
        length(i) = Cells(Rows.Count, 1).End(xlUp).Row 
        i = i + 1
    Close #1
    file = Dir
Loop

All the values of the length array end up being 1, even though the .csv files are probably 15-20 rows long.

Upvotes: 0

Views: 1046

Answers (2)

Jericho Johnson
Jericho Johnson

Reputation: 759

As @SOofWXLS stated, your code is not opening the files in Excel, you are opening them for direct i/o.

Here is a complete code sample that will fill your array with the file lengths as you were trying to do.

Dim fPath As String
Dim fName As String
Dim hFile As Long
Dim i As Long
Dim NumLines As Long
Dim length() As Long
Dim strLine As String

ReDim length(1 To 1)
fPath = Application.ActiveWorkbook.Path & "\"
fName = Dir(fPath & "*.csv")
Do While Len(fName) > 0
    i = i + 1
    NumLines = 0
    ReDim Preserve length(1 To i)
    hFile = FreeFile
    Open fPath & fName For Input As hFile
    Do While Not EOF(hFile)
        Line Input #hFile, strLine
        NumLines = NumLines + 1
    Loop
    Close hFile
    length(i) = NumLines
    fName = Dir
Loop

This will also dynamically expand your array to accommodate as many files as are found.

Upvotes: 0

SierraOscar
SierraOscar

Reputation: 17637

You're not actually opening the file in Excel so you can't count how many cells there are. Try reading how many lines instead:

Open FilePath & file For Input As #1
    While Not EOF(1): Line Input #1, trashLine: Wend
    i = i + 1
Close #1

Alternatively, open the file in Excel - test - then close afterwards:

Set tempWB = Workbooks.Open(FilePath & file)
    i = i + tempWB.Sheets(1).Cells(tempWB.Sheets(1).Rows.Count, 1).End(xlUp).Row
tempWB.Close False

Or an even quicker way is to use Windows Script:

 Dim i As Long

 For Each varFile In _
    Filter(Split(CreateObject("WScript.Shell").Exec("cmd /c find /v /c """" """ _
    & ThisWorkbook.Path & "\*.csv""").StdOut.ReadAll, vbCrLf), ":")

    i = i + CLng(Split(varFile, ":")(2))

 Next

 Debug.Print i

That way, if you've got 10 files the code is only working with 10 strings rather than opening/closing a file or reading thousands of lines...

Upvotes: 1

Related Questions