0

I am trying to pass a XML into an excel table for further analysis (graphs, dynamic tables, etc). I request the information from a HTTP Server with wget (linux), then pass it on to Windows and then I would like to know a way to pass this XML file into a table.

This is for 24 XML files (1 per hour) that recollets data from the http server. The part of requesting every hour is solved, but the data treatment is my problem.

So the XML file comes like this:

<Ptexport version="1.6.0" >

<Response><RR cid="1459546492000" sid="38587" eod="0">

<RR1 direction="0" statTime="1556668838385" lostperc="0" dmax="264947" jmax="50154" />
<RR1 direction="1" statTime="1556668838385" lostperc="0" dmax="34530" jmax="16085" />
<RR1 direction="0" statTime="1556668898385" lostperc="28428" dmax="239192" jmax="21417" />
<RR1 direction="1" statTime="1556668898385" lostperc="0" dmax="40576" jmax="19246" />
<RR1 direction="0" statTime="1556668958386" lostperc="31666" dmax="257147" jmax="22487" />
<RR1 direction="1" statTime="1556668958386" lostperc="0" dmax="52772" jmax="9648" />
<RR1 direction="0" statTime="1556669018386" lostperc="36666" dmax="236172" jmax="28367" />
<RR1 direction="1" statTime="1556669018386" lostperc="0" dmax="75074" jmax="9524" />
<RR1 direction="0" statTime="1556669078385" lostperc="26711" dmax="210017" jmax="28195" />
<RR1 direction="1" statTime="1556669078385" lostperc="0" dmax="87410" jmax="11392" />
<RR1 direction="0" statTime="1556669138386" lostperc="16638" dmax="215571" jmax="23909" />
</RR>

<RR cid="1459546492000" sid="34123" eod="0">

<RR1 direction="0" statTime="1556668832391" lostperc="1000000" dmax="0" jmax="0" />
<RR1 direction="1" statTime="1556668832391" lostperc="0" dmax="0" jmax="0" />
<RR1 direction="0" statTime="1556668892391" lostperc="1000000" dmax="0" jmax="0" />
<RR1 direction="1" statTime="1556668892391" lostperc="0" dmax="0" jmax="0" />
<RR1 direction="0" statTime="1556668952391" lostperc="1000000" dmax="0" jmax="0" />
<RR1 direction="1" statTime="1556668952391" lostperc="0" dmax="0" jmax="0" />

</RR></Response></Ptexport>

So we can see one tag for a give time with 6 values:

sid: is at the begginning of each measurement, it is the node id that I am using. RR1 direction: wether if the registry is Upstream or Downstream ('1' or '0') stattime: epoch linux time (i know how to pass it to human time, no problem there) lostperc, dmax, jmax: telecom values

So what I would like to happen is to show the data in an Excel likes this:

Excel RESULT

Daniel Rivas
  • 15
  • 4

1 Answers1

0

You need to first parse the XML. Take a look at this question or this tutorial for how to do that.

The second part is exporting the data to .csv or something else Excel can work with. For that, look at python's csv module.

Faboor
  • 1,365
  • 2
  • 10
  • 23
  • `openpyxl` and `xlwings` are also very convenient for creating/editing Excel files in Python. – Matt M May 14 '19 at 00:46