DB to CSV

Discussion in 'Ruby' started by Mitja ÄŒebokli, Apr 16, 2010.

  1. Hello,

    i am very new to ruby and i have a major problem i would like to have
    some help on...

    I would like to execute a query on a database and export the values to
    the CSV file. I have tried many different ways and i can't get it to
    work.

    This is what i have so far:

    require 'oci8'
    require 'csv'

    time = Time.now
    rows =[]

    connection = OCI8.new("user", "pass", "srv")
    dataset = connection.exec("select column from table")
    while r = dataset.fetch()
    rows << r.join("\n")
    end
    dataset.close
    connection.logoff

    CSV.open("c:\\Temp\\test_"+time.strftime("%d-%m-%Y_%H-%M")+".csv","w",:col_sep
    => ";") do |csv|

    rows.each do |row|
    csv << row
    end
    end

    I am getting this error:
    csv.rb:1700 undefined method 'map'

    What i want to achieve is pretty simple:
    - have a header with names of columns from the database (automatic)
    - write a CSV file with each record as a new line
    - have a semicolon as a seperator

    Thanks for any info on this.
    BTW: no RAILS!
    --
    Posted via http://www.ruby-forum.com/.
     
    Mitja ÄŒebokli, Apr 16, 2010
    #1
    1. Advertising

  2. [Note: parts of this message were removed to make it a legal post.]

    Does something like this work?

    require 'csv'

    time = Time.now
    rows =[]

    (1..10).each do |x|
    rows << ['row', x, 'cheese']
    end

    CSV.open("testfile.csv","w",:col_sep => ";") do |csv|
    rows.each do |row|
    csv << row
    end
    end

    It this works then look at your data.

    Also I note that the line

    rows << r.join("\n")

    is probably not what you want. It will turn an array into a string.

    a = ['row',1,'cheese']
    a.join("\n") => "row\n1\ncheese"

    Are you sure you want to do this?
     
    Peter Hickman, Apr 16, 2010
    #2
    1. Advertising

  3. [Note: parts of this message were removed to make it a legal post.]

    Further hacking reveals that this might be a better option to create the
    file.

    CSV.open("testfile.csv","w", ";") do |csv|

    Note we are no longer using the :col_sep => ";" notation but we do get the ;
    as a record / column separator.
     
    Peter Hickman, Apr 16, 2010
    #3
  4. Peter Hickman wrote:
    > Further hacking reveals that this might be a better option to create the
    > file.
    >
    > CSV.open("testfile.csv","w", ";") do |csv|
    >
    > Note we are no longer using the :col_sep => ";" notation but we do get
    > the ;
    > as a record / column separator.


    Hi, thanks a lot for your help.

    I have changed

    while r = dataset.fetch()
    rows << r.join("\n")
    end

    to

    while r = dataset.fetch()
    rows << r
    end

    and this now works as expected! No more problems about filling the CSV
    file.

    On the other hand, not using :col_sep is giving me an error:
    can't convert String into Integer (TypeError)

    Maybe the formatting of the line is wrong?

    One more question on this matter.
    How do I extract/use the table columns from the select file or if used
    as "select *" to extract all the column names, and than use them as a
    CSV header.

    Thanks again helping me out, really appreciate it!

    BR



    --
    Posted via http://www.ruby-forum.com/.
     
    Mitja ÄŒebokli, Apr 16, 2010
    #4
  5. And one more thing :)

    I have a problem, as it seems, with columns where data are numbers.
    For example, row with ID number is returning me something like 0.2001E4

    What's with that? Shouldn't the CSV export treat all the data coming
    from DB as a string? Maybe there is a problem with wrong oci8 usage? (on
    my side of course)

    Thxs
    --
    Posted via http://www.ruby-forum.com/.
     
    Mitja ÄŒebokli, Apr 16, 2010
    #5
  6. On 16 April 2010 12:57, Mitja =C4=8Cebokli <> wrot=
    e:

    > On the other hand, not using :col_sep is giving me an error:
    > can't convert String into Integer (TypeError)
    >
    > Maybe the formatting of the line is wrong?
    >
    >

    Well if :col_sep works for you then use it. Perhaps this is a gem version
    issue. It didn't like it for me.

    One more question on this matter.
    > How do I extract/use the table columns from the select file or if used
    > as "select *" to extract all the column names, and than use them as a
    > CSV header.
    >
    >

    Sorry I have no knowledge of the Oracle db driver so I can't help you with
    that.

    I have a problem, as it seems, with columns where data are numbers.
    > For example, row with ID number is returning me something like 0.2001E4
    >


    How is the data being returned from the database? If Oracle is returning th=
    e
    data in this format 0.2001E4 (as a string) then ruby will just copy it as
    is. I suspect that Oracle is returning the float as a string because ruby
    would display 0.2001E4 as 2001.0 if it was actually given as a number.

    You might need to convert the data to get the values displayed in a sensibl=
    e
    way.

    a =3D "0.2001E4" =3D> "0.2001E4"
    a.to_f =3D> 2001.0
    a.to_f.to_s =3D> "2001.0"
     
    Peter Hickman, Apr 16, 2010
    #6
  7. On Fri, Apr 16, 2010 at 9:02 PM, Mitja =C4=8Cebokli
    <> wrote:
    > And one more thing :)
    >
    > I have a problem, as it seems, with columns where data are numbers.
    > For example, row with ID number is returning me something like 0.2001E4


    How did you define the ID number.
    If it is defined as NUMBER, the column is not an integer and it may not fit=
    to
    Float. Thus ruby-oci8 fetch it as BigDecimal by default.
    See: http://rubyforge.org/forum/forum.php?thread_id=3D47561&forum_id=3D107=
    8

    Could you redefine the ID as NUMBER(38) or so?
     
    KUBO Takehiro, Apr 16, 2010
    #7
    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. Michal Mikolajczyk
    Replies:
    0
    Views:
    673
    Michal Mikolajczyk
    Feb 13, 2004
  2. Skip Montanaro
    Replies:
    0
    Views:
    742
    Skip Montanaro
    Feb 13, 2004
  3. Tintin92
    Replies:
    1
    Views:
    1,751
    Andrew Thompson
    Feb 14, 2007
  4. jliu66
    Replies:
    0
    Views:
    528
    jliu66
    Oct 19, 2007
  5. sso
    Replies:
    20
    Views:
    2,707
    Martin Gregorie
    Apr 26, 2009
Loading...

Share This Page