XML data to CSV

pman92 · Post by **pman92** » Mon Dec 06, 2021 7:23 pm

Hey guys,
I've collected some data logs from a scan tool (snapon). They are saved in a ".pids" format which can be used with the snapon software (Shopstream scanner data viewer). However I would like to export that data to .csv format so I can work with it in excel.
I've found after opening the .pids file with notepad that its just xml data. Here's a trimmed down example:

Code: Select all

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<pids-collection breakAtSamples="11" year="2021" make="SomeMake" model="Commodore" engine="V8" system="Engine">
  <pid index="1157899" name="Vehicle Speed (km/h)" default-units="UnitTypeSpeedKilometres">
    <sample time="0" stringValue="0">0</sample>
    <sample time="1" stringValue="0">0</sample>
    <sample time="2" stringValue="0">0</sample>
    <sample time="3" stringValue="0">0</sample>
    <sample time="4" stringValue="0">0</sample>
    <sample time="5" stringValue="0">0</sample>
    <sample time="6" stringValue="0">0</sample>
    <sample time="7" stringValue="0">0</sample>
    <sample time="8" stringValue="0">0</sample>
    <sample time="9" stringValue="0">0</sample>
    <sample time="10" stringValue="0">0</sample>
    <sample time="11" stringValue="0">0</sample>
  </pid>
  <pid index="3417610" name="Intake Manifold Absolute Pressure Supported" default-units="UnitTypeString">
    <options-list>
      <option index="0">Unsupport</option>
      <option index="1">Support</option>
    </options-list>
    <sample time="0" stringValue="Support">1</sample>
    <sample time="1" stringValue="Support">1</sample>
    <sample time="2" stringValue="Support">1</sample>
    <sample time="3" stringValue="Support">1</sample>
    <sample time="4" stringValue="Support">1</sample>
    <sample time="5" stringValue="Support">1</sample>
    <sample time="6" stringValue="Support">1</sample>
    <sample time="7" stringValue="Support">1</sample>
    <sample time="8" stringValue="Support">1</sample>
    <sample time="9" stringValue="Support">1</sample>
    <sample time="10" stringValue="Support">1</sample>
    <sample time="11" stringValue="Support">1</sample>
  </pid>
  <pid index="1480829" name="Accel Position (%)" default-units="UnitTypePercentage">
    <sample time="0" stringValue="0">0.0</sample>
    <sample time="1" stringValue="0">0.0</sample>
    <sample time="2" stringValue="0">0.0</sample>
    <sample time="3" stringValue="0">0.0</sample>
    <sample time="4" stringValue="0">0.0</sample>
    <sample time="5" stringValue="0">0.0</sample>
    <sample time="6" stringValue="0">0.0</sample>
    <sample time="7" stringValue="0">0.0</sample>
    <sample time="8" stringValue="0">0.0</sample>
    <sample time="9" stringValue="0">0.0</sample>
    <sample time="10" stringValue="0">0.0</sample>
    <sample time="11" stringValue="0">0.0</sample>
  </pid>
</pids-collection>

I've tried importing it directly as xml data in excel but I cant get it to work nicely. I've found if I manually edit the file and copy and paste something like this out of it to a new xml file, I can import one item to excel at a time:

Code: Select all

<pid>
    <sample time="0" stringValue="0">0</sample>
    <sample time="1" stringValue="0">0</sample>
    <sample time="2" stringValue="0">0</sample>
    <sample time="3" stringValue="0">0</sample>
    <sample time="4" stringValue="0">0</sample>
    <sample time="5" stringValue="0">0</sample>
    <sample time="6" stringValue="0">0</sample>
    <sample time="7" stringValue="0">0</sample>
    <sample time="8" stringValue="0">0</sample>
    <sample time="9" stringValue="0">0</sample>
    <sample time="10" stringValue="0">0</sample>
    <sample time="11" stringValue="0">0</sample>
  </pid>

Note that I have to delete the "index", "name" and "default-units" attributes of the "pid" node.

Ideally I would like to simply convert the whole .pids file into a csv that I can import straight into excel, quickly and easily, and end up with a column for Time and columns for each PID, probably with its stringValue as the data.

There might be a way to make this work directly with excel but I've got no idea how to do it. Or I was thinking its probably not hard to convert it to .csv format before importing to excel. I was also thinking something like Python might be useful for that, but again I've never coded anything in python in my life.

Does anyone have any helpful ideas?

Cheers