Solved How do I extract certain data from a xml file to a batch?

March 26, 2017 at 22:45:15
Specs: Windows 7
Iam trying to set up a excel table, therefore I need data from different xml files. I do only need the data of 5 values which are stored in xml files. How do I extract these datas from a xml files to a batch?

Many thanks for help !

message edited by meiyoulimao


See More: How do I extract certain data from a xml file to a batch?

Report •

✔ Best Answer
April 3, 2017 at 01:00:27
could it be you have no new files in the folder?
or perhaps it really is giving you a different date than me

I think i fixed it :)


test.bat
------------------------
@echo off&setlocal EnableDelayedExpansion&set n=0&set m=0&set f=0&c=0&set d=0
cls&echo Enter the amount of variables you want to save the value of(1-9):
choice /c 123456789 /n>nul
set vars=%errorlevel%
:loop
if %n% equ %vars% for /f "tokens=3,* delims= " %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do set "file=%%B"&goto folderloop
set /a n+=1
cls&echo Enter the name of variable %n%(CAPS sensitive) ^& hit enter:
set /p "var%n%="
goto loop
:folderloop
for /f "tokens=3,* delims= " %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do set /a f+=1
:skip1
set /a c+=1
set /a d=!c!-1
if !d! equ 0 for /f "tokens=3,* delims= " %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do set "file!c!=%%B"&goto indexing
for /f "skip=%d% tokens=3,* delims= " %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do set "file!c!=%%B"&goto indexing
:indexing
if '!file%c%!'=='' echo No files found with date:%date:~-10%&timeout 2 /nobreak>nul&exit /b
set "file=!file%c%!"
set /a m+=1
set temp=!var%m%!
for /f "tokens=1-26 delims== """ %%a in ('type "%file%" ^| find "%temp%"') do (
if '%%a'=='%temp%' set val!m!=1
if '%%b'=='%temp%' set val!m!=2
if '%%c'=='%temp%' set val!m!=3
if '%%d'=='%temp%' set val!m!=4
if '%%e'=='%temp%' set val!m!=5
if '%%f'=='%temp%' set val!m!=6
if '%%g'=='%temp%' set val!m!=7
if '%%h'=='%temp%' set val!m!=8
if '%%i'=='%temp%' set val!m!=9
if '%%j'=='%temp%' set val!m!=10
if '%%k'=='%temp%' set val!m!=11
if '%%l'=='%temp%' set val!m!=12
if '%%m'=='%temp%' set val!m!=13
if '%%n'=='%temp%' set val!m!=14
if '%%o'=='%temp%' set val!m!=15
if '%%p'=='%temp%' set val!m!=16
if '%%q'=='%temp%' set val!m!=17
if '%%r'=='%temp%' set val!m!=18
if '%%s'=='%temp%' set val!m!=19
if '%%t'=='%temp%' set val!m!=20
if '%%u'=='%temp%' set val!m!=21
if '%%v'=='%temp%' set val!m!=22
if '%%w'=='%temp%' set val!m!=23
if '%%x'=='%temp%' set val!m!=24
if '%%y'=='%temp%' set val!m!=25
if '%%z'=='%temp%' set val!m!=26
)
if not %vars% equ %m% goto indexing
set var=0
:confirmvar
set /a var+=1
set tmp=!var%var%!
set /a value1=!val%var%!+1
for /f "tokens=%value1% delims== """ %%1 in ('type "%file%" ^| find "%tmp%"') do set val%var%=%%1
if not !var! equ %vars% goto confirmvar
:output
echo %val1% %val2% %val3% %val4% %val5% %val6% %val7% %val8% %val9%>>outputfile.txt
if not !c! equ !f! set m=0&goto skip1
start notepad outputfile.txt
exit /b
---------------------

Simple solutions are often the best

message edited by hidde663



#1
March 26, 2017 at 22:48:04
The xml file looks like this:

-<testInfo physicalPruefumfangName="FINISH" iStep="F056-16-11-504" testTime="2017-03-24T08:52:05" retryCountSummary="40" editor="DEFAULT" testVersion="36" pruefumfangName="FINISH" complete="1" errorCount="0" testDuration="90"

For example I need the value from "testDuration & errorCount"


Report •

#2
March 27, 2017 at 01:52:28
test.bat
-----------------------------
@echo off&for /f "tokens=19,21 delims== " %%a in (test.xml) do echo %%a %%b&pause>nul
-----------------------------

gives me: "0" "90"

Simple solutions are often the best


Report •

#3
March 27, 2017 at 02:26:53
gives me:

