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
Solved

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

Posted on 2011-09-25
5
180 Views
Last Modified: 2012-05-12
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

Open in new window

0
Comment
Question by:kenuk110
  • 3
5 Comments
 
LVL 5

Expert Comment

by:tygrus2
ID: 36597038
You could keep track of the level of recursion relative to the base path and pass the
level as long, cityCode, storeCode, StoreType, gridX as string to recursive file search.

OR
FileSystemObject help
see code
'Calculate the length of the base folder
i = len("c:\FICS\DropBox\Photos")   '=22
'substring the path to chop the start
temp1 = mid(path, i)
'Convert string to array
PathSplit = Split(temp1,"\")
cityCode = PathSplit(0)
storeCode = PathSplit(1)
StoreType = PathSplit(2)
gridX = PathSplit(3)

Open in new window

That's a bit rough and I haven't tested it, but it should point you into the right direction.
0
 

Author Comment

by:kenuk110
ID: 36597911
Hi,

I'm not really sure about how to implement the solution or even the advice, I'm struggling my way through this lot as I'm not really a programmer, well not even 'not really', I'm simply not!

Looking at the code though, where you have the PathSplit sections, do I need to tell it how many characters to got through before it 'splits'?

Sorry for the stupid questions!

Regards,

Ken
0
 
LVL 5

Expert Comment

by:tygrus2
ID: 36598058
The 2nd parameter for split is the character to be used as the delimiter. It doesn't care how many and it returns an array (object) of strings with as many elements as needed for the number of strings found.
Dim PathSplit as Variant 'or empty array
Dim i as long
Dim temp1 as string

Open in new window

I assume you already had the 'path' and you can replace the target fieldnames (citycode etc.) with references to the database fields. Temp1 has the "c:\FICS\DropBox\Photos" part removed though you might have to add 1 to i so it shoulf be 23 not 22.
If you are writting code in Visual Studio then you should have access to the help pages that explain the objects & methods etc. available and how to use them. Else, search the internet for the class, object, event and or method and the language you are using.
check out
fso.GetExtensionName(path as string)
fso.GetParentFolderName(path as string)

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36598234
Hi,

This path will be static ?

"c:\FICS\DropBox\Photos"

2nd

This format will be fixed ?

"02\006-S\AA1"

- Bhavesh
0
 
LVL 5

Accepted Solution

by:
tygrus2 earned 500 total points
ID: 36598727
...02\006-S\AA1
I think you can have a list of delimiters which is needed because you have the dash. If the structure doesn't change then..
left(string, length)
mid(string, startpos, length)
cityCode = left(temp1,2)
storeCode = mid(temp1,4,3)
StoreType = mid(temp1,8,1)
gridX = mid(temp1,10,3)

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 42
SQL Error - Query 6 41
stress test workstation for SQL 2016 for CPU-specific evaluation 3 22
sql 2016 data tools breakdown.. 1 11
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

791 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