Extract a value from an xml file and output it to csv using a batch file

Question

I need to extract the Value from a specific xml tag in all the xml files in a folder and export the value and file name w/o ext in 2 separate columns in a csv file. I have tried the follow with zero luck.

The xml looks like this:

<ICSMXML xmlns="http://www.icsm.com/icsmxml" version="1.0">
<Header>
<MsgDelivery>
  <To>
    <Credential>
      <Domain>ICSMID</Domain>
      <Identity>11</Identity>
    </Credential>
  </To>
  <From>
    <Credential>
      <Domain>DUNS</Domain>
      <Identity>039576814</Identity>
    </Credential>
  </From>
  <Sender>
    <Credential>
      <Domain>DUNS</Domain>
      <Identity>039576814</Identity>
    </Credential>
    <UserAgent />
  </Sender>
</MsgDelivery>
<MsgHeader>
  <MessageId>10000095713</MessageId>
  <Timestamp>04/12/2013 10:24:00 AM</Timestamp>

I need to parse the value from MessageId in any xml file found in the folder, and out put that in to an csv file along with the original file name w/o ext. preferably have the value in column 1 and the file name w.o ext in column 2.

@echo off
call :check_lines < %1 > "%~N1.xml"
exit /b

REM Seek for the start of Data tag
:check_lines
set /P line=
if not "%line%" == "<MessageId>" goto check_lines

REM Copy until the end of Data tag
set /P line=
:put_lines
if "%line%" == "</MessageId>" goto end_lines
set /P line=%line% 
goto put_lines
:end_lines
echo %line%
>>Message.csv

Matt Williamson · Accepted Answer · 2013-12-05 19:17:20Z

1

This should do it:

@echo off
setlocal enabledelayedexpansion
for %%a in (*.xml) do (
call :XMLExtract "%%a" "<MessageID>" location
echo.!location!,%%~na
)
exit /b

:XMLExtract file keystart location
@echo off & setlocal
for /f "tokens=3 delims=<>" %%a in ('Findstr /i /c:%2 "%~1"') do (
   set "loc=%%a" & goto :endloop
)
:endLoop
ENDLOCAL & IF "%~3" NEQ "" (SET %~3=%loc%) ELSE echo.%loc%
exit /b

edited Dec 5, 2013 at 19:17

answered Dec 5, 2013 at 17:52

Matt Williamson

7,1691 gold badge27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2522219 Over a year ago

I ran the code and wasn't able to produce the csv, I moved the >>Message.csv just after the closing ) and received the csv but was empty.

Matt Williamson Over a year ago

You need to leave that part inside of the loop. Otherwise, it won't write it out for each file. Did you try it exactly as I wrote it?

user2522219 Over a year ago

i copied the code again, just to double check and make sure it was the same and didn't get the message.csv.

Matt Williamson Over a year ago

Ahh, it's all on the same line. This code is designed to extract tags on seperate lines. I'll have to re-work it.

user2522219 Over a year ago

Once I added in the >>Message.csv it works like a charm, thank you for the help, much appreciated!!

Collectives™ on Stack Overflow

Extract a value from an xml file and output it to csv using a batch file

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related