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!
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?
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 H. 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
On 16 April 2010 12:57, Mitja ÄŒebokli [email protected] wrote:
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
the
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
sensible
way.
a = “0.2001E4” => “0.2001E4”
a.to_f => 2001.0
a.to_f.to_s => “2001.0”
On Fri, Apr 16, 2010 at 9:02 PM, Mitja ÄŒebokli
[email protected] 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=47561&forum_id=1078
Could you redefine the ID as NUMBER(38) or so?
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