How can I put folder contents into a table?

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


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

Any help would be appreciated.
Question by:DanielAttard
    LVL 23

    Accepted Solution

    You can use powershell:

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

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

    Expert Comment

    by:Bill Ross

    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 & "');"
        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
            sHldr = Dir
            If sHldr = "" Then Exit Do
            sTemp = sTemp & "|" & sHldr
        FileList = Split(sTemp, "|")
    End Function

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


    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.


    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    Make a table:
    Table name: tblFiles
    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
    LVL 39

    Expert Comment

    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!FileName = strFileName
        strFileName = Dir
    Set rs = Nothing

    Open in new window

    LVL 74

    Expert Comment

    by:Jeffrey Coachman


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


    Author Closing Comment

    Thanks guys.  Great work!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    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!

    Article by: Lee
    Windows 7 Ultimate and Enterprise (and 2008 R2) introduced a new feature you may not be aware of - Boot from VHD.   Boot from VHD (or what Microsoft refers to asNative Boot allows you to install Windows to a VHD (Virtual Hard Disk) file that is t…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    This Micro Tutorial will teach you the basics of configuring your computer to improve its speed. It will also teach you how to disable programs that are running in the background simultaneously. This will be demonstrated using Windows 7 operating…
    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…

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now