PDA

View Full Version : Solved: Easy one? - replace unknown characters in string


denon
03-11-2009, 05:38 AM
Hi,

I have a string that is a bit like the example below.


<product datestamp="20090216"><node1>blah blah blah</node1>
<node2>blah blah blah</node2></product>

<product datestamp="20080720"><node1>blah blah blah</node1>
<node2>blah blah blah</node2></product>

<product datestamp="20091023"><node1>blah blah blah</node1>
<node2>blah blah blah</node2></product>
I need to replace '<product datestamp="********">' with '<product>'

i.e. find '<product datestamp="' + any 8 characters and '">' and replace.

I know it is probably really easy but I am stuck.

Please help :)

Thanks
CreganTur
03-11-2009, 06:12 AM
Welcome to the forum- it's always great to see new members.

Will your strings always be like this: <product datestamp="20090216"><node1>blah blah blah</node1>
<node2>blah blah blah</node2></product>

Or do you have to find the string within another string?

If the string will be in the above format all the time, the you could do what you want using:
Sub test()
Dim strStart As String
strStart = "<product datestamp='20090216'><node1>blah blah blah</node1><node2>blah blah blah</node2></product>"
MsgBox "<product>" & Right(strStart, Len(strStart) - 30)

End Sub

Place the code above in a new module, put your cursor anywhere within it, and then press F5 to run it, so you can see the example run.

HTH:thumb
denon
03-11-2009, 06:59 AM
Hi Randy,

Thanks very much for your prompt reply.

I think we are getting our wires crossed slightly. I am talking about 1 string with many of these product tags in it.

I will give a larger example of the string (xml) below:


<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE ONIXmessage SYSTEM "vbaexpress will not allow links - yet :)">
<ONIXmessage release="2.1">
<m174>Any Publisher</m174>

<m175>Name, 00948747</m175>

<m283>email@email.com</m283>

<m182>20090309</m182>

<product datestamp="20090216">
<a001>3246692</a001>

<a002>03</a002>

<productidentifier>
<b221>03</b221>

<b244>9783447057127</b244>
</productidentifier>

<productidentifier>
<b221>15</b221>

<b244>9783447057127</b244>
</productidentifier>

<b012>BC</b012>

<b014>PB</b014>

<series>
<seriesidentifier>
<b273>04</b273>

<b244>TT159</b244>
</seriesidentifier>

<title>
<b202>01</b202>

<b203>Philippika</b203>

<b029>Marburger Altertumskundliche Abhandlungen</b029>
</title>

<b019>27</b019>
</series>

<title>
<b202>01</b202>

<b203>Innovationen in der Privatplastik</b203>
</title>

<contributor>
<b034>1</b034>

<b035>A01</b035>

<b340>1</b340>

<b036>Edith Bernhauer</b036>

<b037>Bernhauer, Edith</b037>

<b039>Edith</b039>

<b040>Bernhauer</b040>
</contributor>

<b057>1</b057>

<language>
<b253>01</b253>

<b252>ger</b252>
</language>

<b061>252</b061>

<mainsubject>
<b191>26</b191>

<b068>2.0</b068>

<b069>1582</b069>

<b070>Hardcover, Softcover / Kunst/Kunstgeschichte</b070>
</mainsubject>

<subject>
<b067>20</b067>

<b070>�gyptologie</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Kunstgeschichte</b070>
</subject>

<subject>
<b067>20</b067>

<b070>�gypten /Kunst</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Plastik</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Amarnazeit</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Statue</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Naossistren</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Sistrophoren</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Theophoren</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Neues Reich</b070>
</subject>

<subject>
<b067>30</b067>

<b069>700</b069>
</subject>

<subject>
<b067>30</b067>

<b069>930</b069>
</subject>

<b073>06</b073>

<othertext>
<d102>03</d102>

<d103>00</d103>

