Help with modify XML Data

Xsi · May 29, 2015

Hello people I need to a code that makes following things:

I have got this XML code,

I had help making to create line breaks in the file. I want following:, if you look, it's several sections, starting tag and end

that can be x number of <file> * </ file> x times. the modified tags will be inside of and tags: example in the file I sent: (it can be any line but the structure is always the same)

line 11:

<inContextExact segments="60" words="55" characters="755" placeables="3" tags="0" />
replace 55 with 0

result:

<inContextExact segments="60" words="0" characters="755" placeables="3" tags="0" />
next tag is:

line:14

<crossFileRepeated segments="2" words="20" characters="0" placeables="0" tags="0" />
Cut two words "20" and replace with 0

result:

line:14<crossFileRepeated segments="2" words="0" characters="0" placeables="0" tags="0" />

add the value 20 to the tag words="34"

line 15:

<repeated segments="17" words="34" characters="293" placeables="2" tags="0" />
result:

line 15:

<repeated segments="17" words="54" characters="293" placeables="2" tags="0" />
Here is my code I got so far. (VBA) if you want to rewrite the code in any other lang is it OK! as long the result is fine.
(the only thing my VBA code do its look for >< replace with >\n\r<) because the XML file is 1 line.

Sub XMLSolve()
Dim FSO As Object '//FileSystemObject
Dim ts(1) As Object '//TextStream
Dim s As String, t As String
Dim FileContents As String

'---------------------------------------------------------
Const SEARCH_FOR As String = "><"
Const REPLACE_WITH As String = ">" & vbCrLf & "<"
'----------------------------------------------

s = "<123><456><789>"
s = Replace(s, SEARCH_FOR, REPLACE_WITH)

On Error GoTo ErrHandler:
s = Application.GetOpenFilename()
If s <> "False" Then
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(s) Then

'//Get File Contents
t = FSO.GetParentFolderName(s) & "\" & Replace(FSO.GetTempName(), ".tmp", ".xml")
Name s As t
Set ts(0) = FSO.OpenTextFile(t, 1, False, -2) '//For reading, use default encoding
FileContents = ts(0).ReadAll
ts(0).Close
Set ts(0) = Nothing

'//Make replacement
FileContents = Replace(FileContents, SEARCH_FOR, REPLACE_WITH)

MsgBox

'//Write new file contents
Set ts(1) = FSO.OpenTextFile(s, 2, True, -2) '//For writing, use default encoding
ts(1).Write (FileContents)
ts(1).Close
Set ts(1) = Nothing

'//Delete Temp file if all actions succeeded
FSO.DeleteFile (t)

End If
End If

'//Check that all files are closed
My_Exit:
If Not ts(0) Is Nothing Then
ts(0).Close
End If
If Not ts(1) Is Nothing Then
ts(1).Close
End If
Set FSO = Nothing
Exit Sub

ErrHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume My_Exit
End Sub

Here is the XML file:

