Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2009
    Posts
    7

    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
    Posts
    43
    Provided Answers: 1
    MAP,


    Code:
    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
         Wend
       ' File Read ... Close it and log entry.
       Close #1
       CurrentDb.Execute "Insert Into tblCSVs (FileName, Columns, Rows) " & _
                         "Values('" & FileName & "', '" & lngHowManyColumns & ", " & lngHowManyRows & ")"
       FileName = Dir()
       Wend
    hth,
    Wayne

  3. #3
    Join Date
    Oct 2009
    Posts
    7
    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!!!

  4. #4
    Join Date
    Dec 2016
    Posts
    3

    Question

    I'm trying to get this to work but it gives me the message: "File not found." What could be the problem?


    Quote Originally Posted by WayCal View Post
    MAP,


    Code:
    Dim varArray As Variant
    Dim strFileName As String
    Dim lngHowManyColumns As Long
    Dim lngHowManyRows As Long
    Dim strBuffer As String
    ....
    hth,
    Wayne

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    the Error telling me base on this line

    FileName = Dir("C:\Temp\*.csv")

    there are no .csv files in the C:\temp folder
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Dec 2016
    Posts
    3

    Question

    Quote Originally Posted by myle View Post
    the Error telling me base on this line

    FileName = Dir("C:\Temp\*.csv")

    there are no .csv files in the C:\temp folder
    I do have CSVs is the temp directory. However, it still produces an error and the debug shows the error to be with this line:

    Code:
    Open FileName For Input As #1

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    ok

    did you Debug.print the FileName

    and what is the Error number


    if there a file there then it could the file is already open by something
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Dec 2016
    Posts
    3

    Question

    Quote Originally Posted by myle View Post
    ok

    did you Debug.print the FileName

    and what is the Error number


    if there a file there then it could the file is already open by something
    Debug.print shows the appropriate file & no other program is accessing it. It says "Run-time Error 53: File not found."

Posting Permissions

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