Solved

how to replace escape characters ?

Posted on 2008-06-18
15
1,865 Views
Last Modified: 2012-08-13
I am extracting the formula fieds from crystal reports. The following is an example : I need to extract only the string constants from the entire FormulaFields.item(x).Text field and write them onto the file.  I am stuck with the escape characters and an easy way to pull out strings from the formula text. Here is a below example , basically every text that I need to pull is wrapped in between \" and \" - how do I write a function to do this ?

'myRpt.FormulaFields.item(2).Text
"\"Student Total :\" + {StudentDetail_ttx.StudentID} + \" - \" + {StudentDetail_ttx.StudentName}"

'myRpt.FormulaFields.item(3).Text
"\"Individual Total :\" + {StudentDetail_ttx.StudentID} + \" - \" + {StudentDetail_ttx.StudentName}"

'myRpt.FormulaFields.item(4).Text
"{StudentDetail_ttx.StudentID} + \" - \" + \"Student ID :\" "


0
Comment
Question by:tech_question
[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
  • 7
  • 5
  • 3
15 Comments
 
LVL 7

Accepted Solution

by:
TRobinJames earned 250 total points
ID: 21814635
Use a Regex or Split function.  There are lots of references like this one:
http://www.regular-expressions.info/tutorial.html

Here is split function definition:

Split(expression[, delimiter[, count[, compare]]])

The Split function syntax has these parts:

Part Description
expression Required.String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.
delimiter Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.
count Optional. Number of substrings to be returned; 1 indicates that all substrings are returned.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.

0
 

Author Comment

by:tech_question
ID: 21814686
how do I replace \" with something ? I am able to split it with "+" and get back the string but then how do I replace the " \" " character ?
0
 
LVL 7

Expert Comment

by:TRobinJames
ID: 21815418
So you have this:

"\"Student Total :\" + {StudentDetail_ttx.StudentID} + \" - \" + {StudentDetail_ttx.StudentName}"


What is it that you want? This?: "StudentDetail_ttx.StudentID" and this?: "StudentDetail_ttx.StudentName"    Show me an example.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:tech_question
ID: 21815735
" \"Student Total :\" + {StudentDetail_ttx.StudentID} + \" - \" + {StudentDetail_ttx.StudentName}"
the output should be all strings i.e.all strings between \" and \"  , eg for the above statement I should get the below output :
Student Total:
-
0
 
LVL 7

Expert Comment

by:TRobinJames
ID: 21816353
Replace \" with  "" as below:

          exp = Replace(exp, "\" & Chr(34), "")

then split into 4 returns

      names() = Split(exp, "+")

and throw out names() with Instr(1,names(),"{")
0
 
LVL 7

Expert Comment

by:TRobinJames
ID: 21816671
Actually, I think

          Split(exp, "\" & Chr(34))

and then throw out

          Instr(1,names(),"{")  

is better, because some formulas may have operators other than +.
0
 

Author Comment

by:tech_question
ID: 21827484
James thanks for your post. I have gone through all the reports : this is what I am doing to extract out the string literals. first I split them with "  then run a regular expression ({|if|then|case.*:|space*|)  - I am pretty much getting everything what I want with all the functions that have been used in these reports are pretty basic. My question to you is is there a better way to accomplish what I am doing - to extract out all string literals from the crystal objects other than the keywords ofcourse ?
0
 
LVL 7

Expert Comment

by:TRobinJames
ID: 21831185
Not that I know of. I think it would have to be a Crystal Reports function, and as I do not claim to know all the Crystal Reports functionallity, you may want to reword a new question in the Zone bleow very specific to Crystal Reports formula extraction and parsing functionallity.

Database // Reporting // Crystal Reports
0
 

Author Comment

by:tech_question
ID: 21832055
James, All I need is a regular expression that gets me matches anything between double quotes :
eg: I have a sample " test123 " in this sentence and the number is " 1234"  
 I need  the output to be : test123 and 1234

is this right ? regExp   = "(\""\w*\"")"            
                      
0
 
LVL 7

Expert Comment

by:TRobinJames
ID: 21832527
These quotes are going to give problems in regex, so use the hex equivalent \x27
0
 
LVL 7

Expert Comment

by:TRobinJames
ID: 21834250
Even if you use Regex , you will get 3 matches and you are going to have to rremove the {} match.  I would stay away from Regex in this example and use a combination of split , replace, instr as in above postings.  Crystal reports is obviously using \" to separate arguments whether string or value and are providing {} encapsulation of values.  Their formula parser will be using a scheme similar to what you are trying to do, and I bet they a splitting the arguments using \" delimiter and determining whether value or string by identifying the splits with {}.
0
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21835486
Adapted from your other question, the following function will work for your requirements in this question.
wscript.echo GetQuotedText("This is the greatest "" test ........"" that happended during ""worldwar1 """)
 
Function GetQuotedText(strText)
' Returns just the text between quotes
	Dim objRegExp, strOutput
	dim colMatches, objMatch
	Set objRegExp = New Regexp
	
	strOutput=""
	objRegExp.IgnoreCase = True
	objRegExp.Global = True
	objRegExp.Pattern = "\"".*\"""
 
	Set colMatches = objRegExp.Execute(strText)
   For Each objMatch In colMatches   ' Iterate Matches collection.
		strOutput=strOutput & Mid(objMatch.Value,2,len(objMatch.Value)-2) & vbCrLf
    Next
 
	GetQuotedText = strOutput    'Return the value of strOutput
 
	Set objRegExp = Nothing
End Function

Open in new window

0
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21835503
Oops... no that won't work... I'll post code very shortly that will though...
0
 
LVL 24

Assisted Solution

by:purplepomegranite
purplepomegranite earned 250 total points
ID: 21835525
The attached works.  Forgot to make the regexp none greedy.
wscript.echo GetQuotedText("\""Student Total :\"" + {StudentDetail_ttx.StudentID} + \"" - \"" + {StudentDetail_ttx.StudentName}")
 
 
Function GetQuotedText(strText)
' Returns just the text between quotes
	Dim objRegExp, strOutput
	dim colMatches, objMatch
	Set objRegExp = New Regexp
	
	strOutput=""
	objRegExp.IgnoreCase = True
	objRegExp.Global = True
	objRegExp.Pattern = "(\\"".*?\\"")"
 
	Set colMatches = objRegExp.Execute(strText)
   For Each objMatch In colMatches   ' Iterate Matches collection.
		strOutput=strOutput & Mid(objMatch.Value,3,len(objMatch.Value)-4) & vbCrLf
    Next
 
	GetQuotedText = strOutput    'Return the value of strOutput
 
	Set objRegExp = Nothing
End Function

Open in new window

0
 

Author Comment

by:tech_question
ID: 21837670
Purple and Robin - thank you so much for your posts. So when I split the function and got every second string from the array, it works beautifully. but I did learn a lot about regular expressions also. thanks.
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

717 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