Forum: Ruby XML to CSV with REXML - I'm sure this should be easy...

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Sandy T. (Guest)
on 2009-03-17 14:05
Ok, I'm taking a fairly simple xml file containing a series of events
and I want to convert it to csv - nothing new there.

However, some events have two or more dates listed and I'd like to
display each as individual lines. My ruby skills are fairly limited but
from googling around I can extract everything up to the dates, but I'm
banging my head against a wall to get any further...

Here's the XML:

<event id='1234'>

  <title>Event Title</title>

  <category>Event Category</category>

  <venue>

    <name>Venue Name</name>

    <address>

      <address1>1 Some Street</address1>

      <town>Some Town</town>

    </address>

  </venue>

  <performances>

    <performance date='2009-04-01 18:00:00' />

    <performance date='2009-04-03 18:00:00' />

  </performances>

</event>

This is my extraction code:

require 'rexml/document'
xml = REXML::Document.new(File.open("data.xml"))
csv_file = File.new("data.csv", "w")
xml.elements.each("//event") do |e|
    csv_file.puts e.attributes['id'] << "|" <<
            e.elements['title'].text << "|" <<
      e.elements['category'].text << "|" <<
      e.elements['venue/name'].text << "|" <<
      e.elements['venue/address/address1'].text << "|" <<
      e.elements['venue/address/town'].text
end

Which gives me:
1234|Event Title|Event Category|Venue Name|1 Some Street|Some Town

But what I really want is:
1234|Event Title|Event Category|Venue Name|1 Some Street|Some
Town|2009-04-01 18:00:00
1234|Event Title|Event Category|Venue Name|1 Some Street|Some
Town|2009-04-03 18:00:00

I'm sure this should be fairly simple but any help would be appreciated.
Cheers!
Robert K. (Guest)
on 2009-03-17 17:10
(Received via mailing list)
On 17.03.2009 13:03, Sandy T. wrote:
> <event id='1234'>
>
>     <performance date='2009-04-01 18:00:00' />
> xml = REXML::Document.new(File.open("data.xml"))
> csv_file = File.new("data.csv", "w")
> xml.elements.each("//event") do |e|
>     csv_file.puts e.attributes['id'] << "|" <<
>             e.elements['title'].text << "|" <<
>       e.elements['category'].text << "|" <<
>       e.elements['venue/name'].text << "|" <<
>       e.elements['venue/address/address1'].text << "|" <<
>       e.elements['venue/address/town'].text

Here you need to iterate through all the "performance" elements _below
the current event_ and concatenate the individual performance's date
with what you have built so far.

You should probably also take measures to emit a line without a date in
case zero performances can be found in input XML.

>
> I'm sure this should be fairly simple but any help would be appreciated.
> Cheers!

Kind regards

  robert
matt neuburg (Guest)
on 2009-03-17 17:16
(Received via mailing list)
Sandy T. <removed_email_address@domain.invalid> wrote:

> <event id='1234'>
>
>     <performance date='2009-04-01 18:00:00' />
> xml = REXML::Document.new(File.open("data.xml"))
> Which gives me:
> 1234|Event Title|Event Category|Venue Name|1 Some Street|Some Town
>
> But what I really want is:
> 1234|Event Title|Event Category|Venue Name|1 Some Street|Some
> Town|2009-04-01 18:00:00
> 1234|Event Title|Event Category|Venue Name|1 Some Street|Some
> Town|2009-04-03 18:00:00

What you are really interested in is each performance (each performance
generates one line of output). So simply deepen your loop:

xml.elements.each("//event") do |e|
  e.elements.each("//performance") do |p|

Now do exactly what you're doing and just append the performance date.
So, for example:

require 'rexml/document'
include REXML
output = ""
class REXML::Element
  def textof(xpaths_arr); xpaths_arr.map {|x| elements[x].text}; end
end
xml = Document.new(s)
xp = %w{title category venue/name
  venue/address/address1 venue/address/town}
xml.elements.each("//event") do |e|
  e.elements.each("//performance") do |p|
    output <<
      [e.attributes['id'],
      e.textof(xp),
      p.attributes['date']].flatten.join("|") + "\n"
  end
end

m.
Sandy T. (Guest)
on 2009-03-17 17:50
Robert K. wrote:
>
> Here you need to iterate through all the "performance" elements _below
> the current event_ and concatenate the individual performance's date
> with what you have built so far.
>
> You should probably also take measures to emit a line without a date in
> case zero performances can be found in input XML.
>
>
> Kind regards
>
>   robert

Thank you Robert, I'm getting closer...

