# XML to CSV

**URL:** <https://boards.straightdope.com/t/xml-to-csv/583352>\
**Category:** Factual Questions\
**Created:** [May 26, 2011, 8:54pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352 "2011-05-26T20:54:39Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![treis](https://avatars.discourse-cdn.com/v4/letter/t/bc79bd/32.png) [@treis](https://boards.straightdope.com/u/treis)\
**Post date:** [May 26, 2011, 8:54pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352/1 "2011-05-26T20:54:39Z")

</div>

Hey all,

I have an xml file with the format of:

```auto

  <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?

---

<div class="post-metadata">

**Author:** ![Kinthalis](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kinthalis/32/16084_2.png) [@Kinthalis](https://boards.straightdope.com/u/Kinthalis)\
**Post date:** [May 26, 2011, 9:01pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352/2 "2011-05-26T21:01:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![treis](https://avatars.discourse-cdn.com/v4/letter/t/bc79bd/32.png) [@treis](https://boards.straightdope.com/u/treis)\
**Post date:** [May 26, 2011, 9:22pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352/3 "2011-05-26T21:22:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\
**Post date:** [May 26, 2011, 9:26pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352/4 "2011-05-26T21:26:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Khadaji](https://avatars.discourse-cdn.com/v4/letter/k/9e8a1a/32.png) [@Khadaji](https://boards.straightdope.com/u/Khadaji)\
**Post date:** [May 26, 2011, 9:31pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352/5 "2011-05-26T21:31:58Z")

</div>

> [@treis](#):
>
> Really? Excel can do that? Interest. I use Open Office, but if Excel can do that I might just pick it up.

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

---

<div class="post-metadata">

**Author:** ![psychonaut](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/psychonaut/32/4655_2.png) [@psychonaut](https://boards.straightdope.com/u/psychonaut)\
**Post date:** [May 26, 2011, 10:16pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352/6 "2011-05-26T22:16:21Z")

</div>

I’d use [sed](http://en.wikipedia.org/wiki/Sed).

---

<div class="post-metadata">

**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\
**Post date:** [May 26, 2011, 10:31pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352/7 "2011-05-26T22:31:39Z")

</div>

> [@treis](#):
>
> Really? Excel can do that? Interest. I use Open Office, but if Excel can do that I might just pick it up.

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

---

<div class="post-metadata">

**Author:** ![treis](https://avatars.discourse-cdn.com/v4/letter/t/bc79bd/32.png) [@treis](https://boards.straightdope.com/u/treis)\
**Post date:** [May 26, 2011, 10:49pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352/8 "2011-05-26T22:49:19Z")

</div>

> [@Reply](#):
>
> Did you figure it out yet? You shouldn’t have to buy Excel just for this.

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.

---

<div class="post-metadata">

**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\
**Post date:** [May 26, 2011, 10:57pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352/9 "2011-05-26T22:57:20Z")

</div>

> [@treis](#):
>
> 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.

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?

---

<div class="post-metadata">

**Author:** ![treis](https://avatars.discourse-cdn.com/v4/letter/t/bc79bd/32.png) [@treis](https://boards.straightdope.com/u/treis)\
**Post date:** [May 26, 2011, 11:30pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352/10 "2011-05-26T23:30:16Z")

</div>

```auto

  <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> 

```

---

<div class="post-metadata">

**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\
**Post date:** [May 26, 2011, 11:39pm UTC](https://boards.straightdope.com/t/xml-to-csv/583352/11 "2011-05-26T23:39:15Z")

</div>

> [@treis](#):
>
> ```auto
> 
> <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> 
> 
> ```

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

---

<div class="post-metadata">

**Author:** ![treis](https://avatars.discourse-cdn.com/v4/letter/t/bc79bd/32.png) [@treis](https://boards.straightdope.com/u/treis)\
**Post date:** [May 27, 2011, 12:04am UTC](https://boards.straightdope.com/t/xml-to-csv/583352/12 "2011-05-27T00:04:13Z")

</div>

Oops sorry, I copied from the wrong file:

```auto

  <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> 

```

---

<div class="post-metadata">

**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\
**Post date:** [May 27, 2011, 12:14am UTC](https://boards.straightdope.com/t/xml-to-csv/583352/13 "2011-05-27T00:14:03Z")

</div>

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

You need two documents:

treis.xml

```auto

<?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:

```auto

<?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.

---

<div class="post-metadata">

**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\
**Post date:** [May 27, 2011, 12:32am UTC](https://boards.straightdope.com/t/xml-to-csv/583352/14 "2011-05-27T00:32:13Z")

</div>

Wow, apparently OpenOffice Calc really doesn’t have an easy XML import function… least that’s what [this thread](http://www.oooforum.org/forum/viewtopic.phtml?t=74197) 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)

---

<div class="post-metadata">

**Author:** ![treis](https://avatars.discourse-cdn.com/v4/letter/t/bc79bd/32.png) [@treis](https://boards.straightdope.com/u/treis)\
**Post date:** [May 27, 2011, 12:42am UTC](https://boards.straightdope.com/t/xml-to-csv/583352/15 "2011-05-27T00:42:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\
**Post date:** [May 27, 2011, 1:53am UTC](https://boards.straightdope.com/t/xml-to-csv/583352/16 "2011-05-27T01:53:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Caught\_Work](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@Caught\_Work](https://boards.straightdope.com/u/Caught_Work)\
**Post date:** [May 27, 2011, 1:56am UTC](https://boards.straightdope.com/t/xml-to-csv/583352/17 "2011-05-27T01:56:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![treis](https://avatars.discourse-cdn.com/v4/letter/t/bc79bd/32.png) [@treis](https://boards.straightdope.com/u/treis)\
**Post date:** [May 27, 2011, 3:36am UTC](https://boards.straightdope.com/t/xml-to-csv/583352/18 "2011-05-27T03:36:32Z")

</div>

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.