Solved

Recursive file and folder audit...

Posted on 2014-11-28
10
166 Views
Last Modified: 2014-12-03
A few years ago, Rey Obrero (Microsoft Access MVP) gave me a solution for this question. But now, I am not able to locate it. Suppose I specify a drive name or a folder name like:

AuditFiles("c:")  or Audit("C:\MyData")

Staying with drive example for now, if we have the following files and folders:

C:
  Folder_1
               FolderA
                      a.xls
                      b.xls
             c.xls  <-- this file is in Folder_1
 Folder_2
            d.xls
            e.xls

There could be many levels of subfolders. I am trying to read file names and folder names to enter in tblFIleAndFolders like:

tblFIleAndFolders
=============================
ID_PK           Name               ID_FK            FileYN
     1                C:                      null
     2          Folder_1                  1    <-- meaning it is in C:
     3          c.xls                         2                    Yes
     4          FolderA                   2
     5          a.xls                        4                    Yes
     6          b.xls                        4                    Yes
     7         Folder_2                  1
     8          d.xls                        7                    Yes
     9          e.xls                        7                    Yes

Question: Could this be done recursively to include all .xls files, folders, and subfolders (regardless it has .xls or not)?

Order of entries is not important. I will, at some point, expand this to include .doc, .png, etc.
0
Comment
Question by:Mike Eghtebas
  • 4
  • 4
  • 2