<task name="analyse">
<taskInfo taskId="21a09311-ade3-4e9a-af21-d13be8b7ba45" runAt="2015-05-20 13:48:50" runTime="5 minutes, 53 seconds">
<project name="13955 - HMI Volvo Truck PA15" number="e20d51c0-71dc-4572-8f9b-4c150bf35222" />
<language lcid="1031" name="German (Germany)" />
<tm name="ENG-DEU_en-GB_de-DE.sdltm" />
<settings reportInternalFuzzyLeverage="yes" reportLockedSegments="no" reportCrossFileRepetitions="yes" minimumMatchScore="70" searchMode="bestWins" missingFormattingPenalty="1" differentFormattingPenalty="1" multipleTranslationsPenalty="1" autoLocalizationPenalty="0" textReplacementPenalty="0" />
</taskInfo>
<file name="VT MAIN TRACK_PA15_Default_DE-DE_20150520_102527.xlf.sdlxliff" guid="111f9ba6-82f6-45fb-ac49-8bf6cf57c169">
<analyse>
<perfect segments="0" words="0" characters="0" placeables="0" tags="0" />
<inContextExact segments="60" words="55" characters="755" placeables="3" tags="0" /> ' Replace the Value word="55" with "0"
<exact segments="114" words="334" characters="1687" placeables="14" tags="3" />
<locked segments="0" words="0" characters="0" placeables="0" tags="0" />
<crossFileRepeated segments="2" words="20" characters="0" placeables="0" tags="0" /> 'Cut the value words="20" replace with 0
<repeated segments="17" words="34" characters="293" placeables="2" tags="0" /> 'add the value to current value 20 to 34 so the new value is words="54"
<total segments="449" words="1462" characters="7630" placeables="66" tags="24" />
<new segments="126" words="434" characters="2384" placeables="18" tags="5" />
<fuzzy min="75" max="84" segments="25" words="108" characters="528" placeables="6" tags="3" />
<fuzzy min="85" max="94" segments="23" words="92" characters="454" placeables="7" tags="4" />
<fuzzy min="95" max="99" segments="77" words="260" characters="1318" placeables="13" tags="6" />
<internalFuzzy min="75" max="84" segments="3" words="16" characters="100" placeables="2" tags="2" />
<internalFuzzy min="85" max="94" segments="4" words="25" characters="111" placeables="1" tags="1" />
<internalFuzzy min="95" max="99" segments="0" words="0" characters="0" placeables="0" tags="0" />
</analyse>
</file>
<file name="VT MAIN TRACK_PA15_Default_DE-DE_20150523_254796.xlf.sdlxliff" guid="111f9ba6-82f6-45fb-ac49-8bf6cf57c169">
<analyse>
<perfect segments="0" words="0" characters="0" placeables="0" tags="0" />
<inContextExact segments="60" words="67" characters="755" placeables="3" tags="0" /> ' Replace the Value word="67" with "0"
<exact segments="114" words="334" characters="1687" placeables="14" tags="3" />
<locked segments="0" words="0" characters="0" placeables="0" tags="0" />
<crossFileRepeated segments="2" words="35" characters="0" placeables="0" tags="0" /> 'Cut the value words="35" replace with 0
<repeated segments="17" words="54" characters="293" placeables="2" tags="0" /> 'add the value to current value 35 to 54 so the new value is words="89"
<total segments="449" words="1462" characters="7630" placeables="66" tags="24" />
<new segments="126" words="434" characters="2384" placeables="18" tags="5" />
<fuzzy min="75" max="84" segments="25" words="108" characters="528" placeables="6" tags="3" />
<fuzzy min="85" max="94" segments="23" words="92" characters="454" placeables="7" tags="4" />
<fuzzy min="95" max="99" segments="77" words="260" characters="1318" placeables="13" tags="6" />
<internalFuzzy min="75" max="84" segments="3" words="16" characters="100" placeables="2" tags="2" />
<internalFuzzy min="85" max="94" segments="4" words="25" characters="111" placeables="1" tags="1" />
<internalFuzzy min="95" max="99" segments="0" words="0" characters="0" placeables="0" tags="0" />
</analyse>
</file>
<batchTotal>
<analyse>
<perfect segments="0" words="0" characters="0" placeables="0" tags="0" />
<inContextExact segments="60" words="139" characters="755" placeables="3" tags="0" />
<exact segments="114" words="334" characters="1687" placeables="14" tags="3" />
<locked segments="0" words="0" characters="0" placeables="0" tags="0" />
<crossFileRepeated segments="0" words="0" characters="0" placeables="0" tags="0" />
<repeated segments="17" words="54" characters="293" placeables="2" tags="0" />
<total segments="449" words="1462" characters="7630" placeables="66" tags="24" />
<new segments="126" words="434" characters="2384" placeables="18" tags="5" />
<fuzzy min="75" max="84" segments="25" words="108" characters="528" placeables="6" tags="3" />
<fuzzy min="85" max="94" segments="23" words="92" characters="454" placeables="7" tags="4" />
<fuzzy min="95" max="99" segments="77" words="260" characters="1318" placeables="13" tags="6" />
<internalFuzzy min="75" max="84" segments="3" words="16" characters="100" placeables="2" tags="2" />
<internalFuzzy min="85" max="94" segments="4" words="25" characters="111" placeables="1" tags="1" />
<internalFuzzy min="95" max="99" segments="0" words="0" characters="0" placeables="0" tags="0" />
</analyse>
</batchTotal>
</task>

Could someone help me with a batchscript /script

Thank you in advance

MakeItSo · May 29, 2015

You still didn't tell what exactly is your problem, what do you want to achieve?

Please describe exactly what it is you want to achieve. If I were you, I wouldn't try to parse this as a text file - too much pain.
Use XmlDOMDocument60 instead.

BTW: If you you don't terribly mind switching to VB.Net then you might think about joining the SDL Developer Community. There you can get the SDKs amongst which is the Project Automation API, which has a AnalysisStatistics class that will give you access to all data from the analysis.
;-)

