Solved

Recursive file and folder audit...

Posted on 2014-11-28
10
169 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

803 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