10 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40471209
I don't know that I'd work that hard.
You could shell out dir /o/b/s > somefile.csv from the starting point.
You then have a textfile.
Load it up into a TextStream object
Walk down it.
Throw each line into a Split(TheLine,"\")
If the last element isn't like *.xls, move on
If it is, walk down the split.
Check each element.
Is it already in the table?  No? Enter it.
The second last element will be the folder the file is in.
Create the entry for the file.

Make sense?
What help would you need to implement it?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40471213
BTW, I will welcome SQL Server solution with Procs etc. I have requested SQL Server TA to be added to this question.

Here is sample code I am trying to revise to do the job:
Option Compare Database
Option Explicit

'list files to tables
'http://allenbrowne.com/ser-59alt.html

Dim gCount As Long ' added by Crystal
Sub audit()
Call ListFilesToTable("C:", "*.zip")
End Sub
Sub runListFiles()
    'Usage example.
    Dim strPath As String _
    , strFileSpec As String _
    , booIncludeSubfolders As Boolean
    
    strPath = "E:\"
    strFileSpec = "*.*"
    booIncludeSubfolders = True
    
    ListFilesToTable strPath, strFileSpec, booIncludeSubfolders
End Sub

'crystal modified parameter specification for strFileSpec by adding default value
Public Function ListFilesToTable(strPath As String _
    , Optional strFileSpec As String = "*.*" _
    , Optional bIncludeSubfolders As Boolean _
    )
On Error GoTo Err_Handler
    'Purpose:   List the files in the path.
    'Arguments: strPath = the path to search.
    '           strFileSpec = "*.*" unless you specify differently.
    '           bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
    'Method:    FilDir() adds items to a collection, calling itself recursively for subfolders.
    
    Dim colDirList As New Collection
    Dim varitem As Variant
    Dim rst As DAO.Recordset
    
   Dim mStartTime As Date _
      , mSeconds As Long _
      , mMin As Long _
      , mMsg As String
      
   mStartTime = Now()
   '--------
    
    Call FillDirToTable(colDirList, strPath, strFileSpec, bIncludeSubfolders)
      
   mSeconds = DateDiff("s", mStartTime, Now())
   
   mMin = mSeconds \ 60
   If mMin > 0 Then
      mMsg = mMin & " min "
      mSeconds = mSeconds - (mMin * 60)
   Else
      mMsg = ""
   End If
   
   mMsg = mMsg & mSeconds & " seconds"
   
   MsgBox "Done adding " & Format(gCount, "#,##0") & " files from " & strPath _
      & IIf(Len(Trim(strFileSpec)) > 0, " for file specification --> " & strFileSpec, "") _
      & vbCrLf & vbCrLf & mMsg, , "Done"
  
Exit_Handler:
   SysCmd acSysCmdClearStatus
   '--------
    
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, , "ERROR"
    
    'remove next line after debugged -- added by Crystal
    Stop: Resume 'added by Crystal
    
    Resume Exit_Handler
End Function

Private Function FillDirToTable(colDirList As Collection _
    , ByVal strFolder As String _
    , strFileSpec As String _
    , bIncludeSubfolders As Boolean)
   
    'Build up a list of files, and then add add to this list, any additional folders
    On Error GoTo Err_Handler
    
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
    Dim strSQL As String

    'Add the files to the folder.
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
         gCount = gCount + 1
         SysCmd acSysCmdSetStatus, gCount
         strSQL = "INSERT INTO Files " _
          & " (FName, FPath) " _
          & " SELECT """ & strTemp & """" _
          & ", """ & strFolder & """;"
         CurrentDb.Execute strSQL
        colDirList.Add strFolder & strTemp
        strTemp = Dir
    Loop

    If bIncludeSubfolders Then
        'Build collection of additional subfolders.
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop
        'Call function recursively for each subfolder.
        For Each vFolderName In colFolders
            Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
        Next vFolderName
    End If

Exit_Handler:
    
    Exit Function

Err_Handler:
    strSQL = "INSERT INTO Files " _
    & " (FName, FPath) " _
    & " SELECT ""  ~~~ ERROR ~~~""" _
    & ", """ & strFolder & """;"
    CurrentDb.Execute strSQL
    
    Resume Exit_Handler
End Function

Public Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0& Then
        If Right(varIn, 1&) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 40471843
What you have posted is not at all an attempt to create what I have suggested, which would work simply and admitably well.  Which parts of that solution  -- piping out cmd /c dir  > /o/b/s ; opening the result as a TextStream object ; reading each line into a Split() array ;  or the loop to process the result -- is giving you grief?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40471933
Nick,

I think I will delete this question from Access and post it in SQL Server. See: http://www.kodyaz.com/articles/file-folder-list-xp_dirtree-sql-server-stored-procedure-recursive-cte.aspx for a very close solution.

Mike
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40472210
Since you seem to be reluctant to build it, I'll start.
Here's code to build and execute a batchfile to create a flat listing of the path selected
Dim db As Database
Dim rs As Recordset
Dim batfile As Object
Dim fs As Object

Set fs = CreateObject("Scripting.FileSystemObject")
Set batfile = fs.CreateTextFile("c:\temp\dir.bat", True)
'ok lets build the batchfile to do the dir
'go to the correct drive
batfile.WriteLine (Left(Me.txtPath, 2) & "\") 'probably c:\
batfile.WriteLine ("cd\") 'back to the root
batfile.WriteLine ("cd " & Me.txtPath) ' to the path
'Ok let's pipe it out
batfile.WriteLine ("dir *.* /o/b/s > c:\temp\dir.txt")
batfile.WriteLine ("pause")
batfile.Close

'ok, execute it
Shell "cmd /c c:\temp\dir.bat"
MsgBox "it begins"

Open in new window


Next up, opening the newly created listing and beginning the parsing
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 100 total points
ID: 40473119
Ok,
This code will grab a dir and stuff it into a table of paths and filenames.
Dim db As Database
Dim rs As Recordset

Dim batfile As Object
Dim dirfile As Object
Dim fs As Object
Dim textline As String
Dim myDictionary As Object
Dim SlashSplit() As String
Dim ThePath As String
Dim TheFilename As String

Set fs = CreateObject("Scripting.FileSystemObject")
Set batfile = fs.CreateTextFile("c:\temp\dir.bat", True)
'ok lets build the batchfile to do the dir
'go to the correct drive
batfile.WriteLine (Left(Me.txtPath, 2)) 'probably c:\
batfile.WriteLine ("cd\") 'back to the root
batfile.WriteLine ("cd " & Me.txtPath) ' to the path
'Ok let's pipe it out
batfile.WriteLine ("dir *." & IIf(Len(Nz(Me.txtExtension.Value, "*")) <> 3, "*", Me.txtExtension.Value) & " /o/b/s>c:\temp\dir.txt")
batfile.WriteLine ("pause")
batfile.Close

'ok, execute it
Shell "cmd /c c:\temp\dir.bat"
MsgBox "Click OK only after closing the batch file"

'clean up the batch file
fs.DeleteFile "c:\temp\dir.bat", True

'start up the dictionary
'Set myDictionary = CreateObject("Scripting.Dictionary")

'get the recordset ready
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from tblPaths where 1=2;", dbOpenDynaset, dbSeeChanges)
'open the file listing
Set dirfile = fs.OpenTextFile("c:\temp\dir.txt", ForReading)
' Read from the file and display the results.
Do While dirfile.AtEndOfStream = False
    textline = dirfile.ReadLine
    'alright let's split the textline
    SlashSplit = Split(textline, "\")
    'Ok, the last item is the filename
    'everything else is the path
    With rs
        .AddNew
        !FileName = SlashSplit(UBound(SlashSplit))
        ThePath = Left(textline, Len(textline) - Len(SlashSplit(UBound(SlashSplit))))
        !Path = ThePath
        .Update
    End With
Loop
dirfile.Close
MsgBox "Processed to Paths & Filenames"

'Now the fun begins

Open in new window


Now,
How do you want to recurse and store things like
c:\temp
c:\temp\temp
c:\something\temp
c:\something\temp\temp
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40478632
Hi Nick & eghtbas,

I have done such an application (liitle bit more elaborate) already for image-files. If you are interested i will strip it down to just look for Excel files. As it will take me some time to do it, i ask you first, if you are interested.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40478775
bonjour-aut,

I appreciate that. I look forward to it.

Mike
0
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 400 total points
ID: 40479022
Hi Mike,

I attach a test-db which is a stripped version of a little image administration tool.
Please start with the drive, then proceed with the directories and as 3rd step the files.
The application is time consuming, so let it work in the background, if you test it on a machine with a lot of diretories.
It recursively walks trough all directores and files, so it can be a base for various purpose.
I hope it gives you some ideas for your specific rquirements.
The file information is comparing data from previous scans, so it will indicate also missing files versus last scan.
Please be aware, that the CreateDate is the Date when a file was stored at the current location. The last Changed date may be older, if a file was just copied as it is.

Franzexcel-db.accdb
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40479108
Thank you.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now