"2017-03-24T08:52:05" "F056-16-11-504"

but how do I check with .bat 1000 xml files for certain values? (using .bat)
Sorry I'am quite new to it
thanks for the answer and help !

message edited by meiyoulimao


Report •

Related Solutions

#4
March 27, 2017 at 02:36:14
Should be do-able, give me like an hour or 2, will be getting lunch ;)

In the meantime, please explain exactly how you want to output to be.

example output.txt after checking 5 files:
---------------------------------------------
errorCount;testDuration
"0";"90"
"0";"16"
"0";"32"
"1";"65535"
"2";"2147483647"
----------------------------------------------

or do you maby want the file name to be included every line?

Simple solutions are often the best


Report •

#5
March 27, 2017 at 02:58:07
Let's say I'am interested in :
testTime
testVersion
testDuration
errorCount
------------------------------------
Output can be "0";"90"

File name doesn't need to be included :)

Much appreciated and have a good one :)


Report •

#6
March 27, 2017 at 05:06:54
test.bat
------------------------
@echo off&setlocal EnableDelayedExpansion
set "folder=%~dp0"
set "folder=%folder:~0,-1%"
:UI
cls&echo Enter the amount of variables you want to save the value of(1-9):
choice /c 123456789 /n>nul
set vars=%errorlevel%&set n=0&set m=0
:loop
if %n% equ %vars% goto indexing
set /a n+=1
cls&echo Enter the name of variable %n% ^& hit enter:
set /p "var%n%="
goto loop
:indexing
set /a m+=1
set temp=!var%m%!
for /f "tokens=1-26 delims== """ %%a in ('type test.xml ^| findstr /I "%temp%"') do (
if '%%a'=='%temp%' set val!m!=1&goto end
if '%%b'=='%temp%' set val!m!=2&goto end
if '%%c'=='%temp%' set val!m!=3&goto end
if '%%d'=='%temp%' set val!m!=4&goto end
if '%%e'=='%temp%' set val!m!=5&goto end
if '%%f'=='%temp%' set val!m!=6&goto end
if '%%g'=='%temp%' set val!m!=7&goto end
if '%%h'=='%temp%' set val!m!=8&goto end
if '%%i'=='%temp%' set val!m!=9&goto end
if '%%j'=='%temp%' set val!m!=10&goto end
if '%%k'=='%temp%' set val!m!=11&goto end
if '%%l'=='%temp%' set val!m!=12&goto end
if '%%m'=='%temp%' set val!m!=13&goto end
if '%%n'=='%temp%' set val!m!=14&goto end
if '%%o'=='%temp%' set val!m!=15&goto end
if '%%p'=='%temp%' set val!m!=16&goto end
if '%%q'=='%temp%' set val!m!=17&goto end
if '%%r'=='%temp%' set val!m!=18&goto end
if '%%s'=='%temp%' set val!m!=19&goto end
if '%%t'=='%temp%' set val!m!=20&goto end
if '%%u'=='%temp%' set val!m!=21&goto end
if '%%v'=='%temp%' set val!m!=22&goto end
if '%%w'=='%temp%' set val!m!=23&goto end
if '%%x'=='%temp%' set val!m!=24&goto end
if '%%y'=='%temp%' set val!m!=25&goto end
if '%%z'=='%temp%' set val!m!=26&goto end
)
:end
if not %vars% equ %m% goto indexing
set var=0
:confirmvar
set /a var+=1
set tmp=!var%var%!
set value1=!val%var%!
set /a value2=!value1!+1
for /f "tokens=%value1%,%value2% delims== """ %%1 in ('type test.xml ^| findstr /M /I "%tmp%"') do if '%%1'=='%tmp%' set val%var%=%%2
if not !var! equ %vars% goto confirmvar
:output
echo %val1% %val2% %val3% %val4% %val5% %val6% %val7% %val8% %val9%>>outputfile.txt
exit /b
-------------------------

outputfile.txt contains:
-----------------------
"2017-03-24T08:52:05" "36" "90" "0"
---------------------

using example test.xml that contains:
----------------------
-<testInfo physicalPruefumfangName="FINISH" iStep="F056-16-11-504" testTime="2017-03-24T08:52:05" retryCountSummary="40" editor="DEFAULT" testVersion="36" pruefumfangName="FINISH" complete="1" errorCount="0" testDuration="90"
----------------------

Comments:

this is still a testversion, since it does not search through all files in the currentdirectory

If you still get a diffrent output, it means your example text and actual text in the .xml file do not match

multi-line sourcefiles are not supported yet, but it is possible

