XML data to CSV
Posted: 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:
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:
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
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>
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>
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