Insert file names and attributes into SQL Server

Posted on 2007-09-28
Last Modified: 2013-11-27

I have several folders each one containing many files and I need to accomplish the following task:
Insert names and attributes (size, date created/modified) of those files into a SQL Server and have the database then automatically updated every time new files are added into those folders.

Am using Microsoft SQL Server 2005 Express edition and Visual Web Developer Express to build a web interface to produce the customized view from SQL Server.

Thank you!

Question by:Zaurb
    LVL 19

    Expert Comment

    use the file system object
    LVL 19

    Expert Comment

    eg below:

        Dim fso As FileSystemObject
        Dim fld As Folder
        Dim file As file
        Set fso = New FileSystemObject
        Set fld = fso.GetFolder("c:\whateverfolder\")
        For Each file In fld.Files
            'do whatever, insert into table etc
            'eg below print to debug window
            With file
            Debug.Print "name: " & .Name & ",datecreated: " & .DateCreated _
                    & ", datemod: " & .DateLastModified & ", size: " & .Size
            End With
        Next file
        Set fso = Nothing
    LVL 1

    Author Comment


    Please, can you provide more details on the procedure above? I mean, is there a way to automate collecting file names and attributes within sql procedure? What you've offered is I believe is for ASP/VB, right?

    Thank you!
    LVL 19

    Expert Comment

    there is probably several ways to do this but for me the easiest way would be to
    1) create DTS package and inside it create an active x script (vb script) using the vb code i posted above. you'll have to modify it to fit vbscript standards (all variables are automatically variant etc, use "createobject" method etc).
    now to execute this DTS package from sql server:
    below i got from
    2)     Schedule the DTS Package.
    3)     Edit the newly created job and delete the schedule.
    4)     Now you have an unscheduled job that is ready to be run from within Query Analyzer.
    5)     Execute the stored procedure sp_start_job, passing in either the GUID of the job or the job's name from Query Analyzer.

    another option (i'm not sure if Microsoft SQL Server 2005 Express edition has DTS/SSIS), is to save it as an external vbscript and execute it from sql which i think would take longer to write, see eg below

    re: database then automatically updated every time new files are added into those folders.
    you'll probably have to create some sort of timer event in your front end to detect new files or run that procedure (say every 5 minutes etc)

    good luck, its bedtime for me now so let me know how you go.

    LVL 1

    Author Comment

    The DTS is not available for SQL Express edition. However, old good bcp is still around. I will see whether I can use it to get my project up and running. I will write more as I proceed. This is my first project in the field and I will really need some help in it.

    The project actually must accomplish the following:
    1) People from several offices will add their files into a share on one centralized server.
    2) File list must be imported into MS-SQL database along with file names, date added, file size, extension, etc.
    3) Once the file list is in an SQL database additional information will be linked to these file names in SQL basing on file name (like to which component or model of the product this file refers).
    4) Make all the above visible through the web interface.

    Nothing is yet implemented but the idea is as described above.

    LVL 1

    Author Comment

    Hi frankytee,

    Thanks to your advise I'm getting closer to resolving this part of my project. So, I've written a script like this:

    aFile = "C:\MyTests\FS\Scripts\2.Get_File_Data_WSH.vbs.txt"
    Set fs = CreateObject ("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile (aFile, forWriting, True)

    On Error Resume Next

    Dim FolderPath        'path to the folder to be searched for files
    Dim objFSO            'the FileSystemObject
    Dim objFolder         'the folder object
    Dim colFiles          'collection of files from files method
    Dim objFile           'individual file object
    'Dim objFileName

    FolderPath = "C:\MyTests\FS\FilesToTest"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(FolderPath)
    Set colFiles = objFolder.Files

    For Each objFile in colFiles
      f.Write "0"
      f.Write VbTab & objFile.Name
      f.Write VbTab & objFile.Size
      f.Write VbTab & objFile.DateLastModified & vbcrlf

    Now, when I get the list of files in a flat file I use bcp utility to export data into SQL server.
    The only thing which I couldn't do yet is to strip the date DateLastModified format.
    I mean, how do I get 9/30/2007 11:04:58 without AM/PM, or without time (only date), or without seconds, or have the time produced in 24-hour format?
    Sorry for so many questions, but can you give me any hint on manipulating date/time output?


    LVL 19

    Accepted Solution

    the simplest way i can think of is to set your date and time settings thru control pane on your Server and return leading zeros (this is the key)
    make sure date settings on pc is dd/MM/yyyy and leading zeros for your time settings,
    eg HH:mm:ss tt  'note double characters for all part
    here the capital HH make sure leading zeros and 24 hour time (small h is for AM/PM),
    this means all dates will be 10 characters ( 8 for the date and 2 for the seperators)
    and the time part will be 8 characters ( 6 for the time and 2 for the seperators)

    then to get date only:
    = Left(objFile.DateLastModified, 10) 'date only

    to get time part
    Right(objFile.DateLastModified, 8) 'time only

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    754 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

    24 Experts available now in Live!

    Get 1:1 Help Now