I'll be back in a day or 2 to finish this

Simple solutions are often the best

message edited by hidde663


Report •

#7
March 27, 2017 at 12:39:53
Here's another approach, but might fail if the format differs from your sample.
@echo off &setlocal enabledelayedexpansion
set sel=
rem put your target elements in this file named "selected" all on one line separated
rem by space, like this: testtime istep errorcount testduration
if exist selected set /p sel=<selected
rem output the column headers from 'selected' file to file 'xml.out'
if defined sel >xml.out echo %sel: =;%
set heads=
set delim=
for %%z in (*.xml) do (
for /f "tokens=2 delims=<>" %%a in (%%z) do call :xx "%%a"
)
goto :eof

:xx
set x=%1
rem first get rid of the "nasties"
set x=%x:<=%
set x=%x:>=%
set x=%x:"=%
echo x: %x%
rem clear accumulator-strings
set heads=
set data=
set line=
set delim=
rem****************************** added this line to clear old values
for %%z in (%sel%) do set %%z=
call :bb %x%
echo header: %heads%
echo values: %data%
for %%a in (%heads%) do echo !%%a!
rem if no columns were requested, then nothing to output.
if not defined sel goto :eof
echo selected values: %sel%
set delim=
for %%a in (%sel%) do (
set line=!line!!delim!"!%%a!"
set delim=;
)
rem ----------- this is the data-value output to 'xml.out' for each xml file
>>xml.out echo !line!
goto :eof

:bb
shift
if "%1" equ "" goto :eof
rem echo set %1=%2
(set %1=%2) 2>nul
set heads=%heads%%delim%%1
set data=%data%%delim%"%2"
set delim=;
shift
if "%1" neq "" goto :bb
goto :eof
::=========== end batch
note that this collects all the values into variables as named in the xml
file. The actual selection is determined by the names enumerated in the first line
of a file named "selected", so you can change them without having to modify the
batch-script. So far this has worked in my tests. OH, and be sure to put
ONLY ONE space between the selected items, and no space at the end of the line or you'll get too many semicolons in the header.

message edited by nbrane


Report •

#8
March 27, 2017 at 14:00:09

Report •

#9
March 27, 2017 at 19:27:18
how do i set up the folder for it? sorry I might be stupid somehow ! @hidde663

Thanks for the help @nbrane. I'll test it right away

@Razor 2.3 I don't think I can do this only with Excel. I have at least 1000 xml files which might differ from each other but contain the same values which I'am looking for. With .bat I can make it work for Excel I think.


Report •

#10
March 29, 2017 at 01:53:28
test.bat
-------------------------------
@echo off&setlocal EnableDelayedExpansion&set n=0&set m=0&set f=0&c=0&set d=0
cls&echo Enter the amount of variables you want to save the value of(1-9):
choice /c 123456789 /n>nul
set vars=%errorlevel%
:loop
if %n% equ %vars% for /f "tokens=4" %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do (set "file=%%A"
goto folderloop)
set /a n+=1
cls&echo Enter the name of variable %n%(CAPS sensitive) ^& hit enter:
set /p "var%n%="
goto loop
:folderloop
for /f "tokens=4" %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do set /a f+=1
:skip1
set /a c+=1
set /a d=!c!-1
if !d! equ 0 for /f "tokens=4" %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do set "file!c!=%%A"&goto indexing
for /f "skip=%d% tokens=4" %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do set "file!c!=%%A"&goto indexing
:indexing
set "file=!file%c%!"
set /a m+=1
set temp=!var%m%!
for /f "tokens=1-26 delims== """ %%a in ('type "%file%" ^| find "%temp%"') do (
if '%%a'=='%temp%' set val!m!=1
if '%%b'=='%temp%' set val!m!=2
if '%%c'=='%temp%' set val!m!=3
if '%%d'=='%temp%' set val!m!=4
if '%%e'=='%temp%' set val!m!=5
if '%%f'=='%temp%' set val!m!=6
if '%%g'=='%temp%' set val!m!=7
if '%%h'=='%temp%' set val!m!=8
if '%%i'=='%temp%' set val!m!=9
if '%%j'=='%temp%' set val!m!=10
if '%%k'=='%temp%' set val!m!=11
if '%%l'=='%temp%' set val!m!=12
if '%%m'=='%temp%' set val!m!=13
if '%%n'=='%temp%' set val!m!=14
if '%%o'=='%temp%' set val!m!=15
if '%%p'=='%temp%' set val!m!=16
if '%%q'=='%temp%' set val!m!=17
if '%%r'=='%temp%' set val!m!=18
if '%%s'=='%temp%' set val!m!=19
if '%%t'=='%temp%' set val!m!=20
if '%%u'=='%temp%' set val!m!=21
if '%%v'=='%temp%' set val!m!=22
if '%%w'=='%temp%' set val!m!=23
if '%%x'=='%temp%' set val!m!=24
if '%%y'=='%temp%' set val!m!=25
if '%%z'=='%temp%' set val!m!=26
)
if not %vars% equ %m% goto indexing
set var=0
:confirmvar
set /a var+=1
set tmp=!var%var%!
set /a value1=!val%var%!+1
for /f "tokens=%value1% delims== """ %%1 in ('type "%file%" ^| find "%tmp%"') do set val%var%=%%1
if not !var! equ %vars% goto confirmvar
:output
echo %val1% %val2% %val3% %val4% %val5% %val6% %val7% %val8% %val9%>>outputfile.txt
if not !c! equ !f! set m=0&goto skip1
start notepad outputfile.txt
exit /b
-----------------------