<d104>Dieses Buch �ber die �Innovationen in der alt�gyptischen Privatplastik� besch�ftigt sich schwerpunktm��ig mit der 18. Dynastie, einem sehr lebendigen und turbulenten Abschnitt der alt�gyptischen Geschichte bis zu ihrem revolution�ren H�hepunkt in der Amarnazeit. Dar�berhinaus wird ein Gesamt�berblick �ber die Entwicklung der Privatplastik als Spiegelbild der jeweiligen Zeit gegeben. Die 18. Dynastie (1539�1293 v.Chr.) verdeutlicht einen gro�en Wandel im Selbstverst�ndnis der Menschen, denn sie lassen sich jetzt in der Funktion als Erzieher oder als Beteiligter am Kult darstellen. Dies zeigt sich anhand vieler neuer Statuentypen, wie den Erzieherstatuen, Theophoren, Sistrophoren oder Opferplattentr�gern, also Statuen, die vor sich K�nigskinder, G�tterfiguren, Naossistren oder Opferplatten halten oder ber�hren. In einem methodischen Teil werden verschiedene Aspekte der Statuenterminologie angesprochen und Vorschl�ge zur computertechnischen Auswertung gemacht.</d104>
</othertext>

<publisher>
<b291>01</b291>

<b241>05</b241>

<b243>5107530</b243>

<b081>Harrassowitz, O</b081>
</publisher>

<b003>200909</b003>

<measure>
<c093>01</c093>

<c094>240</c094>

<c095>mm</c095>
</measure>

<measure>
<c093>02</c093>

<c094>170</c094>

<c095>mm</c095>
</measure>

<supplydetail>
<supplieridentifier>
<j345>05</j345>

<b244>5107530</b244>
</supplieridentifier>

<j137>Harrassowitz, O</j137>

<j292>00</j292>

<j141>NP</j141>

<j396>10</j396>

<price>
<j148>04</j148>

<j266>01</j266>

<j151>48.00</j151>

<j152>EUR</j152>

<b251>DE</b251>

<j153>R</j153>
</price>

<price>
<j148>02</j148>

<j266>01</j266>

<j151>83.00</j151>

<j152>CHF</j152>

<b251>CH</b251>

<j153>R</j153>
</price>
</supplydetail>
</product>

<product datestamp="20090211">
<a001>3166704</a001>

<a002>03</a002>

<productidentifier>
<b221>02</b221>

<b244>344705736X</b244>
</productidentifier>

<productidentifier>
<b221>03</b221>

<b244>9783447057363</b244>
</productidentifier>

<productidentifier>
<b221>15</b221>

<b244>9783447057363</b244>
</productidentifier>

<b012>BC</b012>

<b014>PB</b014>

<series>
<seriesidentifier>
<b273>04</b273>

<b244>RW269</b244>
</seriesidentifier>

<title>
<b202>01</b202>

<b203>Studien der Forschungsstelle Ostmitteleuropa an der Universit�t Dortmund</b203>
</title>

<b019>40</b019>
</series>

<title>
<b202>01</b202>

<b203>Wahrnehmungen des Terrors</b203>

<b029>Berichte aus Sowjetrussland und der Sowjetunion zwischen 1918 und 1938</b029>
</title>

<contributor>
<b034>1</b034>

<b035>A01</b035>

<b340>1</b340>

<b036>Wolfgang Geier</b036>

<b037>Geier, Wolfgang</b037>

<b039>Wolfgang</b039>

<b040>Geier</b040>
</contributor>

<b057>1</b057>

<b058>1., Aufl.</b058>

<language>
<b253>01</b253>

<b252>ger</b252>
</language>

<b254>XII</b254>

<b255>168</b255>

<mainsubject>
<b191>26</b191>

<b068>2.0</b068>

<b069>1556</b069>

<b070>Hardcover, Softcover / Geschichte/20. Jahrhundert (bis 1945)</b070>
</mainsubject>

<subject>
<b067>30</b067>

<b069>63</b069>
</subject>

<subject>
<b067>20</b067>

<b070>Russland /Geschichte</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Sowjetunion /Geschichte</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Bolschewismus</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Moskauer Prozesse</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Repression</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Terror</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Gro�e S�uberung</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Stalin</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Stalinsche S�uberung</b070>
</subject>

<subject>
<b067>30</b067>

<b069>940</b069>
</subject>

<subject>
<b067>30</b067>

<b069>950</b069>
</subject>

<b073>06</b073>

<othertext>
<d102>01</d102>

<d103>00</d103>

<d104>Zwischen den Weltkriegen besuchten hunderte Intellektuelle, Wissenschaftler, Schriftsteller, andere K�nstler, Publizisten, Journalisten, Vertreter politischer, staatlicher, gesellschaftlicher, kultureller Institutionen und Organisationen Sowjetrussland beziehungsweise die Sowjetunion. Ihre Aufenthalte fanden bereits w�hrend bolschewistischen und setzen sich w�hrend des Stalin Terrors zwischen 1929 und 1939 mit dem Tiefpunkt der Moskauer Prozesse 1936 bis 1939 fort. Diese Besucher hinterlie�en Berichte unterschiedlicher Art mit ihren Eindr�cken, Erlebnissen und Erfahrungen.</d104>
</othertext>

