?
Solved

How can I put folder contents into a table?

Posted on 2012-08-21
6
Medium Priority
?
1,173 Views
Last Modified: 2012-08-21
I have a folder containing over 100,000 gif images.  Each image has a 15 digit file name:

190101101000500.gif
190101101000502.gif
190101101000506.gif
190101101000509.gif

I would like to get the list of images into a table to I can query the results.

Any help would be appreciated.
0
Comment
Question by:DanielAttard
6 Comments
 
LVL 23

Accepted Solution

by:
Stelian Stan earned 1000 total points
ID: 38316702
You can use powershell:

Get-ChildItem C:\Windows\* -Include *.gif | ft > C:\gif.txt

Change the path from C:\Windows to your path.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 38316865
Hi,

Here are 2 functions that I use.  Paste the code below into a new module:



Function DoIt(vFolder As String)
    Dim vList As Variant, i As Integer, vFileNameFound As String, v
    vList = FileList(vFolder, "*.*")
    If TypeName(vList) <> "Boolean" Then
        For i = LBound(vList) To UBound(vList)
            vFileNameFound = vList(i)
            Debug.Print vFileNameFound
           'CurrentDB.Execute("INSERT INTO tablename (columnname) VALUES('" & vFileNameFound & "');"
        Next
    End If
   
End Function

Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
    Dim sTemp As String, sHldr As String
    If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
    sTemp = Dir(fldr & fltr)
    If sTemp = "" Then
        FileList = False
        Exit Function
    End If
    Do
        sHldr = Dir
        If sHldr = "" Then Exit Do
        sTemp = sTemp & "|" & sHldr
     Loop
    FileList = Split(sTemp, "|")
End Function




Once you have that done then test it in the immediate window using:

?DoIt("C:\Windows")

This will list all the files in the Windows folder to the debug window.
Uncomment the execute command and fix the SQL statement to insert the file name into your table and column.

Regards,

Bill
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1000 total points
ID: 38316944
Make a table:
Table name: tblFiles
Fileds:
FileID (Primary Key, AutoNumber)
FileName (text, 255)


Put code like this on a button on a form:

Dim strFolderName As String
Dim strFileName As String
strFolderName = "c:\Yourfolder\"

'Clear out the old file names
CurrentDb.Execute "DELETE * FROM tblFiles", dbFailOnError

strFileName = Dir(strFolderName & "*.Gif")

Do While strFileName <> ""
    strFileName = Dir
    CurrentDb.Execute "INSERT INTO tblFiles (FileName) VALUES (" & "'" & strFileName & "'" & ")", dbFailOnError
Loop
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 40

Expert Comment

by:als315
ID: 38317532
Jeff, for 100,000 files I like more your code, but you should move  strFileName = Dir to the end of loop. It will be also very interesting to compare  execution time of code with execute and direct write to table:
Dim strFolderName As String
Dim strFileName As String
Dim rs as DAO.Recorset
strFolderName = "c:\Yourfolder\"

'Clear out the old file names
CurrentDb.Execute "DELETE * FROM tblFiles", dbFailOnError
Set rs = CurrentDB.OpenRecordset("tblFIles")
strFileName = Dir(strFolderName & "*.Gif")

Do While strFileName <> ""
    rs.addNew
    rs!FileName = strFileName
    rs.update
    strFileName = Dir
Loop
rs.close
Set rs = Nothing

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38317716
als315.

LOL

You are probably correct...
That is just something I threw together in a hurry...
;-)

Jeff
0
 

Author Closing Comment

by:DanielAttard
ID: 38318026
Thanks guys.  Great work!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

IF you are either unfamiliar with rootkits, or want to know more about them, read on ....
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

621 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