Forum: Ruby Parsing CSV file into a database

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.
7af028825f6c0f24554c69b2c93729a0?d=identicon&s=25 Nick Hoyle (hoylen)
on 2009-02-16 15:25
How would i go about parsing a csv from the web eg yahoo finance stock
data into my database table called stocks?

I would like this process to happen each time a user logs into there
account so it will update the table with the latest data possible from
the csv file.
E088bb5c80fd3c4fd02c2020cdacbaf0?d=identicon&s=25 Jesús Gabriel y Galán (Guest)
on 2009-02-16 16:44
(Received via mailing list)
On Mon, Feb 16, 2009 at 3:25 PM, Nick Hoyle <niho28@tiscali.co.uk>
wrote:
> How would i go about parsing a csv from the web eg yahoo finance stock
> data into my database table called stocks?

If you have a url to download the csv, you can read it with something
like open-uri.
Then use a CSV library like FasterCSV for csv parsing, and then I
would use a ORM
(Sequel, ActiveRecord or Datamapper come to mind) to create the rows
in your table.

Hope this helps,

Jesus.
7af028825f6c0f24554c69b2c93729a0?d=identicon&s=25 Nick Hoyle (hoylen)
on 2009-02-16 16:57
ok thanks thats great, thanks for the quick response. Have you any
sample code or examples that you could possible provide me?

Thanks for your time

Nick

Jesús Gabriel y Galán wrote:
> On Mon, Feb 16, 2009 at 3:25 PM, Nick Hoyle <niho28@tiscali.co.uk>
> wrote:
>> How would i go about parsing a csv from the web eg yahoo finance stock
>> data into my database table called stocks?
>
> If you have a url to download the csv, you can read it with something
> like open-uri.
> Then use a CSV library like FasterCSV for csv parsing, and then I
> would use a ORM
> (Sequel, ActiveRecord or Datamapper come to mind) to create the rows
> in your table.
>
> Hope this helps,
>
> Jesus.
4299e35bacef054df40583da2d51edea?d=identicon&s=25 James Gray (bbazzarrakk)
on 2009-02-16 17:09
(Received via mailing list)
On Feb 16, 2009, at 9:57 AM, Nick Hoyle wrote:

> ok thanks thats great, thanks for the quick response. Have you any
> sample code or examples that you could possible provide me?

It's probably as simple as:

   require "open-uri"
   require "rubygems"
   require "faster_csv"
   open("url goes here") do |csv|
     FCSV.new(csv).each do |row|
       # load row into database here...
     end
   end

Give it a shot and come back with specific questions when you get stuck.

James Edward Gray II
7af028825f6c0f24554c69b2c93729a0?d=identicon&s=25 Nick Hoyle (hoylen)
on 2009-02-16 17:39
Ok great! will do

Nick

James Gray wrote:
> On Feb 16, 2009, at 9:57 AM, Nick Hoyle wrote:
>
>> ok thanks thats great, thanks for the quick response. Have you any
>> sample code or examples that you could possible provide me?
>
> It's probably as simple as:
>
>    require "open-uri"
>    require "rubygems"
>    require "faster_csv"
>    open("url goes here") do |csv|
>      FCSV.new(csv).each do |row|
>        # load row into database here...
>      end
>    end
>
> Give it a shot and come back with specific questions when you get stuck.
>
> James Edward Gray II
9770ee3431156709c728f6105dd98f0d?d=identicon&s=25 Bosko Ivanisevic (Guest)
on 2009-02-16 20:40
(Received via mailing list)
On Feb 16, 5:39 pm, Nick Hoyle <nih...@tiscali.co.uk> wrote:
> >> sample code or examples that you could possible provide me?
> >    end
>
> > Give it a shot and come back with specific questions when you get stuck.
>
> > James Edward Gray II
>
> --
> Posted viahttp://www.ruby-forum.com/.

Here is a sample for downloading historical data from Yahoo:

def get_historical_data(symbol, startDateString, endDateString)
  startDate = Date.parse(startDateString)
  endDate = Date.parse(endDateString)
  query = "/table.csv?s=#{symbol}&g=d" +
          "&a=#{startDate.month-1}&b=#{startDate.mday}&c=#
{startDate.year}" +
          "&d=#{endDate.month-1}&e=#{endDate.mday}&f=#
{endDate.year.to_s}"
  Net::HTTP.start("itable.finance.yahoo.com", 80) { |http|
    res = http.get(query)
    res.body
  }
end
54404bcac0f45bf1c8e8b827cd9bb709?d=identicon&s=25 7stud -- (7stud)
on 2009-02-16 21:34
Bosko Ivanisevic wrote:
> On Feb 16, 5:39�pm, Nick Hoyle <nih...@tiscali.co.uk> wrote:
>> >> sample code or examples that you could possible provide me?
>> > � �end
>>
>> > Give it a shot and come back with specific questions when you get stuck.
>>
>> > James Edward Gray II
>>
>> --
>> Posted viahttp://www.ruby-forum.com/.
>
> Here is a sample for downloading historical data from Yahoo:
>
> def get_historical_data(symbol, startDateString, endDateString)
>   startDate = Date.parse(startDateString)
>   endDate = Date.parse(endDateString)
>   query = "/table.csv?s=#{symbol}&g=d" +
>           "&a=#{startDate.month-1}&b=#{startDate.mday}&c=#
> {startDate.year}" +
>           "&d=#{endDate.month-1}&e=#{endDate.mday}&f=#
> {endDate.year.to_s}"
>   Net::HTTP.start("itable.finance.yahoo.com", 80) { |http|
>     res = http.get(query)
>     res.body
>   }
> end


require 'date'
require 'net/http'