<publisher>
<b291>01</b291>

<b241>05</b241>

<b243>5107530</b243>

<b081>Harrassowitz, O</b081>
</publisher>

<b003>200902</b003>

<measure>
<c093>01</c093>

<c094>240</c094>

<c095>mm</c095>
</measure>

<measure>
<c093>02</c093>

<c094>170</c094>

<c095>mm</c095>
</measure>

<measure>
<c093>08</c093>

<c094>360</c094>

<c095>gr</c095>
</measure>

<supplydetail>
<supplieridentifier>
<j345>05</j345>

<b244>5107530</b244>
</supplieridentifier>

<j137>Harrassowitz, O</j137>

<j292>00</j292>

<j141>IP</j141>

<j396>20</j396>

<price>
<j148>04</j148>

<j266>02</j266>

<j151>48.00</j151>

<j152>EUR</j152>

<b251>DE</b251>

<j153>R</j153>
</price>

<price>
<j148>02</j148>

<j266>02</j266>

<j151>83.00</j151>

<j152>CHF</j152>

<b251>CH</b251>

<j153>R</j153>
</price>
</supplydetail>
</product>

<product datestamp="20081117">
<a001>3132857</a001>

<a002>03</a002>

<productidentifier>
<b221>02</b221>

<b244>344705798X</b244>
</productidentifier>

<productidentifier>
<b221>03</b221>

<b244>9783447057981</b244>
</productidentifier>

<productidentifier>
<b221>15</b221>

<b244>9783447057981</b244>
</productidentifier>

<b012>BB</b012>

<b014>GB</b014>

<series>
<seriesidentifier>
<b273>04</b273>

<b244>TD405</b244>
</seriesidentifier>

<title>
<b202>01</b202>

<b203>Diskurse der Arabistik</b203>
</title>

<b019>15</b019>
</series>

<title>
<b202>01</b202>

<b203>Das poetische Werk des Abu Sahr al-Hudali</b203>

<b029>Eine literaturanthropologische Studie</b029>
</title>

<contributor>
<b034>1</b034>

<b035>A01</b035>

<b340>1</b340>

<b036>Kirill Dmitriev</b036>

<b037>Dmitriev, Kirill</b037>

<b039>Kirill</b039>

<b040>Dmitriev</b040>
</contributor>

<b057>1</b057>

<b058>1., Aufl.</b058>

<language>
<b253>01</b253>

<b252>ger</b252>
</language>

<b255>352</b255>

<mainsubject>
<b191>26</b191>

<b068>2.0</b068>

<b069>1569</b069>

<b070>Hardcover, Softcover / Sprachwissenschaft, Literaturwissenschaft/Sonstige Sprachen, Sonstige Literaturen</b070>
</mainsubject>

<subject>
<b067>30</b067>

<b069>58</b069>
</subject>

<subject>
<b067>20</b067>

<b070>Altarabische Poesie</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Nasib</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Arabien /Literaturwissenschaft</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Gazal</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Liebesdichtung</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Arabische Poesie</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Arabische Liebesdichtung</b070>
</subject>

<subject>
<b067>20</b067>

<b070>Fr�hislamische Kultur</b070>
</subject>

<subject>
<b067>30</b067>

<b069>890</b069>
</subject>

<publisher>
<b291>01</b291>

<b241>05</b241>

<b243>5107530</b243>

<b081>Harrassowitz, O</b081>
</publisher>

<b003>200902</b003>

<measure>
<c093>01</c093>

<c094>240</c094>

<c095>mm</c095>
</measure>

<measure>
<c093>02</c093>

<c094>170</c094>

<c095>mm</c095>
</measure>

<supplydetail>
<supplieridentifier>
<j345>05</j345>

<b244>5107530</b244>
</supplieridentifier>

<j137>Harrassowitz, O</j137>

<j292>00</j292>

<j141>NP</j141>

<j396>10</j396>

<price>
<j148>04</j148>

<j266>01</j266>

<j151>74.00</j151>

<j152>EUR</j152>

<b251>DE</b251>

<j153>R</j153>
</price>

<price>
<j148>02</j148>

