• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Recursive directory/file listing to populate database with parent / child

Hi experts,

I have a dillema on my hands. I need to read all the folders/subfolders/files of a particular root directory (eg c:\documents) and then populate a SQL database in the following database table format...

ID | PARENT ID | FILE/FOLDER NAME | FOLDER or FILE YES/NO

So for example....

1 | NULL | First node under root | FOLDER
2 | NULL | Second node under root | FOLDER
3 | 1 | First file under first node | FILE
4 | 1 | Second file under first node | FILE
5 | 2 | First file under second node | FILE
6 | 2 | First folder under second node | FOLDER
7 | 6 | First file under node 6 | FILE

etc...

So far I have written this code... (it may help to answer the question)

Substitute sFilePath with the path to a folder

<%

Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(sFilePath)

nFileID = 1
nParentID = 1
nSubID = 1

Response.Write "ID: " & nFileID & ", Parent: N/A, Name: " & objFolder.Name & "<br><br>"
IterateThroughDirectory objFolder

Set objFSO = Nothing


' Sub to iterate through directory
'========================
     
Sub IterateThroughDirectory(objFolder)

     nFileID = nFileID + 1 ' Increase file ID

     nSubID = nFileID

     For Each objFile in objFolder.Files
          Response.Write "ID:" & nFileID & ", Parent: " & nSubID & ", Name:" & objFile.Name & "<br>"
          nFileID = nFileID + 1 ' Increase file ID
     Next

     For Each objSubFolder in objFolder.SubFolders
         
          Response.Write "<br><br>ID: " & nFileID & ", Parent: " & nParentID & ", Name: " & objSubFolder.Name & "<br><br>"
         
          IterateThroughDirectory objSubFolder
         
          nParentID = nSubID
          nFileID = nFileID + 1 ' Increase file ID
     Next

     
End Sub

%>

Many thanks!!

Cheers

Anthony
0
anthony6482
Asked:
anthony6482
  • 4
  • 2
1 Solution
 
hujiCommented:
What about this:

<%
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(sFilePath)

nID = 1

Response.Write "ID: " & nFileID & ", Parent: N/A, Name: " & objFolder.Name & "<br><br>"
IterateThroughDirectory objFolder, 0

Set objFSO = Nothing


' Sub to iterate through directory
'========================
     
Sub IterateThroughDirectory(objFolder,nParentID)

     For Each objFile in objFolder.Files
          Response.Write "ID:" & nID & ", Parent: " & nParentID & ", Name:" & objFile.Name & "<br>"
          nID = nID + 1 ' Increase file ID
     Next

     For Each objSubFolder in objFolder.SubFolders
         
          Response.Write "<br><br>ID: " & nID & ", Parent: " & nParentID & ", Name: " & objSubFolder.Name & "<br><br>"
         
          IterateThroughDirectory objSubFolder, nID
         
          nID = nID + 1 ' Increase file ID
     Next

     
End Sub

%>
0
 
hujiCommented:
Better even:



<table>
<tr><th>ID</th><th>Parent</th><th>Name</th><th>Type</th></tr>
<%
sFilePath = "C:\Inetpub\wwwroot\Test\Article"

Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(sFilePath)

nID = 1

Response.Write "<tr><td>" & nID & "</td><td> N/A</td><td> " & objFolder.Name & "</td><td>Folder</td></tr>"
IterateThroughDirectory objFolder, 0

Set objFSO = Nothing


' Sub to iterate through directory
'========================
     
Sub IterateThroughDirectory(objFolder,nParentID)

     For Each objFile in objFolder.Files
          Response.Write "<tr><td>" & nID & "</td><td>" & nParentID & "</td><td>" & objFile.Name & "</td><td>File</td></tr>"
          nID = nID + 1 ' Increase file ID
     Next

     For Each objSubFolder in objFolder.SubFolders
         
          Response.Write "<tr><td>" & nID & "</td><td>" & nParentID & "</td><td>" & objSubFolder.Name & "</td><td>Folder</td></tr>"
         
          IterateThroughDirectory objSubFolder, nID
         
          nID = nID + 1 ' Increase file ID
     Next

     
End Sub

%>
</table>
0
 
anthony6482Author Commented:
Thanks Huji for the answer but this is still not working...

This is what I get from your code...

I have tabbed the output below to show how the directory/file structure actually exists in my hard drive

As you can see, the parent id number is not correct.

Please help! Many thanks :)

ID Parent Name Type
1 N/A DocumentLibrary Folder
1 0 base.html File
2 0 index.html File
3 0 menu.html File
4 0 About the Trust Folder
    4 4 Charity Events Folder
       4 4 Fundraising.pdf File
    6 6 Code of Conduct and Financial Regs Folder
       6 6 conduct_regulations.html File
    8 8 Core Values and Objectives Folder
       8 8 filelist.xml File
       9 9 image001.gif File
       10 10 image002.gif File
       11 11 image003.gif File
       12 12 Strategic Review Revised July 2005 - final version.pdf File
   14 14 health_safety Folder
       14 14 Case House - Evacuation Procedure.pdf File
       15 15 Health_Safety_Handbook_2002.pdf File
       16 16 HS_Committee.html File
       17 17 index.html File
       18 18 Ist Aider List.pdf File
       19 19 Lists of Hospitals for notice boards.pdf File
   21 21 Jobs Folder
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
hujiCommented:
Well, I didn't check it correctly. Excuse me for that. I think this will fix it for you:



<table>
<tr><th>ID</th><th>Parent</th><th>Name</th><th>Type</th></tr>
<%
sFilePath = "C:\Inetpub\wwwroot\Test\Article"

Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(sFilePath)

nID = 1

Response.Write "<tr><td>" & nID & "</td><td> N/A</td><td> " & objFolder.Name & "</td><td>Folder</td></tr>"
IterateThroughDirectory objFolder, 0

Set objFSO = Nothing


' Sub to iterate through directory
'========================
     
Sub IterateThroughDirectory(objFolder,nParentID)

     For Each objFile in objFolder.Files
          Response.Write "<tr><td>" & nID & "</td><td>" & nParentID & "</td><td>" & objFile.Name & "</td><td>File</td></tr>"
          nID = nID + 1 ' Increase file ID
     Next

     For Each objSubFolder in objFolder.SubFolders
         
          Response.Write "<tr><td>" & nID & "</td><td>" & nParentID & "</td><td>" & objSubFolder.Name & "</td><td>Folder</td></tr>"

              nID = nID + 1 ' Increase file ID
         
          IterateThroughDirectory objSubFolder, nID-1
         
     Next

     
End Sub

%>
</table>
0
 
anthony6482Author Commented:
Hey Huji,

Thanks for that, at first glance everything seems to work great! Only thing I changed was the line:

IterateThroughDirectory objFolder, 0

To

IterateThroughDirectory objFolder, 1

As the files in the root directory were showing 0 as the parent ID which should show 1 instead.

Brilliant mate, thanks VERY much indeed. 500 points well earned :)

Anthony
0
 
hujiCommented:
You are very welcome. I appreciate your posting the basic code and letting me put less time to find the solution, as well.
Huji
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now