Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009

    Unanswered: VBA to Count Number of Rows and Columns in Each CSV File in Folder

    Using MS Access 2010, I need to be able to produce a list of all .csv files that exist in a subdirectory and show the filename, the number of columns and the number of rows for each file. This subdirectory will contain approximately 500 .csv files every time we need to produce this list. The result list needs to be an Access table so that it can be sorted when reviewed. Is this possible to do using VBA? Any help that can be provided would be greatly appreciated.

  2. #2
    Join Date
    Dec 2013
    Provided Answers: 1

    Dim varArray As Variant
    Dim strFileName As String
    Dim lngHowManyColumns As Long
    Dim lngHowManyRows As Long
    Dim strBuffer As String
    CurrentDb.Execute "Delete From tblCSVs"
    FileName = Dir("C:\Temp\*.csv")
    While FileName <> ""
       Open FileName For Input As #1
       ' The 1st line will show how many columns ...
       Line Input #1, strBuffer
       varArray = Split(strBuffer, ",")
       lngHowManyColumns = UBound(varArray) + 1
       ' Read entire file to get # of rows ...
       lngHowManyRows = 1
       While Not EOF(1)
         lngHowManyRows = lngHowManyRows + 1
         Line Input #1, strBuffer
       ' File Read ... Close it and log entry.
       Close #1
       CurrentDb.Execute "Insert Into tblCSVs (FileName, Columns, Rows) " & _
                         "Values('" & FileName & "', '" & lngHowManyColumns & ", " & lngHowManyRows & ")"
       FileName = Dir()

  3. #3
    Join Date
    Oct 2009
    Thanks Wayne,
    After a couple of minor modifications, this works exactly as needed. Thanks so much for sharing your expertise!!! This will save us a tremendous amount of time and effort in analyzing our data files!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts