Find text and replace value next to it

[Lanier100]

Hello All...I need some help with a VBSCRIPT. I have 100's of XML files and I need the vbscript to search for a tag and update partial contents within the tag. Example below:

<address>1 Main St</address>
<startDateTime>2011-01-06-13:00:00</startDateTime>
<endDateTime>2011-01-06-17:00:00</endDateTime>

I want the program to go through the XML file and replace the date between the tag with a variable date (dtDate - which represents current date), while leaving the time alone. So it would change 2011-01-06 to 2011-01-07. When I run the VBScript, the values in the tags will differ so I cannot run the search / replace command. That is why I think just using the start tag of <startDateTime> should be what the program uses to find the starting point.

I have the starting of the script below - any help would be a huge help and greatly appreciated!!


Const ForReading = 1
Const ForWriting = 2

strYr = Year(date)
strMth = Month(date)
strDay = Day(date)

if len(strYr) < 4 then
strYr = "20" & Year(date)
end if

if len(strMth) < 2 then
strMth = "0" & Month(date)
end if

if len(strDay) < 2 then
strDay = "0" & Day(date)
end if

dtDateE = strYr & "-" & strMth & "-" & strDay

msgbox dtDateE


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\temp\Test\a.xml", ForReading)


strText = objFile.ReadAll
objFile.Close

' HERE IS WHERE THE CODE SHOULD GO

Set objFile = objFSO.OpenTextFile("C:\temp\test\a.xml", ForWriting)
objFile.WriteLine strNewText
objFile.Close

 

[wvdba]

this is not a good statement:

if len(strYr) < 4 then    
    strYr = "20" & Year(date)
end if

so, if the year is 95 or 87, you assume it's 2087? or 2095?
this may lead to ambiguity. if you're talking about birth dates, and somebody enters 09, does that mean (s)he was born in 1909 or 2009? you may have to force the user to enter all 4 digits. this is one of the problem that led to the (in)famous Y2k deal.

 

[Lanier100]

All dates will be in the year 20xx.

Thanks!!

Rob

 

[wvdba]

looking at this:
<startDateTime>2011-01-06-13:00:00</startDateTime>
and
<endDateTime>2011-01-06-17:00:00</endDateTime>
it seems that the dates are always after strings <StartDateTime> and <EndDateTime>
let's take the first one (start date time):

if left(str,15) = "<startDateTime>" then 
   tweak_string = mid(str, 16, 35)
   date_parts = split(tweak_string, "-")
   yr = date_parts(0)
   mo = date_parts(1)
   dy = date_parts(2)
end if 
new_date = yr & "-" & mo & "-" & day(now())

 

[Lanier100]

Hi wvdba,

That is not working unfortunately. I think it is because my XML files are not formated and the tags all run together like one big single line / string.

<serviceArea>EAST-MTR</serviceArea></location><appointmentInformation><appointment>Y</appointment></appointmentInformation><timeWindows><timeWindowsList><startDateTime>2011-01-06-07.00.00</startDateTime><endDateTime>2011-01-06-12.00.00</endDateTime><timeWindowUsage>M1EF</timeWindowUsage>

Any other ideas on how we could make it work with the above example of the xml file? What do you think?

Thanks!!

 

[wvdba]

how is this xml file processed normally?
why not process the xml file normally, extract the date and replace it with now()?

 

[wvdba]

ok. you can try this one:
it will find the start position of <startDateTime> and start extracting 20 characters from there.

start_pos = InStr(str,"<startDateTime>") + 15
tweak_string = mid(str, start_pos, 20)   
date_parts = split(tweak_string, "-")   
yr = date_parts(0)   
mo = date_parts(1)   
dy = date_parts(2)
new_date = yr & "-" & mo & "-" & day(now())

 

[macropod]

Hi Lanier100,

This looks like a job for a RegEx Find/Replace, where:
Find = (\>)([12][90][0-9]{2}-[0-9]{2}-[0-9]{2})(-[0-9:]{8}\<)
Replace = \1 & Format(Date(),"YYYY-MM-DD") & \3
I'm not proficient enough with RegEx to code the lot, though (I could do this easily in Word with a wildcard Find/Replace).

Cheers
Paul Edstein
[MS MVP - Word]

 

[PHV]

What about this ?

Const ForReading = 1
Const ForWriting = 2
dtDateE = Year(Date) & "-" & Right("0" & Month(Date), 2) & "-" & Right("0" & Day(Date), 2)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\temp\Test\a.xml", ForReading)
strText = objFile.ReadAll
objFile.Close
arrTag = Array("<startDateTime>", "<endDateTime>")
For Each strTag In arrTag
  intPos = InStr(strText, strTag)
  strText = Left(strText, intPos + Len(strTag) - 1) & dtDateE & Mid(strText, intPos + Len(strTag) + 10)
Next
Set objFile = objFSO.OpenTextFile("C:\temp\test\a.xml", ForWriting)
objFile.WriteLine strText
objFile.Close

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lanier100]

Hey Guys,
Thanks for the help...I got it working with your help!! Much appreciated!!

start_pos = (InStrRev(strContents,"<startDateTime>"))
tweak_string = mid(strContents, start_pos, 25)
	date_parts = split(tweak_string, "-")
		yr = date_parts(0)
		mo = date_parts(1)
		dy = date_parts(2)
	if len(day(now)) <2 then
		newDay="0" & day(now)
		Else newDay=day(now)
	end if
new_date = yr & "-" & mo & "-" & newDay
StrNewContents = Replace(strContents,tweak_string,new_date)

Thanks for the help all!!

 

[PHV]

So, I presume you'll back here on 2011-02-01 ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tsuji]

I can only add this note to the approach. Using fso to process the xml file is fundamentally, or put it more mildly, is going to suffer with mixed success, since xml has internationalization built into its foundation. Processing it as "text" file with fso can result in data distortion. But as a quick fix to specific kind of xml document containing basically ascii characters using fso... why not!

 

[wvdba]

phv is right.
your interpretation of the date/day is not going to work. example: on 2011-02-01 when your <start_date> is 2011-01-31 you can't take day 01 and plug it into the string. you will end up with 2011-01-01!? - think!
you have to take the whole string and replace with "now()" components.