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
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:
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?
' Create a windows scheduler entry to run the script as often as required
' cscript -nologo PATH_WHERE_SCRIPT_STORED\jpgs_to_db.vbs
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;"
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
' --- process subfolders of current folder
Set subFolders = folder.SubFolders
For Each fldr in subFolders
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() )"
Set recset = ADO.execute(sql)
If not recset.EOF Then getSql=recset(0)
' wscript.echo s