?
Solved

Create D/B records that are entered from files saved in directories

Posted on 2011-09-24
5
Medium Priority
?
164 Views
Last Modified: 2012-05-12
Hi,

I have files which are delivered through DropBox to my file server, what I'd like to do it when a file is 'dropped', something scans the directories, finds the latest saved file, enters a record in the DB which points to this file.

Is this possible? The naming convention of all the files is the same so the format won't change, the directory it resides in though is the differentiator.

So, the filename is like this:

IMG00001-201109024-1441.jpg

the length of each part won't change, so the first part IMG00001 will always be IMG003432 for example, the format will never change.

The directories are similar:

\\photos\02\006-S\AA12

the second and third part of the structure will never change from a 2 digit/5 digit structure, the last part could be 2-4 alphanumerics.

I know this is probably impossible but thought I'd ask the question anyway!!

Any help would be much appreciated.

Regards,

Ken



0
Comment
Question by:kenuk110
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:nrbreen
ID: 36593869
Very little is impossible,
what approx number of folders and files need to be catered for?
0
 
LVL 3

Accepted Solution

by:
nrbreen earned 2000 total points
ID: 36594039
The following VBscript will scan all folders under the nominated TopFolder,
locate any JPG file, and add the folderpath, filename, and added-date-time  to a DB table.
It can be run either from the server, or from another machine that can access the folders.
An ODBC DSN must be setup to point to your DB, and you must create an appropriate table.
The script can be run at regular intervals via Windows Scheduler.
You can test it by running from a command prompt, after changing highlighted values to suit your setup.

' Create a windows scheduler entry to run the script as often as required
' cscript -nologo PATH_WHERE_SCRIPT_STORED\jpgs_to_db.vbs

'------------------------
option explicit

dim TopFolder, MyDSN, MyTablename
dim FSO, ADO
 
 TopFolder="X:\photos"     ' <<---- change to suit your server
 
 MyDSN="mydbdsn"                   ' <<--- an ODBC DSN that points to your DB
 MyTablename="jpg_files"        ' <<--- change to suit your tablename
    ' create table  jpg_files(path varchar(50), name varchar(30), add_date datetime)
    ' then  in   sub processFile   change column-name references if necessary to suit

 Set FSO = CreateObject("Scripting.FileSystemObject")   
 Set ADO = CreateObject("ADODB.Connection")             
 ADO.ConnectionString= "DSN=" & MyDSN & ";"
 ADO.open  

 ScanFolder topfolder
 
 ADO.close
 
'------------------
sub ScanFolder(path)
Dim folder, subFolders, fldr, subfiles, fil
 
  print "Fold: " & path
  Set folder = fso.GetFolder(path)

  set subfiles  = folder.files
  For Each fil in subfiles
    processFile path, fil
  next

    ' --- process subfolders of current folder
  Set subFolders = folder.SubFolders
  For Each fldr in subFolders     
       call scanfolder(fldr)
  Next

End sub

'-----------------
sub processFile(path, fil)
Dim f, f1, fc, sql
  
  print "File: " & fil & "    Path: " & path & "    name: " & fil.name
  if right(lcase(fil),4)<>".jpg" then exit sub      '  a simple filter for JPGs only
  
                    ' --- change references to columns  path, name, add_date   to suit your table
                    
  sql="select count(*) from " & MyTablename & " where path='" & path & "' and name='" & fil.name & "'"
  
  if getSql(sql)=0 then     ' add record
    sql = "insert into " & MyTablename & " (path, name, add_date) values('" & path & "', '" & fil.name & "', getdate() )"
    ADO.execute(sql)
  end if  
End sub

'-------------
function getSql(sql)
dim recset
  Set recset = ADO.execute(sql)
  If not recset.EOF Then getSql=recset(0)
end function
'--------
sub print(s)
  wscript.echo s
end sub

Open in new window

0
 

Author Comment

by:kenuk110
ID: 36594473
Thank you nrbreen!

Let me try this on my system, I really appreciate it, I'm not a programmer and haven't also used VB, the limited stuff I have done has been on C Sharp but let me change the parts you have suggested.

Really appreciate it.

Cheers,

Ken
0
 

Author Comment

by:kenuk110
ID: 36595198
Hi nrbreen,

Right, I have gone through the file, it all came in in one long string so I had to press enter a load of times, hence taking a little time to get back to you!

It all seems to be trying to do what it should but I have an issue where it requires the username and password for our SQL Server. Is there a way to add this information to the connection string as the message I get back is:

Login failed for user''.

Any ideas?
0
 

Author Comment

by:kenuk110
ID: 36595235
Hi again,

It works!!! I found the additional info on the net so added it and it works like a dream!

I really appreciate your help on this.

Best Regards,

Ken
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

762 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