comments:
------------------------
Multiline-files still not supported, might work on that :)
------------------------

outputfile.txt, tested with 2, but should work with 2147483647 files :D
---------------------------------------
"2017-03-24T08:52:05" "36" "90" "0"
"2017-03-24T08:52:06" "37" "91" "1"
---------------------------------------

Simple solutions are often the best

message edited by hidde663


Report •

#11
March 29, 2017 at 19:21:03
Sorry for my late response !

@Hidde663 wow, you really helped me out a lot !

It does check multiple files for me now :) You're the man ! honestly thank you so much !

Unfortunately, I can't upload the whole XML Data due to work :(

message edited by meiyoulimao


Report •

#12
March 31, 2017 at 03:06:46
@Hidde663
asking for a (small) tweak. Is it possible to sort the xml Data or skip files if they are too old? Lets say I only need the Data files from today. e.g: 31.03.17

Report •

#13
April 1, 2017 at 06:50:34
I've editted my last post, current-date-check has been added :D

checked with cmd on these files:

dir

Directory of C:\Users\hidde\Desktop

01-04-2017 16:09 <DIR> .
01-04-2017 16:09 <DIR> ..
17-03-2017 19:16 <DIR> 940M PhysX 1366 x768 lowQ
11-02-2017 21:09 20.246 BEP.xlsm
17-03-2017 20:10 <DIR> CPU PhysX 1366x768 lowQ
01-04-2017 16:09 2.283 extractxmldata.bat
03-03-2017 20:22 235 ftp-server.txt
17-03-2017 18:25 <DIR> HD 530 PhysX 1366x768 lowQ
28-02-2017 19:15 753.426 isle map v3.jpg
01-04-2017 16:10 146 outputfile.txt
10-03-2017 20:48 23.808 Plane-preformance-stats - kopie.xlsx
10-03-2017 20:48 23.808 Plane-preformance-stats.xlsx
01-04-2017 15:46 226 test.xml
25-03-2017 16:40 451 test1.txt
01-04-2017 15:47 226 test1.xml
05-03-2017 20:23 5.596.617 UserBenchMark.exe
11 File(s) 6.421.472 bytes
5 Dir(s) 26.710.237.184 bytes free

for /f "tokens=4" %a in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do echo %a
test.xml
test1.xml

message edited by hidde663


Report •

#14
April 2, 2017 at 19:35:34
Good morning :)

somehow it's bugging for me now hehe,

A loop is starting now at " Enter the name of variable 3( or any number)<CAPS sensitive> & hit enter

It got stuck in there. I literally can enter the name of variable as often as i want :D

the variable number just goes further and further hehe

It says : Enter the name of variabe 32<Caps sensitive> & hit enter:

( just to show how far it can go )

or Did I do sth wrong? :D


Report •

#15
April 3, 2017 at 00:14:55
>.<

give me a sec

updated post #10

Simple solutions are often the best

message edited by hidde663


Report •

#16
April 3, 2017 at 00:40:08
Mhm somehow the date is the 4th ( of the batch script) even tho its the 3rd of April here hehe :)

mhm I'll try something around and get back to you later !

message edited by meiyoulimao


Report •

#17
April 3, 2017 at 00:46:15
srry, ive updated it again 3min ago, you might want to try it 1 more time

also it does not like filenames with spaces in it & if there are no files made on the current date

& its supposed to be: 03-04-2017 (using system time)

This was the problem btw:

if %n% equ %vars% for /f "tokens=4" %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do (set "file=%%A"
goto folderloop)

