0

I am trying to take the results of a query to a db and save them to a csv file. Here is my current Ruby script.

#! /usr/bin/ruby

require 'rubygems'
require 'mysql'
require 'date'

# need mysql queries here / use mysql2 calls
db_con = Mysql.new("localhost", "root", "", "msd")

date_results = db_con.query("SELECT CONCAT(CONVERT(date_format(dd.date, '%b-%e'),char),'\\n') AS date
FROM msd.date_dim dd LEFT OUTER JOIN msd.results cs 
    ON dd.id = cs.date_id
GROUP BY
    dd.date")

# create new xml file and insert header
open('test_trend.xml', 'w') do |f|
  date_results.each_hash do |f|
    f.puts "#{f['date']}"
  end      
end

I get the following error. Line 23 is the date_results.each_hash line.

so_test.rb:24:in `block (2 levels) in <main>': private method `puts' called for {"date"=>"Jun-12\n"}:Hash (NoMethodError)
    from /Users/pierce/.rbenv/versions/1.9.2-p290/lib/ruby/gems/1.9.1/gems/ruby-mysql-2.9.9/lib/mysql.rb:686:in `call'
    from /Users/pierce/.rbenv/versions/1.9.2-p290/lib/ruby/gems/1.9.1/gems/ruby-mysql-2.9.9/lib/mysql.rb:686:in `each_hash'
    from so_test.rb:23:in `block in <main>'
    from so_test.rb:22:in `open'
    from so_test.rb:22:in `<main>'

Any advice is appreciated. Thanks.

analyticsPierce
  • 2,979
  • 9
  • 57
  • 81
  • http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format – Casper Jul 01 '12 at 08:18
  • @Casper thanks for the direction. My example is simplified and there are extra test lines that have to be added to the file to meet requirements. So there are several other f.puts line around the database results in my complete script. – analyticsPierce Jul 01 '12 at 08:26
  • That's not csv. Ruby has a csv library that escapes commas and things like that. What you're doing is just writing text to a file. And the fact that xml is the file extension makes it a little extra confusing since it's not xml data. – pguardiario Jul 01 '12 at 09:35
  • Yes, this is for a specific doc that is named .xml but is really a text file without correct xml formatting. I am working on a similar problem for true csv files and mislabeled the question title. Sorry about that. – analyticsPierce Jul 01 '12 at 22:39

1 Answers1

2

In your nested blocks, you're overriding the f variable. Instead, try something like:

# create new xml file and insert header
open('test_trend.xml', 'w') do |f|
  date_results.each_hash do |hash|
    f.puts "#{hash['date']}"
  end      
end
Chris Ledet
  • 11,458
  • 7
  • 39
  • 47