Insert file names and attributes into SQL Server

Hi!

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!

LVL 1
ZaurbAsked:
Who is Participating?
 
frankyteeConnect With a Mentor Commented:
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
0
 
frankyteeCommented:
use the file system object
0
 
frankyteeCommented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ZaurbAuthor Commented:
Hello!

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!
0
 
frankyteeCommented:
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
http://www.sqlservercentral.com/articles/DTS/dts/159/
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
http://www.mssqlcity.com/Articles/General/OleAutSP.htm

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.

0
 
ZaurbAuthor Commented:
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.

Thanks!
0
 
ZaurbAuthor Commented:
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
Next
f.Close

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?

Thanks!!!

0
All Courses

From novice to tech pro — start learning today.