Wiki Home

Replace To Insert


Namespace: WIN_COM_API
Here is some code to help upgrade APPEND BLANK/REPLACE commands to an INSERT command. This is version 2. Version 1 code didn't work if there were function calls with parameters separated by commas. Version 1.1 didn't either. Ver 2 is much better, but still doesn't handle these two:
replace quote with "may the force be with you" and replace with with m.with If anyone names a field or var with, I will punch them. I left the previous code around in case someone wants to make a version 3.

* ReplaceToInsert.prg
* Turns a REPLACE statement into an INSERT (you need to fill in the tablename.)

clear
* _ClipText = ;
"Replace ;
	kClient_fk With lkClient, ;
	kCashReceipt_fk With lkCashReceipt, ;
	kBillPayItem_fk With lkBillPayItem, ;
	dTransactionDate With itcrcm.PayDate, ;
	yTransactionAmount With itcrcm.xActAmt"

 _ClipText = ;
[	replace neconum WITH m.i_beanum,;
		cfirst_name WITH cProper( m.i_fstnam, 1 ),;
		clast_name WITH cProper( m.i_lstnam, 1 ),;
		njdyear WITH IIF(.not. EMPTY(i_jdyear),VAL("19"+i_jdyear),0),;
		csalutation WITH cProper( m.i_prenam, 0 ) ]


lcRepls = _cliptext

? lcRepls

* Remove tab/cr/lf/;/"replace"
lcRepls = chrtran( lcRepls, Chr(9)+chr(13)+chr(10)+[;], [ ] )
lcRepls = StrTran( lcRepls, [replace], [], 1,1,1)

?
? [REPL ] + lcRepls

* To make the 'edge case' easier, add a comma to the end
lcRepls = lcRepls + ','

lcFieldList = ""
lcExprList = ""
lnField = 1
Do while .t.
	? lnField, lcRepls
	lcField = StrExtract( lcRepls, "", " with ", 1, 1 )
	If Empty( lcField )
		Exit
	EndIf
	* the field is whatever is after the last comma
	lcField = allTrim( Substr( lcField, Rat( ",",lcField)+1) )
	
	lcExpr = allTrim( StrExtract( lcRepls, " with ", " with ", 1, 3 ) )
	* The expressioin is everthing upto the last comma
	lcExpr = Substr( lcExpr, 1, Rat( ",",lcExpr)-1 )
	
	? lcField + " WITH " + lcExpr

	lcFieldList = Iif(empty(lcFieldList), [], lcFieldList + [, ]) + lcField
	lcExprList = Iif(empty(lcExprList ), [], lcExprList + [, ]) + lcExpr
	
	lcRepls = Substr( lcRepls, At( "with", Lower(lcRepls) )+4 )
	
	lnField = lnField + 1
	?
	
EndDo


? [Fields: ] + lcFieldList
? [Expressions: ] + lcExprList

Text to lcInsert textmerge noshow
Insert into TableName ;
	( << lcFieldList >> ) ;
	values ;
	( << lcExprList >> )
EndText

? lcInsert
_ClipText = lcInsert

Return



* swap ,'s we need to keep for chr(0)
* so anything inside () is kept
x=1
lcTarget = StrExtract( lcRepls, "(", ")", x, 4 )
Do while !Empty( lcTarget )
	lcNew = StrTran( lcTarget, ",", " BAM! " )
	lcRepls = StrTran( lcRepls, lcTarget, lcNew )
	x = x + 1

	lcTarget = StrExtract( lcRepls, "(", ")", x, 4 )
EndDo
_ClipText = lcRepls
Return

lnRepls = ALines( laRepls, lcRepls, .t., "," )

lcFieldList = ""
For lnRepl = 1 to lnRepls
	lcRepl = laRepls[lnRepl]
	lcField = StrExtract( lcRepl, [], [ with ],1,1 )
	lcFieldList = Iif(empty(lcFieldList), [], lcFieldList + [, ]) + lcField
EndFor
? [Fields: ] + lcFieldList


lcExprList = ""
For lnRepl = 1 to lnRepls
	lcRepl = laRepls[lnRepl]
	lcExpr = StrExtract( lcRepl, [ with ], [], 1, 1 )
	lcExprList = Iif(empty(lcExprList ), [], lcExprList + [, ]) + lcExpr
EndFor
? [Expressions: ] + lcExprList

Text to lcInsert textmerge noshow
Insert into TableName ;
	( << lcFieldList >> ) ;
	values ;
	( << lcExprList >> )
EndText

? lcInsert
_ClipText = lcInsert

***
Another case I hate to see:
  append blank
  replace field1 with value1
  replace field2 with value2
  ...
  replace field35 with value35

So to avoid the more complex parsing problems above, the following handles only this case.

** fill clipboard with equivalent of a code highlight/copy
_cliptext = [replace field1 with 'value1'] + chr(13) + ;
			  [replace field2 with 'value2'] + chr(13) + ;
			  [replace field3 with iif(.t.,v3t,v3f) ] + chr(13)

_cliptext = replaceToInsert(_cliptext, "tablename")

function replaceToInsert
lparameters  tcIn, tcTbl

lcFieldList = ""
lcValueList = ""

lnLines = alines( laLines, tcIn )

for lnI = 1 to lnLines
	lcLine = laLines(lnI)
	lcRepl = lower("replace")
	lcWith = lower(" with ")
	
	lnFieldStart = at(lcRepl, lower(lcLine) )+len(lcRepl )+ 1
	lnWithAt = at(lcWith, lower(lcLine))
	
	lcFieldList = lcFieldList + substr(lcLine, lnFieldStart, lnWithAt-lnFieldStart)
	lcValueList = lcValueList + substr(lcLine, lnWithAt + len(lcWith) )
	
	if lnI < lnLines
		lcFieldList = lcFieldList + ","
		lcValueList = lcValueList + ","
	endif
next

lcOut = "insert into " + tcTbl + " (" + lcFieldList + ") values (" + lcValueList + ")"

return lcOut



Contributors: Carl Karsten, MikeDougherty
( Topic last updated: 2004.09.28 10:52:41 AM )