def get_historical_data(symbol, startDateString, endDateString)
  startDate = Date.parse(startDateString)
  endDate = Date.parse(endDateString)
  query = "/table.csv?s=#{symbol}&g=d" +
          "&a=#{startDate.month-1}&b=#{startDate.mday}&c=#
{startDate.year}" +
          "&d=#{endDate.month-1}&e=#{endDate.mday}&f=#
{endDate.year.to_s}"
  Net::HTTP.start("itable.finance.yahoo.com", 80) { |http|
    res = http.get(query)
    res.body
  }
end

p get_historical_data("AAPL", "12/28/2008", "1/7/2009")

--output:--
/usr/lib/ruby/1.8/net/http.rb:1556:in `read_status_line': wrong status
line: "Date,Open,High,Low,Close,Volume,Adj Close" (Net::HTTPBadResponse)
        from /usr/lib/ruby/1.8/net/http.rb:1538:in `read_new'
        from /usr/lib/ruby/1.8/net/http.rb:833:in `request'
        from /usr/lib/ruby/1.8/net/http.rb:615:in `get'
        from r1test.rb:13:in `get_historical_data'
        from r1test.rb:12:in `start'
        from /usr/lib/ruby/1.8/net/http.rb:324:in `start'
        from r1test.rb:12:in `get_historical_data'
        from r1test.rb:18
9770ee3431156709c728f6105dd98f0d?d=identicon&s=25 Bosko Ivanisevic (Guest)
on 2009-02-17 08:50
(Received via mailing list)
On Feb 16, 9:34 pm, 7stud -- <bbxx789_0...@yahoo.com> wrote:
> >> Posted viahttp://www.ruby-forum.com/.
> > {endDate.year.to_s}"
>   startDate = Date.parse(startDateString)
> end
>         from r1test.rb:12:in `start'
>         from /usr/lib/ruby/1.8/net/http.rb:324:in `start'
>         from r1test.rb:12:in `get_historical_data'
>         from r1test.rb:18
>
> --
> Posted viahttp://www.ruby-forum.com/.

Probably cut-paste caused error. Here is my slightly rewritten script
and output:

require 'net/http'
require 'date'

def get_historical_data(symbol, startDateString, endDateString)
  startDate = Date.parse(startDateString)
  endDate = Date.parse(endDateString)
  query = "/table.csv?s=#{symbol}&g=d"
  query.concat("&a=#{startDate.month-1}&b=#{startDate.mday}&c=#
{startDate.year}")
  query.concat("&d=#{endDate.month-1}&e=#{endDate.mday}&f=#
{endDate.year.to_s}")
  Net::HTTP.start("itable.finance.yahoo.com", 80) { |http|
    res = http.get(query)
    res.body
  }
end

puts get_historical_data("MSFT", "12/28/2008", "1/7/2009")

Date,Open,High,Low,Close,Volume,Adj Close
2009-01-07,20.19,20.29,19.48,19.51,72709900,19.51
2009-01-06,20.75,21.00,20.61,20.76,58083400,20.76
2009-01-05,20.20,20.67,20.06,20.52,61475200,20.52
2009-01-02,19.53,20.40,19.37,20.33,50084000,20.33
2008-12-31,19.31,19.68,19.27,19.44,46419000,19.44
2008-12-30,19.01,19.49,19.00,19.34,43224100,19.34
2008-12-29,19.15,19.21,18.64,18.96,58512800,18.96
54404bcac0f45bf1c8e8b827cd9bb709?d=identicon&s=25 7stud -- (7stud)
on 2009-02-17 14:15
Bosko Ivanisevic wrote:
> Probably cut-paste caused error. Here is my slightly rewritten script
> and output:
>

I get the same error:


require 'net/http'
require 'date'

def get_historical_data(symbol, startDateString, endDateString)
  startDate = Date.parse(startDateString)
  endDate = Date.parse(endDateString)
  query = "/table.csv?s=#{symbol}&g=d"
  query.concat("&a=#{startDate.month-1}&b=#{startDate.mday}&c=#
{startDate.year}")
  query.concat("&d=#{endDate.month-1}&e=#{endDate.mday}&f=#
{endDate.year.to_s}")
  Net::HTTP.start("itable.finance.yahoo.com", 80) { |http|
    res = http.get(query)
    res.body
  }
end

puts get_historical_data("MSFT", "12/28/2008", "1/7/2009")

--output:--

$ ruby r1test.rb
/usr/lib/ruby/1.8/net/http.rb:1556:in `read_status_line': wrong status
line: "Date,Open,High,Low,Close,Volume,Adj Close" (Net::HTTPBadResponse)
        from /usr/lib/ruby/1.8/net/http.rb:1538:in `read_new'
        from /usr/lib/ruby/1.8/net/http.rb:833:in `request'
        from /usr/lib/ruby/1.8/net/http.rb:615:in `get'
        from r1test.rb:13:in `get_historical_data'
        from r1test.rb:12:in `start'
        from /usr/lib/ruby/1.8/net/http.rb:324:in `start'
        from r1test.rb:12:in `get_historical_data'
        from r1test.rb:18
D7908f05c89e965f6bc5308ad6f41256?d=identicon&s=25 Siep Korteling (steenslag)
on 2009-02-17 14:35
7stud -- wrote:
> Bosko Ivanisevic wrote:
>> Probably cut-paste caused error. Here is my slightly rewritten script
>> and output:
>>
>
> I get the same error:
>


I hope this resolves the issue:
These 2 lines should be on one line in your editor:
  query.concat("&a=#{startDate.month-1}&b=#{startDate.mday}&c=#
  {startDate.year}")

And the same is  true for the next "query.concat etcetera" lines.

hth,

Siep
This topic is locked and can not be replied to.