XML to CSV

Factual Questions
1

Hey all,

I have an xml file with the format of:



  <node id='-1' visible='true' lat='11.2523448' lon='-85.8714329'>
    <tag k='created_by' v='GPSBabel-1.4.2'/>
    <tag k='name' v='006'/>
    <tag k='note' v='23-APR-11 11:35:26AM'/>
  </node>

I want to create a CSV file with the columns ‘name’, ‘lat’, and ‘lon’. What’s the easiest way for me to do this?

2

You could try opening it in excel, then saving it as a csv.

3

Really? Excel can do that? Interest. I use Open Office, but if Excel can do that I might just pick it up.

4

If Excel can’t do it, you should look into XSLT. All your favorite scripting languages should have support for it.

5

I copied your example to an xml file and opened it in Excel. It opened fine and produced this csv

id,visible,lat,lon,k,v
-1,TRUE,11.2523448,-85.8714329,created_by,GPSBabel-1.4.2
-1,TRUE,11.2523448,-85.8714329,name,006
-1,TRUE,11.2523448,-85.8714329,note,23-APR-11 11:35:26AM

6

I’d use sed.

7

Did you figure it out yet? You shouldn’t have to buy Excel just for this.

8

I probably shouldn’t have to, but it seems by far the easiest option. I don’t have a favorite scripting language, and sed makes my eyes glaze. It is too bad open office can’t open it.

9

It won’t be automatic with Excel, either, because of the way it’s formatted. I take it each new location is a separate <node>, right?

ETA: Could you post a longer example with 2-3 samples?

10 

  <node id='-1' visible='true' lat='11.4646745' lon='-86.1141656'>
    <tag k='created_by' v='GPSBabel-1.4.2'/>
  </node>
  <node id='-2' visible='true' lat='11.4648664' lon='-86.1144875'>
    <tag k='created_by' v='GPSBabel-1.4.2'/>
  </node>
  <node id='-3' visible='true' lat='11.4649419' lon='-86.1146632'>
    <tag k='created_by' v='GPSBabel-1.4.2'/>
  </node>
11

What happened to the names? Is the “id” the name or the “name” field the actual name?

12

Oops sorry, I copied from the wrong file:


  <node id='-145706' timestamp='2011-05-26T20:46:31Z' visible='true' lat='11.4414161' lon='-85.8321054'>
    <tag k='name' v='258' />
    <tag k='note' v='24-MAY-11 1:54:38PM' />
  </node>
  <node id='-145704' timestamp='2011-05-26T20:46:31Z' visible='true' lat='11.4407362' lon='-85.8317069'>
    <tag k='name' v='257' />
    <tag k='note' v='24-MAY-11 1:52:28PM' />
  </node>
  <node id='-145702' timestamp='2011-05-26T20:46:31Z' visible='true' lat='11.5401551' lon='-85.7008251'>
    <tag k='name' v='256' />
    <tag k='note' v='24-MAY-11 12:09:28PM' />
  </node>
13

As ultrafilter suggested, you can do this relatively easily (and with no charge) with XSLT.

You need two documents:

treis.xml



<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml-stylesheet type="text/xsl" href="treis.xsl"?>
<document>
<!-- Paste nodes here-->

  <node id='-145706' timestamp='2011-05-26T20:46:31Z' visible='true' lat='11.4414161' lon='-85.8321054'>
    <tag k='name' v='258' />
    <tag k='note' v='24-MAY-11 1:54:38PM' />
  </node>
  <node id='-145704' timestamp='2011-05-26T20:46:31Z' visible='true' lat='11.4407362' lon='-85.8317069'>
    <tag k='name' v='257' />
    <tag k='note' v='24-MAY-11 1:52:28PM' />
  </node>
  <node id='-145702' timestamp='2011-05-26T20:46:31Z' visible='true' lat='11.5401551' lon='-85.7008251'>
    <tag k='name' v='256' />
    <tag k='note' v='24-MAY-11 12:09:28PM' />
  </node>

<!-- Don't edit below this line -->
</document>

And the XSLT stylesheet:



<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="document">name,lat,long
<xsl:for-each select="node">
	<xsl:value-of select="tag[@k='name']/@v"/>,<xsl:value-of select="@lat"/>,<xsl:value-of select="@lon"/><xsl:text>
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Put in the full list of nodes in treis.xml, between the two comment lines. Then, if you use Firefox, you should be able to just put both files in the same directory and open treis.xml and see it as a csv. If you don’t use Firefox, maybe you can email me the XML and I’ll email you back a CSV?

If you do this a lot, obviously, XSLT might be worth learning, or perhaps tinkering more with OpenOffice and XML imports, or regular expressions, blah blah… there are many ways.

14

Wow, apparently OpenOffice Calc really doesn’t have an easy XML import function… least that’s what this thread is saying.

Their solution? Write a custom XSLT filter for XML -> OpenOffice Calc format. :smack:

(I’m not sure if Excel is any better at this, to be honest)

15

That’s fantastic, thanks a bunch. I need to do a lot of nodes, but not necessarily on many occassions, so that should easily be enough.

16

I forgot to mention that the stylesheet should be called treis.xsl, in case it wasn’t obvious.

17

GPSBabel which created this file will export it to a csv file.

18

Yes, but the problem is that I am making slight adjustments to the nodes in JOSM. GPS Babel can go from the .osm file to a csv, but it doesn’t pick up the name tag.