I modified it as below, but for some reason the dates are now stacking
up on each other as so:
1234|Event Title|Event Category|Venue Name|1 Some Street|Some
Town|2009-04-01 18:00:00
1234|Event Title|Event Category|Venue Name|1 Some Street|Some
Town|2009-04-01 18:00:00|2009-04-03 18:00:00

So, I'm still missing something - any ideas?

xml.elements.each("//event") do |e|
  detail =
  (
    e.attributes['id'] << "|" <<
    e.elements['title'].text << "|" <<
    e.elements['category'].text << "|" <<
    e.elements['venue/name'].text << "|" <<
    e.elements['venue/address/address1'].text << "|" <<
    e.elements['venue/address/town'].text
  )

  xml.elements.each("//performances/performance") do |f|
    csv_file.puts detail << "|" << f.attributes['date']
  end
end
Sandy T. (Guest)
on 2009-03-17 17:55
>   xml.elements.each("//performances/performance") do |f|

That should read e.elements.each.... but the result is the same

- cheers for the reply matt, just looking at that now
Sandy T. (Guest)
on 2009-03-17 18:27
> What you are really interested in is each performance (each performance
> generates one line of output). So simply deepen your loop:
>
> xml.elements.each("//event") do |e|
>   e.elements.each("//performance") do |p|
>
> Now do exactly what you're doing and just append the performance date.
> So, for example:
>
> require 'rexml/document'
> include REXML
> output = ""
> class REXML::Element
>   def textof(xpaths_arr); xpaths_arr.map {|x| elements[x].text}; end
> end
> xml = Document.new(s)
> xp = %w{title category venue/name
>   venue/address/address1 venue/address/town}
> xml.elements.each("//event") do |e|
>   e.elements.each("//performance") do |p|
>     output <<
>       [e.attributes['id'],
>       e.textof(xp),
>       p.attributes['date']].flatten.join("|") + "\n"
>   end
> end
>
> m.

Ok, so this is plainly much neater, thanks Matt :)

...but, whilst it works for one event with multiple dates, as soon as I
add a second event it iterates through all of the dates against every
event, so for 2 events each with 2 dates it outputs 8 lines...

Here's the code as it now stands:

require 'rexml/document'
include REXML
output = ""
class REXML::Element
  def textof(xpaths_arr); xpaths_arr.map {|x| elements[x].text}; end
end
xml = REXML::Document.new(File.open("data.xml"))
csv_file = File.new("data.csv", "w")
xp = %w{title category venue/name venue/address/address1
venue/address/town}

xml.elements.each("//event") do |e|
  e.elements.each("//performance") do |p|

  csv_file.puts output + [e.attributes['id'], e.textof(xp),
p.attributes['date']].flatten.join("|") + "\n"

  end
end
matt neuburg (Guest)
on 2009-03-17 20:54
(Received via mailing list)
Sandy T. <removed_email_address@domain.invalid> wrote:

> ..but, whilst it works for one event with multiple dates, as soon as I
> add a second event it iterates through all of the dates against every
> event, so for 2 events each with 2 dates it outputs 8 lines...

Cool! :)

> xml.elements.each("//event") do |e|
>   e.elements.each("//performance") do |p|

Yeah, sorry about that. I wasn't thinking about the XPath here.
Obviously "//performance" is wrong. I shoulda said
"descendant::performance" or "performances/performance" or similar.

Of course one could also argue that Ruby and REXML are more heavyweight
than you need; you're just dumpster-diving in simple XML and outputting
text, so you could write this whole thing as an XSLT template. Choices,
choices...!

m.
Sandy T. (Guest)
on 2009-03-17 21:02
matt neuburg wrote:
> Sandy T. <removed_email_address@domain.invalid> wrote:
>
>> ..but, whilst it works for one event with multiple dates, as soon as I
>> add a second event it iterates through all of the dates against every
>> event, so for 2 events each with 2 dates it outputs 8 lines...
>
> Cool! :)
>
>> xml.elements.each("//event") do |e|
>>   e.elements.each("//performance") do |p|
>
> Yeah, sorry about that. I wasn't thinking about the XPath here.
> Obviously "//performance" is wrong. I shoulda said
> "descendant::performance" or "performances/performance" or similar.
>
> Of course one could also argue that Ruby and REXML are more heavyweight
> than you need; you're just dumpster-diving in simple XML and outputting
> text, so you could write this whole thing as an XSLT template. Choices,
> choices...!
>
> m.

Choices indeed, but it works perfectly now so I'll go with it :)

Thanks!
This topic is locked and can not be replied to.