Splitting entries in a database table and adding them to another table?

Hi,

I have a table with entries within it which need 'splitting up'

My table is called ficsFiles and the colums and data format are as follows:

path                                                               name                                             add_date
c:\FICS\DropBox\Photos\02\006-S\AA1       IMG00020-20110924-1441.jpg      2011-09025 17:38:53

There are many more of these entries but what I would like to do is have this data split. The first column - PATH, this needs to be split so the following is in it's own column in a table called FICSProcessed:

The PATH should be retained in a column called PATH
then the last part of the path is broken down:

cityCode     storeCode    StoreType    gridX
02               006              S                   AA1

The second column, needs splitting as follows and I'm sure some conversion needs to take place also:

fileName    datetime
IMG00020  2011-09-24 14:41

I hope I have explained this well enough, if anyone has any idea how to do this it would be much appreciated.

I'm not sure if this should be completed as a trigger script or I run a timed script to copy the entries from the original ficsFiles table to the FICSProcessed table? Again, any advice would be much appreciated.

I have added the script that put the data in the first table, maybe this could do what I need it to do and not need a second conversion?

Best Regards,

Ken

' 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="C:\FICS\Dropbox"     	' <<---- change to suit your server
MyDSN="T1"                 ' <<--- an ODBC DSN that points to your DB
MyTablename="ficsFiles"        	' <<--- 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 & ";Uid=sa;Pwd=LZnjUAp4;" 
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

Select allOpen in new window