if there was no file made on 03-04-2017 the for /f function would fail, but because its a double command, there was no errormessage and it would continue while ignoring the 2nd command (goto folderloop)

which in its turn caused the endless loop :O

Simple solutions are often the best

message edited by hidde663


Report •

#18
April 3, 2017 at 00:56:45
hm it gives me errors now somehow (echo is off)

I runned also the older batch script just to see and the older one works flawless ( the one without date check)


Report •

#19
April 3, 2017 at 01:00:27
✔ Best Answer
could it be you have no new files in the folder?
or perhaps it really is giving you a different date than me

I think i fixed it :)


test.bat
------------------------
@echo off&setlocal EnableDelayedExpansion&set n=0&set m=0&set f=0&c=0&set d=0
cls&echo Enter the amount of variables you want to save the value of(1-9):
choice /c 123456789 /n>nul
set vars=%errorlevel%
:loop
if %n% equ %vars% for /f "tokens=3,* delims= " %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do set "file=%%B"&goto folderloop
set /a n+=1
cls&echo Enter the name of variable %n%(CAPS sensitive) ^& hit enter:
set /p "var%n%="
goto loop
:folderloop
for /f "tokens=3,* delims= " %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do set /a f+=1
:skip1
set /a c+=1
set /a d=!c!-1
if !d! equ 0 for /f "tokens=3,* delims= " %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do set "file!c!=%%B"&goto indexing
for /f "skip=%d% tokens=3,* delims= " %%A in ('dir /4 ^| find "%date:~-10%" ^| find ".xml"') do set "file!c!=%%B"&goto indexing
:indexing
if '!file%c%!'=='' echo No files found with date:%date:~-10%&timeout 2 /nobreak>nul&exit /b
set "file=!file%c%!"
set /a m+=1
set temp=!var%m%!
for /f "tokens=1-26 delims== """ %%a in ('type "%file%" ^| find "%temp%"') do (
if '%%a'=='%temp%' set val!m!=1
if '%%b'=='%temp%' set val!m!=2
if '%%c'=='%temp%' set val!m!=3
if '%%d'=='%temp%' set val!m!=4
if '%%e'=='%temp%' set val!m!=5
if '%%f'=='%temp%' set val!m!=6
if '%%g'=='%temp%' set val!m!=7
if '%%h'=='%temp%' set val!m!=8
if '%%i'=='%temp%' set val!m!=9
if '%%j'=='%temp%' set val!m!=10
if '%%k'=='%temp%' set val!m!=11
if '%%l'=='%temp%' set val!m!=12
if '%%m'=='%temp%' set val!m!=13
if '%%n'=='%temp%' set val!m!=14
if '%%o'=='%temp%' set val!m!=15
if '%%p'=='%temp%' set val!m!=16
if '%%q'=='%temp%' set val!m!=17
if '%%r'=='%temp%' set val!m!=18
if '%%s'=='%temp%' set val!m!=19
if '%%t'=='%temp%' set val!m!=20
if '%%u'=='%temp%' set val!m!=21
if '%%v'=='%temp%' set val!m!=22
if '%%w'=='%temp%' set val!m!=23
if '%%x'=='%temp%' set val!m!=24
if '%%y'=='%temp%' set val!m!=25
if '%%z'=='%temp%' set val!m!=26
)
if not %vars% equ %m% goto indexing
set var=0
:confirmvar
set /a var+=1
set tmp=!var%var%!
set /a value1=!val%var%!+1
for /f "tokens=%value1% delims== """ %%1 in ('type "%file%" ^| find "%tmp%"') do set val%var%=%%1
if not !var! equ %vars% goto confirmvar
:output
echo %val1% %val2% %val3% %val4% %val5% %val6% %val7% %val8% %val9%>>outputfile.txt
if not !c! equ !f! set m=0&goto skip1
start notepad outputfile.txt
exit /b
---------------------

Simple solutions are often the best

message edited by hidde663


Report •

#20
April 3, 2017 at 02:17:49
Hm I don't know actually the probelm but now it shows me:

The system cannot find the file specified.

Eventhough I didnt change the folder or data structure. still xml files. weird. Sorry but u alrady helped me out so much ! I think the old one should be fine :)


Report •

#21
April 3, 2017 at 02:50:12
Seems to be a compatability problem with the format, language, or there are no files in the folder with creation date 03-04-2017

Mark the thread as solved :)

and maby try my last post 1 more time o.0

Simple solutions are often the best


Report •

#22
April 3, 2017 at 02:55:31
Sure thanks for all the help ! Really appreciate it ! :)

Report •

Ask Question