Parsing CSV file into a database

Discussion in 'Ruby' started by Nick Hoyle, Feb 16, 2009.

  1. Nick Hoyle

    Nick Hoyle Guest

    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.
    --
    Posted via http://www.ruby-forum.com/.
     
    Nick Hoyle, Feb 16, 2009
    #1
    1. Advertising

  2. On Mon, Feb 16, 2009 at 3:25 PM, Nick Hoyle <> 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.
     
    Jesús Gabriel y Galán, Feb 16, 2009
    #2
    1. Advertising

  3. Nick Hoyle

    Nick Hoyle Guest

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


    --
    Posted via http://www.ruby-forum.com/.
     
    Nick Hoyle, Feb 16, 2009
    #3
  4. Nick Hoyle

    James Gray Guest

    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
     
    James Gray, Feb 16, 2009
    #4
  5. Nick Hoyle

    Nick Hoyle Guest

    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


    --
    Posted via http://www.ruby-forum.com/.
     
    Nick Hoyle, Feb 16, 2009
    #5
  6. On Feb 16, 5:39 pm, Nick Hoyle <> wrote:
    > 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

    >
    > --
    > 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
     
    Bosko Ivanisevic, Feb 16, 2009
    #6
  7. Nick Hoyle

    7stud -- Guest

    Bosko Ivanisevic wrote:
    > On Feb 16, 5:39�pm, Nick Hoyle <> 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

    --
    Posted via http://www.ruby-forum.com/.
     
    7stud --, Feb 16, 2009
    #7
  8. On Feb 16, 9:34 pm, 7stud -- <> wrote:
    > Bosko Ivanisevic wrote:
    > > On Feb 16, 5:39 pm, Nick Hoyle <> 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
    >
    > --
    > 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
     
    Bosko Ivanisevic, Feb 17, 2009
    #8
  9. Nick Hoyle

    7stud -- Guest

    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

    --
    Posted via http://www.ruby-forum.com/.
     
    7stud --, Feb 17, 2009
    #9
  10. 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
    --
    Posted via http://www.ruby-forum.com/.
     
    Siep Korteling, Feb 17, 2009
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. GIMME
    Replies:
    2
    Views:
    895
    GIMME
    Feb 11, 2004
  2. Dave
    Replies:
    4
    Views:
    1,849
    Tom Forsmo
    Oct 23, 2006
  3. Tintin92
    Replies:
    1
    Views:
    1,758
    Andrew Thompson
    Feb 14, 2007
  4. jliu66
    Replies:
    0
    Views:
    532
    jliu66
    Oct 19, 2007
  5. Li Chen
    Replies:
    18
    Views:
    688
    Azmi Farih
    Mar 23, 2010
Loading...

Share This Page