Cheers,
MakeItSo

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

Xsi · May 29, 2015

Well Sorry If I wasn't clear enough but if you look on the XML a part of the file:

here is the Example original:

<file name="VT MAIN TRACK_PA15_Default_DE-DE_20150520_102527.xlf.sdlxliff" guid="111f9ba6-82f6-45fb-ac49-8bf6cf57c169">
<analyse>
<perfect segments="0" words="0" characters="0" placeables="0" tags="0" />
<inContextExact segments="60" words="55" characters="755" placeables="3" tags="0" /> ' Replace the Value word="55" with "0"
<exact segments="114" words="334" characters="1687" placeables="14" tags="3" />
<locked segments="0" words="0" characters="0" placeables="0" tags="0" />
<crossFileRepeated segments="2" words="20" characters="0" placeables="0" tags="0" /> 'Cut the value words="20" replace with 0
<repeated segments="17" words="34" characters="293" placeables="2" tags="0" /> 'add the value to current value 20 to 34 so the new value is words="54"
<total segments="449" words="1462" characters="7630" placeables="66" tags="24" />
<new segments="126" words="434" characters="2384" placeables="18" tags="5" />
<fuzzy min="75" max="84" segments="25" words="108" characters="528" placeables="6" tags="3" />
<fuzzy min="85" max="94" segments="23" words="92" characters="454" placeables="7" tags="4" />
<fuzzy min="95" max="99" segments="77" words="260" characters="1318" placeables="13" tags="6" />
<internalFuzzy min="75" max="84" segments="3" words="16" characters="100" placeables="2" tags="2" />
<internalFuzzy min="85" max="94" segments="4" words="25" characters="111" placeables="1" tags="1" />
<internalFuzzy min="95" max="99" segments="0" words="0" characters="0" placeables="0" tags="0" />
</analyse>
</file>

here is result I want

<file name="VT MAIN TRACK_PA15_Default_DE-DE_20150520_102527.xlf.sdlxliff" guid="111f9ba6-82f6-45fb-ac49-8bf6cf57c169">
<analyse>
<perfect segments="0" words="0" characters="0" placeables="0" tags="0" />
<inContextExact segments="60" words="0" characters="755" placeables="3" tags="0" />
<exact segments="114" words="334" characters="1687" placeables="14" tags="3" />
<locked segments="0" words="0" characters="0" placeables="0" tags="0" />
<crossFileRepeated segments="2" words="[highlight #4E9A06]0[/highlight]" characters="0" placeables="0" tags="0" />
<repeated segments="17" words="54" characters="293" placeables="2" tags="0" />
<total segments="449" words="1462" characters="7630" placeables="66" tags="24" />
<new segments="126" words="434" characters="2384" placeables="18" tags="5" />
<fuzzy min="75" max="84" segments="25" words="108" characters="528" placeables="6" tags="3" />
<fuzzy min="85" max="94" segments="23" words="92" characters="454" placeables="7" tags="4" />
<fuzzy min="95" max="99" segments="77" words="260" characters="1318" placeables="13" tags="6" />
<internalFuzzy min="75" max="84" segments="3" words="16" characters="100" placeables="2" tags="2" />
<internalFuzzy min="85" max="94" segments="4" words="25" characters="111" placeables="1" tags="1" />
<internalFuzzy min="95" max="99" segments="0" words="0" characters="0" placeables="0" tags="0" />
</analyse>
</file>

MakeItSo · May 29, 2015

OK, just to clarify:
You want to set all inContextExact word values to 0, and summarize crossFileRepeated words and repated words in the repeated node and set crossFileRepeated also to 0?

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

Xsi · May 29, 2015

I want to:
<inContextExact segments="60" words=" 55" characters="755" placeables="3" tags="0" />
result:
<inContextExact segments="60" words=" 0" characters="755" placeables="3" tags="0" />

and
<crossFileRepeated segments="2" words=" 20" characters="0" placeables="0" tags="0" /> 'Cut the value words=" 20" replace with 0
result:

<repeated segments="17" words=" 34" characters="293" placeables="2" tags="0" /> 'add the value to current value 20 to 34 so the new value is words=" 54"

result:
<repeated segments="17" words=" 54" characters="293" placeables="2" tags="0" /> 'add the value to current value 20 to 34 so the new value is words=" 54

MakeItSo · May 29, 2015

Hi Elmas,

look at the follwing little script, it does exactly what you want (need to replace the hardcoded test paths to the file of course):

