DonationCoder.com Software > Post New Requests Here

NetTraffic xml export to Excel

<< < (3/3)

itge13:
ok guys I know now how to translate the values. But not how to extract them.
I try to achieve it with powershell. But how can I separate all values of the same line with comas?
<key dy="19904" dx="30080" ul="1725446" dl="28220776" tm="34377250805" ts="636850944"/>
<key dy="139586" dx="221126" ul="44928805" dl="1278397665" tm="370183712706" ts="636851808"/>
should looke like this:
199904,30080,1725446 etc
139586,221126,44928805 etc

Ath:
Thanks for sharing. And I'm happy my educated guess about the ts field was quite close :-[

Can you attach an original xml file to a reply? Scraping it from your messages doesn't provide the original structure, you seem to have taken clips from a webbrowser (the dashes in front of the <Stat elements give it away ;)) (Finally downloaded NetTraffic and did a testrun 8))

Ath:
Well, I mocked up a batchfile, using xmlstarlet to be downloaded here, that converts all "NetTraffic <date-time>.xml" files of current directory into a single NetTrafficAll.csv file.

(Used the PowerShell highlighter as Batch isn't supported by the board)
You'll need to adjust the path to the xml.exe file, and check the other settings as 'documented'
NOTE: No data-conversions done here, Excel can do that better and easier

--- Code: PowerShell ---@echo off:: Set the relative path to your xmlstarlet install:set XML=..\Downloads\XML\xmlstarlet-1.6.1\xml.exe:: Change filemask if desiredset "FILEMASK=NetTraffic 2*.xml":: Change result outputfile as desired, keep the .csv extension, when adding spaces, surround with quotes like FILEMASKset OUTFILE=NetTrafficAll.csv:: Add StatHour to this list if desiredset "ELEMENTS=StatYear StatMonth StatDay":: Choose csv separator, either , (comma) or ; (semicolon)set CSVSEP=; set XMLARG1=sel -T -t -m NetTraffic/set XMLARG2=/key -v "concat('set XMLARG3=%CSVSEP%',@ts,'%CSVSEP%',@tm,'%CSVSEP%',@dl,'%CSVSEP%',@ul,'%CSVSEP%',@dx,'%CSVSEP%',@dy)" -n :: Write headerecho Period%CSVSEP%ts%CSVSEP%tm%CSVSEP%dl%CSVSEP%du%CSVSEP%dx%CSVSEP%dy>"%OUTFILE%":: Loop all files from FILEMASKfor %%F in ("%FILEMASK%") do (echo File: %%F:: Fetch all configured elementsfor %%G in (%ELEMENTS%) do (%XML% %XMLARG1%%%G%XMLARG2%%%G%XMLARG3% "%%F" >>"%OUTFILE%"))
NOTE2: The csv file will be fully replaced each run of the script!

itge13:
@ath thanks a lot for your work. Like a charm :Thmbsup:

if anyone else needs it - excel formulas:

for date =((ts*POWER(10;2)/60/60/24)-693593)
for time =((C9/10000000))/24/60/60)
for dl/ul in MiB =dl/2^20
cursor =SQRT(pixel size*pixel size*dx*dx + pixel size*pixel size*dy*dy)

* pixel size in mm with the same name from here https://www.venea.net/web/pixel_calculator

Navigation

[0] Message Index

[*] Previous page