<j266>01</j266>

<j151>125.00</j151>

<j152>CHF</j152>

<b251>CH</b251>

<j153>R</j153>
</price>
</supplydetail>
</product>
</ONIXmessage>


My XML routine which I have used for other publishers XML files expect to find the product node starting with '<product>' and with no 'attributes'.

So I need to replace the attributed open Product tag with... '<product>'

So, in summary, I want to have:

<product><node>blah blah</node</product> instead of:

<product datestamp="********"><node>blah blah</node></product>
I hope I am making myself clear enough.

Thanks again

PS - Sorry about horizontal scrolling, I couldn't work out how to stop it
CreganTur
03-11-2009, 07:42 AM
PS - Sorry about horizontal scrolling, I couldn't work out how to stop it

use line breaks on your long XML strings. That'll keep it from spreading so much.

The success of this will depend on getting the wildcard characters to work with the Replace method. I'll try a few things and get back to you.
hansup
03-11-2009, 08:23 AM
So, in summary, I want to have:

<product><node>blah blah</node</product> instead of:

<product datestamp="********"><node>blah blah</node></product>

See if you can use my RegExpReplace function to fit your need.

You will need to set a reference in your project:
1. In the code editor, select menu item Tools > References
2. Check item Microsoft VBScript Regular Expressions.

Public Function RegExpReplace(ByVal strSource As String, _
ByVal strPattern As String, _
ByVal strReplace As String) As String
'-------------------------------------------------------------------------------
' Purpose : replace [strPattern] with [strReplace] in [strSource]
' Comment : [strPattern] can be plain text or a regexp pattern;
' all occurences of [strPattern] are replaced
'-------------------------------------------------------------------------------
'requires reference to Microsoft VBScript Regular Expressions
Dim re As RegExp

Set re = New RegExp
re.Global = True
re.Pattern = strPattern
RegExpReplace = re.Replace(strSource, strReplace)
Set re = Nothing
End Function
I loaded a string variable with your XML text, then used it with the function like this:
strXmlAfter = RegExpReplace(strXmlBefore, "product datestamp[^>]*", "product")
Debug.Print strXmlAfter Best I can tell, strXmlAfter contains the transformed XML you want.

Good luck,
Hans
denon
03-11-2009, 11:41 AM
Hi Hans,

I'll have to wait until I get back to work tomorrow to test it but it looks promising.


strXmlAfter = RegExpReplace(strXmlBefore, "product datestamp[^>]*", "product")


Excuse my ignorance but could you please explain what the [^>]* signifies. Thanks.

I'll post a reply with the results.

Thanks for your help both of you.

:)


use line breaks on your long XML strings. That'll keep it from spreading so much. Formatted the XML but still some scrolling - oh well, hopefully this thread is almost done with...............!
hansup
03-11-2009, 01:40 PM
Excuse my ignorance but could you please explain what the [^>]*Regular expressions use square brackets to designate a "character range". As an example, the range [0-9] would match a single digit at that position in the search string. In our case, the "range" consists of a single character, ">". The "^" symbol as the first character after the opening bracket is not a member of the character range we're intending to match. Instead, it kind of "reverses" the meaning --- our match will include any character NOT in the range. Finally, the "*" means match the preceding character (range) zero or more times.

So, in our case, "product datestamp[^>]*", we are saying "Match character sequences which start with 'product datestamp' and include any following characters other than '>'."

I hope that explanation made sense. If not, let me know and I'll try again. Regular expressions can be incredibly useful, though they tend to resemble gibberish at first. I can handle the simpler expression reasonably well. But complex expressions make my head explode.

Good luck,
Hans
denon
03-12-2009, 03:21 AM
:clap:

It works perfectly, thanks a lot.

The explanation was also very useful. I can imagine there will be some other occasions where regular expressions will come in handy as well. - I'll have to do some reading up on them.

Thanks Hans (and Randy)

:thumb
hansup
03-12-2009, 07:53 AM
It works perfectly, thanks a lot.

The explanation was also very useful. I can imagine there will be some other occasions where regular expressions will come in handy as well. - I'll have to do some reading up on them. You're welcome, denon. I'm glad to hear it worked for you. I would revise the call to:
strXmlAfter = RegExpReplace(strXmlBefore, "<product datestamp[^>]*>", "<product>") That change should make it more selective, by not replacing "product datestamp<whatever>" anywhere in the string other than the element node tag.

Good luck,
Hans