Code:

Dim analyse
Dim exactContexts
Dim subnode
Dim repeated
Dim realRepeated
Dim tmpValue

Set analyse = CreateObject("Msxml2.DOMDocument.6.0")
analyse.Load "C:\00_Projekte_temp\analyse.xml"

Set exactContexts = analyse.SelectNodes("//inContextExact")

For i = 0 To exactContexts.Length - 1
    Set subnode = exactContexts(i)
    For Each att In subnode.Attributes
        If att.Name = "words" Then
            att.Value = "0"
            Exit For
        End If
    Next att
Next i

Set repeated = analyse.SelectNodes("//crossFileRepeated")

For i = 0 To repeated.Length - 1
    Set subnode = repeated(i)
    For Each att In subnode.Attributes
        If att.Name = "words" Then
            tmpValue = att.Value
            att.Value = "0"
            Exit For
        End If
    Next att
    
    Set realRepeated = subnode.NextSibling
    For Each att In realRepeated.Attributes
        If att.Name = "words" Then
            att.Value = Val(att.Value) + Val(tmpValue)
            Exit For
        End If
    Next att
Next i

analyse.Save "C:\00_Projekte_temp\analyse2.xml"

That OK?

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

Xsi · May 29, 2015

do I need to add any reference plugin?

strange the code generates an empty file?
Thank you in advance

MakeItSo · May 29, 2015

The above is the naked VBScript, if you run this from say VBA, you will need a reference to Microsoft Scripting Runtime.
It should work without explicit reference to Microsoft XML 6.0.

Once you give the correct path to the xml (instead of "C:\00_Projekte_temp\analyse.xml"), it should produce the correct output.

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

Xsi · May 29, 2015

Hmm I activated the references
also hardcoded my location of file
analyse.Load "C:\test\example.xml"

Microsoft XML, v6.0
Microsoft Scripting Runtime

xxxx

analyse.Save "C:\test\analyse2.xml"

but I get runtime error '424':
Object required.

when I go debug:

It marks

For Each att In realRepeated.Attributes

any ideas?

Thank you in advance

MakeItSo · May 29, 2015

Where do you run this from?
VB6? VBA? Word? Excel? Version?

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

MakeItSo · May 29, 2015

P.S: I ran that code from Word VBA and it worked fine. I have set references to Microsoft XML 6.0, Microsoft Scripting Runtime and Microsoft VBScript Regular Expressions 5.5

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

Xsi · May 29, 2015

oh jesus I run it from Excel? but is it any differences?
otherwise is it possible to make the code work in microsoft word?
same problem in Microsoft word I run microsoft word 2010 small & business

Thank you in advance

MakeItSo · May 29, 2015

Very strage!
I just pasted the code as is into Excel, no references set and it ran without problems.
Have you changed anything, added or removed any line?

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

MakeItSo · May 29, 2015

Hmmm.. Version? Operating System?
Maybe you need to install MS xml core services?

http://www.microsoft.com/en-us/download/details.aspx?id=6276

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

Xsi · May 29, 2015

Hi again

I run windows 7 Pro,
Service pack 1
I also Have installed the Microsoft Core XML Services (MSXML) 6.0 Service Pack 1
I run microsoft office 2013

Xsi · May 29, 2015

Have you got teamviewer?

Thank you in advance

MakeItSo · May 29, 2015

I think I have a hunch what is going on.

Code:

Set realRepeated = subnode.NextSibling

This is assuming that the node <repeated> always directly follows the node <crossFileRepeated>, which it does in a normal Studio analysis.
Have you changed anything about the XML? added comments or anything?

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

Xsi · May 29, 2015

I havent changed anything in the xml,

Where will I put the new code(line/place) snipet? "Set realRepeated = subnode.NextSibling "

thank you in advance

Xsi · May 29, 2015

It actually works now! Awesome!!!

how do I make the code adds a dialogwindow and I select a folder then the script takes the whole folder and loop through the xml files?

thank you in advance

MakeItSo · May 29, 2015

Not possible in VBA (at least not without breaking ones neck about it).
VBA User forms do not have the folder and file browser controls available.

In VBA I usually simply use an inputbox and have the user simply paste the path there. Or save it in a Word .doc or .docm.
If you open that .doc(m) from the root folder you wish to process, you could use "ActiveDocument.Path".

Do you have VB6 or VB.Net Express available? You could then create a full standalone .exe.

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Help with modify XML Data

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor