Read up from the bottom of a file until a particular tag is found.

[Matt Williamson]

I have an unformatted xml file that I need to read from the bottom up searching for a particular tag. Once I find that tag, I need to read it from that string down extracting a few things. Here is a very small extract of the XML. I had to format it, I couldn't stand working with it that way.

Code: Select all

<Proponix>
  <Header>
    <DestinationID>ABC</DestinationID>
    <SenderID>PRO</SenderID>
    <ClientBank>ABC</ClientBank>
    <OperationOrganizationID>BLT1</OperationOrganizationID>
    <MessageType>BALMIS</MessageType>
    <DateSent>20151020</DateSent>
    <TimeSent>170633</TimeSent>
    <MessageID>1077169400</MessageID>
  </Header>
  <SubHeader>
    <InstrumentID>ID10001</InstrumentID>
  </SubHeader>
</Proponix>

So, in this scenario, I want to start at the bottom of the file and work back until I find the first <Proponix> tag. Then, I want to extract <DateSent>,<TimeSent>,<MessageID> and <InstrumentID>

I've been working in Powershell so much that my brain hurts. This should be relatively simple but I've got nothing yet.

Thanks,

Matt

[Aacini]

Well, everyone will say you that Batch is not the right tool for this task, and that Batch code will fail if the format of the file changes...

Anyway, here it is:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

set "Proponix="
set "fields=DateSent TimeSent MessageID InstrumentID"
for /F "tokens=1-3 delims=<>" %%a in ('findstr "Proponix %fields%" ^< input.txt') do (
   if defined Proponix (
      set "%%b=%%c"
      if /I "%%b" equ "InstrumentID" set "Proponix="
   ) else if /I "%%a" equ "Proponix" (
      set "Proponix=1"
   )
)

rem Show the values of same-name variables
for %%a in (%fields%) do echo %%a= !%%a!

Output:

Code: Select all

DateSent= 20151020
TimeSent= 170633
MessageID= 1077169400
InstrumentID= ID10001

Antonio

[Matt Williamson]

Thanks Antonio. This is great code and works perfectly with the subset of data I gave but the file is much bigger and findstr is balking on me.

FINDSTR: Line 16 is too long.
FINDSTR: Line 22 is too long.
FINDSTR: Line 27 is too long.
FINDSTR: Line 28 is too long.
FINDSTR: Line 113 is too long.
FINDSTR: Line 127 is too long.
FINDSTR: Line 129 is too long.
FINDSTR: Line 131 is too long.
FINDSTR: Line 227 is too long.
FINDSTR: Line 274 is too long.
FINDSTR: Line 277 is too long.
FINDSTR: Line 340 is too long.
FINDSTR: Line 349 is too long.
FINDSTR: Line 355 is too long.
FINDSTR: Line 374 is too long.
FINDSTR: Line 383 is too long.
FINDSTR: Line 385 is too long.
FINDSTR: Line 399 is too long.
FINDSTR: Line 409 is too long.
FINDSTR: Line 411 is too long.

[dbenham]

The line too long error only happens when FINDSTR reads the input via redirection or a pipe. That error goes away if you pass the name (path) of the file directly to FINDSTR.

But Aacini's code is dependent on the XML being formatted as you show in your original question. It will not work with unformatted XML, even if you fix the redirection issue.

If your input XML is truly unformatted, then there is a major risk that you cannot solve this with pure batch because batch is limited to reading lines with max length of ~8191 bytes.

I don't understand why you could not get it to work with powershell, given that it is designed to be able to work very well with XML.

Here is a solution that uses JREPL.BAT

The first JREPL extracts the last Proponix tag from the file, which is piped to the second JREPL that extracts all of the desired tags in "name=value" format. This output is read by FOR /F which then sets variables with the appropriate names.

Code: Select all

@echo off
setlocal

:: Define source file
set "file=test.xml"

:: Define tags of interest
set "final=Proponix"
set "tags=DateSent TimeSent MessageID InstrumentID"

:: Clear existing variables
for %%V in (%tags%) do set "%%V="

:: Extract the desired tags
for /f "delims=" %%A in (
  'jrepl "<(%final%)>(?![\s\S]*<\1>)[\s\S]*</\1>" $0 /m /jmatch /f "%file%"^
  ^| jrepl "<(%tags: =|%)>\s*([\s\S]*?\S)\s*</\1>" "$1+'='+$2" /m /jmatch'
) do set "%%A"

:: Show the results
for %%V in (%tags%) do set %%V

--OUTPUT--

Code: Select all

DateSent=20151020
TimeSent=170633
MessageID=1077169400
InstrumentID=ID10001

The code should be fairly robust for most input, but there are some XML constructs that could trip it up.

Really you should return to powershell and process the XML properly.


Dave Benham

[Matt Williamson]

Thanks Dave.

I work for an international bank and to get approval for a powershell script can take months with all of the security and testing. I can use batch script quick and easy though.

[dbenham]

Did you try the JREPL solution? I believe it should work pretty well as long as the input file does not approach 1 gigabyte.


Dave Benham

[Matt Williamson]

I did and it works great on the initial file. Thank you so much Dave! I'm just starting to use regular expressions. If you have some time, I'd love if you could explain what the regexs in this are doing. I've been playing around with it at http://www.regex101.com but I'm not exactly getting it yet. I am using the Javascript Flavor as to my understanding, JRepl is using Javascript under the 'Hood'. Also, have you ever used JRepl as a function in the same batch file? I can't figure out how to do it. Thanks again, you are truly an asset here and on SO.

I'm also having an issue getting the output to write to a file. I'm sure it's something simple but I con't figure it out. Here is the code I've come up with so far.

Code: Select all

@echo off
setlocal
set "testing=1"

if "%testing%" EQU "1" (
   set "file=F:\Scripts\batch\Parse Saved Message Testing\savedMessages.txt"
) ELSE (
   set "file=\\servername\Developer\inetpub\Ftproot\BusinessWorksProjectSupportingFiles\savedMessages.txt"
)

:: Define tags of interest
set "final=Proponix"
set "tags=DateSent TimeSent MessageID InstrumentID OriginalInstrumentID"

:: Clear existing variables
for %%V in (%tags%) do set "%%V="

:: Extract the desired tags
for /f "delims=" %%A in (
  'jrepl "<(%final%)>(?![\s\S]*<\1>)[\s\S]*</\1>" $0 /m /jmatch /f "%file%"^
  ^| jrepl "<(%tags: =|%)>\s*([\s\S]*?\S)\s*</\1>" "$1+'='+$2" /m /jmatch'
) do set "%%A"

:: Show the results
setlocal enabledelayedexpansion
for %%V in (%tags%) do (
   set %%V && echo !%%V!>>output.txt
)
endlocal
pause

The output I'm getting is:

DateSent TimeSent MessageID InstrumentID OriginalInstrumentID=20151020
DateSent TimeSent MessageID InstrumentID OriginalInstrumentID=170633
DateSent TimeSent MessageID InstrumentID OriginalInstrumentID=1077169493
DateSent TimeSent MessageID InstrumentID OriginalInstrumentID=ID1881820001

I'm sure you'll notice that I added the OriginalInstrumentID tag. This is what will be in the file if there is no InstrumentID. I left it out originally because I though I'd get some code that I could figure out easily and modify. That's not the case here though. So, if you could help me modify the regex to allow for that tag, I'd very much appreciate it. Again, Thank you so much Dave.



Matt

[Matt Williamson]

hey Dave, I'm still trying to decipher your regexes but for the moment, can you just show me how to outup the data to a file? thanks.

Never mind, I'm an idiot. It helps when you read the full instructions and not just skim over them. /O works perfectly.